I have a string with delimiter ‘_’, example below ‘AAA_BBB_CCC_DDD_EEE’ I want to trim the first two elements of delimited string. Expected output: ‘CCC_DDD_EEE’ Answer You should be able to use regexp_substr(): Or regexp_replace():
Find matching pattern
I have a table that contains multiple strings pattern (‘Q88 9WE’,’S9 D2P’,WC2R 3LS etc..). How do I extract and split the rows that meet only the pattern Where A is a character in the range A-Z and N is a digit 1-9 I have tried using the logic below and it doesn’t seem to work I&…
How to get max value from group by and groupconcat other field in mysql?
I tried to get the max value corresponding row and group_concat all the email address. MySql table: expected output: I tried the following query: but it gave wrong result: instead of id 1 I am getting id 2. If I remove the group_concat it gives the correct output. Answer If you want the id of the row with the…
MySQL query for selecting distinct rows with all possible values in a column
Here’s my DB table named ‘test_tbl’: id type 1 A 1 B 1 C 1 D 2 A 2 B 2 C 2 D 3 A 3 B 4 A 4 D Here every ‘id’ can have at most 4 possible values (A,B,C,D) for ‘type’ column. I want to find out those ids who don’t have all four values in
Oracle SQL combining table information and creating output
I have two tables in Oracle SQL developer and I am trying to figure out how to display “fname, ssn” and the employees corresponding manager “fname, snn”. Below are the tables that I have created but I cant seem to get the employee information to correspond to their subsequent manager. …
SQL Join – If it doesn’t find any, try another parameters
Let’s suppose we have the following query: If that LEFT JOIN doesn’t find nothing, I need to change the ON parameters to something like: That second LEFT JOIN only needs to run if the first one doesn’t return nothing. How can I achieve that behaviour? I have already tried with an OR statemen…
How to identify non-existing keys with reference to a table that has all mandatory keys, SQL?
I have the table ‘Table01’ which contains the keys that should be mandatory: id 1 2 3 4 And I also have the table ‘Table02’ which contains the records to be filtered: id customer weight 1 a 100 2 a 300 3 a 200 4 a 45 1 b 20 2 b 100 3 b 17 1 c 80 4
Separate numeric values and character values in oracle
A table contains both numeric and character values in a column. The numeric values and character values need to be separated in different columns Col1 1 2 3 A B C The output needs to be col1 col2 1 A 2 B 3 C Answer You could do this using conditional aggregation: You cold simplify this a bit using a
Is there a way to add ROW_NUMBER() without using OVER (ORDER BY …) in SQL
Is there a way to add ROW_NUMBER() simply based on the default row order without using OVER (ORDER BY …)? Answer There is no implicit ordering to rows in a table, it is a logical unordered set. however you can do row_number() over (order by (select null)) As suggested by Itzik Ben-Gan from his book on w…
ORA-00907: missing right parenthesis. I am passing a Variable ‘&Coordinator_Id&’
ORA-00907: missing right parenthesis I am trying to run the code and I am keeping getting the right parenthesis error I checked the entire code. Could somebody see why I am failing? Answer As mentioned in comments a case expression evaluates to a value, which you can then use or compare to something else. In …