I have a table in a database and one of the columns of the table is of the format AAA-BBBBBBB-CCCCCCC(in the table below column Id) where A, B and C are all numbers (0-9). I want to write a SELECT query such that for this column I only want the values in the format BBBBBBB-CCCCCCC. I am new to SQL so not sure how to do this. I tried using SPLIT_PART on - but not sure how to join the second and third parts.

Table -

So when the query is written the output should be like

Output

4567890-1234567
7890123-4567890 

CodePudding user response：

As mentioned in the request comments, you should not store a combined number, when you are interested in its parts. Store the parts in separate columns instead.

However, as the format is fixed 'AAA-BBBBBBB-CCCCCCC', it is very easy to get the substring you are interested in. Just take the string from the fifth position on:

 select substr(col, 5) from mytable;

CodePudding user response：

You can select the right part of a column starting at the 4th character

SELECT RIGHT(Id, LEN(Id)-4) AS TrimmedId;

CodePudding user response：

Another option using regexp_substr

with x ( c1,c2,c3 ) as 
(
select '123-4567890-1234567', 'First Name' , 199 from dual union all
select '456-7890123-4567890', 'Hulkamania' , 200 from dual 
)
select regexp_substr(c1,'[^-] ',1,2)||'-'||regexp_substr(c1,'[^-] ',1,3) as result from x ;

Demo

SQL> with x ( c1,c2,c3 ) as
(
select '123-4567890-1234567', 'First Name' , 199 from dual union all
select '456-7890123-4567890', 'Hulkamania' , 200 from dual
)
select regexp_substr(c1,'[^-] ',1,2)||'-'||regexp_substr(c1,'[^-] ',1,3) as result from x ;  2    3    4    5    6

RESULT
--------------------------------------------------------------------------------
4567890-1234567
7890123-4567890

SQL>