I am trying to get the string separated to different columns from right based on Hyphen(-) using SQL Server. But, I am unable to implement it. Below is an example of what I would like to do:

String Input:'CCP-GX-NP-ICL-14-SDSFPD4_04-MUN0X3'

Expected Output:

From right, I would like to get first string separated by Hyphen into different column, second string from separated by Hyphen into separate column and rest all the information from right to third column.

I tried below thing

Try 1:

declare @str varchar(max)=  'CCP-GX-NP-ICL-14-SDSFPD4_04-MUN0X3'

select * from (select value, row_number() over (order by charindex('-'   value   '-', '-'   @str   '-')) rn
    from string_split(@str, '-')) t1

I was able to get values separated out in different rn's, how can I get only rn number 6 and 7 in different column and rn 1 to (Last-2) into other column?

CodePudding user response：

Here is an option using JSON rather than string_split() which has no ordinal and can not GTD the sequence.

declare @str varchar(max)=  'CCP-GX-NP-ICL-14-SDSFPD4_04-MUN0X3'

Select Col1 = JSON_VALUE(JS,'$[0]')
          '-' JSON_VALUE(JS,'$[1]')
          '-' JSON_VALUE(JS,'$[2]')
          '-' JSON_VALUE(JS,'$[3]')
          '-' JSON_VALUE(JS,'$[4]')
      ,Col2 = JSON_VALUE(JS,'$[5]')
      ,Col3 = JSON_VALUE(JS,'$[6]')
 From (values ( '["' replace(string_escape(@str,'json'),'-','","') '"]' ) )A(JS)

Results

Col1                Col2        Col3
CCP-GX-NP-ICL-14    SDSFPD4_04  MUN0X3

If variable number of dashes and the key is the last two

Select Col1 = left(@Str,len(JSON_VALUE(JS,'$[0]') JSON_VALUE(JS,'$[1]')))
      ,Col2 = reverse(JSON_VALUE(JS,'$[1]'))
      ,Col3 = reverse(JSON_VALUE(JS,'$[0]'))
 From (values ( '["' replace(string_escape(reverse(@str),'json'),'-','","') '"]' ) )A(JS)

CodePudding user response：

You can do this with CHARINDEX() and SUBSTRING() functions in T-SQL

DECLARE @str VARCHAR(MAX)=  'CCP-GX-NP-ICL-14-SDSFPD4_04-MUN0X3'
DECLARE @end_index INT = DATALENGTH(@str)-CHARINDEX(REVERSE('-'),REVERSE(@str)) --Get the last occurrence of the delimiter
DECLARE @next_to_last INT =  DATALENGTH(SUBSTRING(@str,1,@end_index))-CHARINDEX(REVERSE('-'),REVERSE(SUBSTRING(@str,1,@end_index))) --Get the next to last occurrence

SELECT 
@str AS String,
DATALENGTH(@str) AS Length,
@next_to_last AS NextToLast,
@end_index AS EndIndex, 
SUBSTRING(@str,1,@next_to_last) AS [Column 1], --Get substring from beginning to next to last occurrence
SUBSTRING(@str,@next_to_last 2,(@end_index-@next_to_last)-1) AS [Column 2], --Get substring between the next to last and last occurrence
SUBSTRING(@str,@end_index 2,DATALENGTH(@str) - @end_index) AS [Column 3] --Get everything to the right of the last occurrence