sqlstringcasesubstrinstr

How to retrieve the required string in SQL having a variable length parameter

Here is my problem statement:

  1. I have single column table having the data like as :

ROW-1>> 7302-2210177000-XXXX-XXXXXX-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-U-XXXXXXXXX-XXXXXX ROW-2>> 0311-1130101-XXXX-000000-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-X-XXXXXXXXX-WIPXXX

Here i want to separate these values from '-' and load into a new table. There are 11 segments in this string separated by '-', therefore, 11 columns. The problem is:

A. The length of these values are changing, however, i have to keep it as the length of these values in the standard format or the length which it has e.g 7302- (should have four values, if the value less then that then keep that value eg. 73 then it should populate 73.

Therefore, i have to separate as well as mentation the integrity. The code which i am writing is :

select 
 SUBSTR(PROFILE_ID,1,(case when length(instr(PROFILE_ID,'-')<>4) THEN (instr(PROFILE_ID,'-') else SUBSTR(PROFILE_ID,1,4) end)
 )AS [RQUIRED_COLUMN_NAME]
 from [TABLE_NAME];

getting right parenthesis error

Please help.

Solution

  • I used the regex_substr SQL function to solve the above issue. Here below is an example:

    select regex_substr('7302-2210177000-XXXX-XXXXXX-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-U-XXXXXXXXX-XXXXXX ROW-2>> 0311-1130101-XXXX-000000-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-X-XXXXXXXXX-WIPXXX',[^-]+,1,1);

    Output is: 7302 --which is the 1st segment of the string

    Similarly, the send string segment which is separated by "-" in the string can be obtained by just replacing the 1 with 2 in the above query at the end.

    Example : select regex_substr('7302-2210177000-XXXX-XXXXXX-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-U-XXXXXXXXX-XXXXXX ROW-2>> 0311-1130101-XXXX-000000-XXX-XXXXXXXXXX-XXXXXX-XXXXXX-X-XXXXXXXXX-WIPXXX',[^-]+,1,2);

    output: 2210177000 which is the 2nd segment of the string