locked
Split string in SQL RRS feed

  • Question

  • Hi Team,

    How to split string without using string_split function.

    Input : SAV-JFK/JFK-DXB/DXB-HYD

    Output : SAV,JFK,DXB,HYD


    Thanks Bala Narasimha

    Monday, June 22, 2020 5:22 AM

All replies

  • DECLARE @st VARCHAR(50)='SAV-JFK/JFK-DXB/DXB-HYD'


    ;WITH cte
    AS
    (
    SELECT value,
    row_number () over (order by (select 0)) rn
     FROM 
     string_split(REPLACE(REPLACE(@st,'-',','),'/',','),',')
     )  SELECT * into #tmp 
     FROM cte 


     with cte1
     as
     (
     select * ,row_number () over (partition by value order by (select 0)) rn1 from #tmp
     )  SELECT  stuff(
      (SELECT  ',' + value as [text()] FROM  cte1 where rn1=1  order by rn for xml path('')),
      1, 1, '');

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 22, 2020 5:54 AM
    Answerer
  • How to split string without using string_split function.

    Why without the existing string_split function?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, June 22, 2020 8:00 AM
  • Replace the string_split function with this UDF

    CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
    RETURNS table

    AS
    RETURN (
        WITH Pieces (ID, start, stop) AS (
          SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
          UNION ALL
          SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
          FROM Pieces
          WHERE stop > 0
        )
        SELECT ID,
          SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
        FROM Pieces
      )
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Tom Phillips Monday, June 22, 2020 12:43 PM
    Monday, June 22, 2020 8:19 AM
    Answerer
  • Check an alternative concept:

    declare @input as varchar(max) = 'SAV-JFK/JFK-DXB/DXB-HYD'

    declare @t as varchar(max)

    set @t = translate(@input, '/', '-')

    set @t = replace(@t, '''', '''''')

    set @t = replace(@t, '-', '''),(''')

    set @t = 'select distinct * from (values (''' + @t + ''')) as t(output)'

    exec (@t)

    Check if it works on your server. Do you also want to preserve the original order and make a comma-separated result?


    Monday, June 22, 2020 10:10 AM
  • Yep it pretty nice , but in case the OP uses SQL Server 2017+

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 22, 2020 10:14 AM
    Answerer
  • Hi Bala Narasimha,

    Here is XML and XQuery based solution. One single T-SQL statement. SQL Server 2005 onwards.

    SQL:

    DECLARE @inputString AS VARCHAR(MAX) = 'SAV-JFK/JFK-DXB/DXB-HYD'
    	, @separator CHAR(1) = ','
    	, @separator1 CHAR(1) = '-'
    	, @separator2 CHAR(1) = '/';
    
    SELECT (SELECT CAST('<root><r>' + 
              REPLACE(REPLACE(@inputString, @separator1, @separator2), @separator2, '</r><r>') + '</r></root>' AS XML)
    ).query('
       for $i in distinct-values(/root/r/text())
       return if ($i eq (distinct-values(/root/r/text())[last()])[1]) then $i
             else concat($i, sql:variable("@separator"))
    ').value('.', 'NVARCHAR(MAX)') AS airportList;

    Output:

    airportList
    SAV, JFK, DXB, HYD
    Monday, June 22, 2020 1:06 PM
  • @Viorel_ has a nice technique .I  just derived from him

     declare @t as varchar(max)
    set @t = concat ( 'select  DISTINCT a from (values ' , concat('(''', replace(replace('SAV-JFK/JFK-DXB/DXB-HYD','-','''),('''),'/','''),('''),''')') , ' ) as x(a)' )
    exec( @t)

    Monday, June 22, 2020 1:44 PM
  • declare @s as varchar(max) = 'SAV-JFK/JFK-DXB/DXB-HYD'
    
    ;with mycte as (
      SELECT  [key], Value val 
      FROM OpenJson('["'+replace(replace(@s ,'-','","'),'/','","')+'"]') 
      )
     
    ,mycte2 as (
    Select [key],
    row_number() Over(partition by [val] order by [key]) rn,
    val
    from mycte )
    
    select STRING_AGG (Val,',') WITHIN GROUP (ORDER BY [key])   
    from mycte2
    where rn=1
    
    --SAV,JFK,DXB,HYD
     

    Monday, June 22, 2020 3:08 PM
  • Took me a while to find where did I document that solution (I first tried to search a bunch of my functions and stored procedures and none was what I was looking for). Finally found it in my blog post (see the bottom of that blog for Peter Larsson's idea):

    https://blogs.lessthandot.com/index.php/datamgmt/datadesign/passing-multiple-ranges-to-stored-proced/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, June 22, 2020 3:25 PM
  • Hi Bala Narasimha,

    --you could use a function to replace string_split
    CREATE FUNCTION [dbo].[split] (@String varchar(max),@Delimiter varchar(10))
    Returns Table 
    As
    Return (  
        Select RetSeq = Row_Number() over (Order By (Select null))
              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
        From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
        Cross Apply x.nodes('x') AS B(i)
    );
    
    --then use it
    DECLARE @in VARCHAR(max)='SAV-JFK/JFK-DXB/DXB-HYD'
    DECLARE @out VARCHAR(max)
    
    ;with cte1 as(select distinct RetVal from [dbo].[split](replace(@in,'/','-'),'-'))
    select @out=coalesce(@out+',','')+RetVal from cte1
    select @out
    
    --output
    DXB,HYD,JFK,SAV

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 23, 2020 2:05 AM
  • Hi Bala Narasimha,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 24, 2020 7:42 AM