Splitting string values with more than 4 values in sql query in SQL server 16

user_15509411 0
Viewed 408

I want to split string "val=ABC_DEF_GHI_JKL_MNO_PQR" in to different column values and Parsename function is not accepting split criteria with more than 4 splits.

This values are coming from Table and needed to implement the function using SQL fucntions if possible.

PARSENAME(replace(val,'_','.'),4) as col1 and returning null. How can i split this value?

sql-server sql
2 Answers

For SQL Server 2016 I have implemented two query below. Query#1 will split the string into rows with string_split() function. Query#2 will split the string into columns with string_split() and dynamic pivot technique.

Create table and insert statements:

 create table testtable (teststring varchar(50));
 insert into testtable values('ABC_DEF_GHI_JKL_MNO_PQR');

Query#1

 select  row_number()over(order by (select null)) rn, [value] as Street1 from testtable 
    cross apply string_split(teststring,'_')
rn Street1
1 ABC
2 DEF
3 GHI
4 JKL
5 MNO
6 PQR

Or without row number it's just :

select   [value] as Street1 from testtable 
   cross apply string_split(teststring,'_')

Query#2:

 DECLARE @cols  AS NVARCHAR(MAX)='';
 DECLARE @query AS NVARCHAR(MAX)='';


 with cte as
 (
  select  row_number()over(order by (select null)) rn, [value] as Street1 from testtable 
  cross apply string_split(teststring,'_')
 )
 select  @cols = STUFF((SELECT distinct ',' + quotename(rn)
             FROM cte
             FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
         ,1,1,'')
         
 set @query = 'SELECT  ' + @cols + ' from 
 (
    select  row_number()over(order by (select null)) rn, [value] as Street1 from testtable 
    cross apply string_split(teststring,''_'')
 ) src
 pivot 
 (
     max(street1) for rn in (' + @cols + ')
 ) piv'
 
 
 execute(@query)

Output:

1 2 3 4 5 6
ABC DEF GHI JKL MNO PQR

db<>fiddle here

If you are using MySQL 8.0 then with recursive common table expression you can easily split this to rows :

 create table testtable (teststring varchar(50));
 insert into testtable values('ABC_DEF_GHI_JKL_MNO_PQR');

Query:

 with RECURSIVE cte (street1,level)as
 (
 select SUBSTRING_INDEX(teststring, '_', 1) AS street1, 1 as level
 from testtable
 
 union all
 
 select SUBSTRING_INDEX(SUBSTRING_INDEX(teststring, "_", level+1), "_", -1),level+1
 from cte inner join testtable t 
 on cte.street1<SUBSTRING_INDEX(SUBSTRING_INDEX(teststring, "_", level+1), "_", -1)
 )
 select street1 from cte

Outupt:

street1
ABC
DEF
GHI
JKL
MNO
PQR

db<fiddle here

user_86516010

SUBSTRING_INDEX is your goal.

set @val="ABC_DEF_GHI_JKL_MNO_PQR" ;


select
    SUBSTRING_INDEX(@val, "_", 1) as no1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 2), "_", -1) as no2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 3), "_", -1) as no3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 4), "_", -1) as no4,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 5), "_", -1) as no5,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 6), "_", -1) as no6;

sample

MariaDB [bernd]> select @val;
+-------------------------+
| @val                    |
+-------------------------+
| ABC_DEF_GHI_JKL_MNO_PQR |
+-------------------------+
1 row in set (0.01 sec)

MariaDB [bernd]> 
MariaDB [bernd]> 
MariaDB [bernd]> select
    -> SUBSTRING_INDEX(@val, "_", 1) as no1,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 2), "_", -1)as no2,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 3), "_", -1)as no3,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 4), "_", -1)as no4,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 5), "_", -1)as no5,
    -> SUBSTRING_INDEX(SUBSTRING_INDEX(@val, "_", 6), "_", -1)as no6;
+------+------+------+------+------+------+
| no1  | no2  | no3  | no4  | no5  | no6  |
+------+------+------+------+------+------+
| ABC  | DEF  | GHI  | JKL  | MNO  | PQR  |
+------+------+------+------+------+------+
1 row in set (0.01 sec)

MariaDB [bernd]>
user_52472790
Related