1

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.

Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
Prashant Rawat
  • 11
  • 3

1 Answers1

0

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

Prashant Rawat
  • 11
  • 3
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Feb 24 '23 at 01:24