locked
Spliting String based on a string RRS feed

  • Question

  •     I have a string, from which I need to break into different string.

       @filter=' ID=234 AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE '%new%''

        I need to split this above string into different string.

        For example if I pass 'ID' I should get (=234 )

       Or can I get separate string like ID=234, TYPE IN(1,2,4) and so on...

       Please suggest , I have tried with charindex & substring but no luck

       Below is the sample code so far

    declare @Condition varchar(1000)
    declare @Condition1 varchar(1000)
    set @Condition='ID'
    set @Condition1='and'
    declare @filter varchar(1000)
    SET @Filter=N'  ID=234 AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE '%new%' '


    SET @Filter=REPLACE(@Filter,'= ','=')
    SET @Filter=REPLACE(@Filter,' =','=')
    SET @Filter=REPLACE(@Filter,' >','>')

    SET @filter= SUBSTRING(substring(@filter,CHARINDEX(@Condition,@Filter),CHARINDEX(@Condition1,@Filter)-len(@condition1)),len(@condition)+1,5) 

    select @filter

    Thanks  in advance..


    • Edited by DM2017 Thursday, November 30, 2017 9:06 AM
    Thursday, November 30, 2017 9:04 AM

Answers

  •  Or can I get separate string like ID=234, TYPE IN(1,2,4) and so on...

      Please suggest , I have tried with charindex & substring but no luck


    Hi DM2017,

    If the version of your SQL Server is above SQL Server 2016, you may use string_split function to achieve your needs.

     declare @Condition varchar(1000)
     declare @Condition1 varchar(1000)
    
     set @Condition='ID'
     set @Condition1='AND'
     declare @filter varchar(1000)
    
     SET @Filter=N'ID=234 AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE ''%new%'''
    
     set @filter=REPLACE(@Filter,@Condition1,'|')
    
     select * from string_split(@Filter,'|')

    Best Regards,

    Will


    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.

    • Proposed as answer by pituachMVP Friday, January 12, 2018 6:44 AM
    • Marked as answer by pituachMVP Friday, January 12, 2018 6:49 AM
    Thursday, November 30, 2017 9:23 AM
  • Hi,

    This is a version for 2012 :

    declare @Condition varchar(1000)
     declare @Condition1 varchar(1000)
    
     set @Condition='ID'
     set @Condition1='AND'
     declare @filter varchar(1000)
    
     SET @Filter=N'ID=234 AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE ''%new%'''
    
     set @filter=REPLACE(@Filter,@Condition1,'|')
    
    
     SELECT   
         Split.a.value('.', 'VARCHAR(100)') AS Filter  
     FROM  
     (
         SELECT  
             CAST ('<M>' + REPLACE(@Filter, '|', '</M><M>') + '</M>' AS XML) AS Filter  
     ) AS A CROSS APPLY Filter.nodes ('/M') AS Split(a); 


    Please mark as answered, If you feel happy with this answer.


    • Edited by Ousama EL HOR Thursday, November 30, 2017 9:35 AM
    • Marked as answer by pituachMVP Friday, January 12, 2018 6:45 AM
    Thursday, November 30, 2017 9:35 AM
  • Thanks @will. Unfortunately it's 2012 sql version  

    Since your are using SQL Server 2012, you could use user-defined function to get desired result.

    CREATE FUNCTION [dbo].[fnSplitString] 
    ( 
        @string NVARCHAR(MAX), 
        @delimiter CHAR(1) 
    ) 
    RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
    ) 
    BEGIN 
        DECLARE @start INT, @end INT 
        SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
        WHILE @start < LEN(@string) + 1 BEGIN 
            IF @end = 0  
                SET @end = LEN(@string) + 1
           
            INSERT INTO @output (splitdata)  
            VALUES(SUBSTRING(@string, @start, @end - @start)) 
            SET @start = @end + 1 
            SET @end = CHARINDEX(@delimiter, @string, @start)
            
        END 
        RETURN 
    END
    
    
     declare @Condition varchar(1000)
     declare @Condition1 varchar(1000)
    
     set @Condition='ID'
     set @Condition1='AND'
     declare @filter varchar(1000)
    
     SET @Filter=N'ID=234 AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE ''%new%'''
    
     set @filter=REPLACE(@Filter,@Condition1,'|')
    
     select * from [dbo].[fnSplitString](@Filter,'|')

    Best Regards,

    Will


    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.

    • Marked as answer by pituachMVP Friday, January 12, 2018 6:50 AM
    Thursday, November 30, 2017 9:36 AM

All replies

  •  Or can I get separate string like ID=234, TYPE IN(1,2,4) and so on...

      Please suggest , I have tried with charindex & substring but no luck


    Hi DM2017,

    If the version of your SQL Server is above SQL Server 2016, you may use string_split function to achieve your needs.

     declare @Condition varchar(1000)
     declare @Condition1 varchar(1000)
    
     set @Condition='ID'
     set @Condition1='AND'
     declare @filter varchar(1000)
    
     SET @Filter=N'ID=234 AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE ''%new%'''
    
     set @filter=REPLACE(@Filter,@Condition1,'|')
    
     select * from string_split(@Filter,'|')

    Best Regards,

    Will


    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.

    • Proposed as answer by pituachMVP Friday, January 12, 2018 6:44 AM
    • Marked as answer by pituachMVP Friday, January 12, 2018 6:49 AM
    Thursday, November 30, 2017 9:23 AM
  • Thanks @will. Unfortunately it's 2012 sql version  
    Thursday, November 30, 2017 9:30 AM
  • Hi,

    This is a version for 2012 :

    declare @Condition varchar(1000)
     declare @Condition1 varchar(1000)
    
     set @Condition='ID'
     set @Condition1='AND'
     declare @filter varchar(1000)
    
     SET @Filter=N'ID=234 AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE ''%new%'''
    
     set @filter=REPLACE(@Filter,@Condition1,'|')
    
    
     SELECT   
         Split.a.value('.', 'VARCHAR(100)') AS Filter  
     FROM  
     (
         SELECT  
             CAST ('<M>' + REPLACE(@Filter, '|', '</M><M>') + '</M>' AS XML) AS Filter  
     ) AS A CROSS APPLY Filter.nodes ('/M') AS Split(a); 


    Please mark as answered, If you feel happy with this answer.


    • Edited by Ousama EL HOR Thursday, November 30, 2017 9:35 AM
    • Marked as answer by pituachMVP Friday, January 12, 2018 6:45 AM
    Thursday, November 30, 2017 9:35 AM
  • Thanks @will. Unfortunately it's 2012 sql version  

    Since your are using SQL Server 2012, you could use user-defined function to get desired result.

    CREATE FUNCTION [dbo].[fnSplitString] 
    ( 
        @string NVARCHAR(MAX), 
        @delimiter CHAR(1) 
    ) 
    RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
    ) 
    BEGIN 
        DECLARE @start INT, @end INT 
        SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
        WHILE @start < LEN(@string) + 1 BEGIN 
            IF @end = 0  
                SET @end = LEN(@string) + 1
           
            INSERT INTO @output (splitdata)  
            VALUES(SUBSTRING(@string, @start, @end - @start)) 
            SET @start = @end + 1 
            SET @end = CHARINDEX(@delimiter, @string, @start)
            
        END 
        RETURN 
    END
    
    
     declare @Condition varchar(1000)
     declare @Condition1 varchar(1000)
    
     set @Condition='ID'
     set @Condition1='AND'
     declare @filter varchar(1000)
    
     SET @Filter=N'ID=234 AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE ''%new%'''
    
     set @filter=REPLACE(@Filter,@Condition1,'|')
    
     select * from [dbo].[fnSplitString](@Filter,'|')

    Best Regards,

    Will


    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.

    • Marked as answer by pituachMVP Friday, January 12, 2018 6:50 AM
    Thursday, November 30, 2017 9:36 AM
  • Hello,

    You could achieve the desired result of getting it as individual strings using table value constructors. Trim the output if required to remove any additional leading and trailing spaces.

    DECLARE @filter varchar(max)=N'ID=234   AND TYPE IN(1,2,4) AND OrderID NOT IN (1,9) AND ORDER LIKE "%new%"'
    
    
    DECLARE @delimiter varchar(max)= 'AND'
    SET @filter = 'SELECT * FROM (VALUES(''' + REPLACE(@filter, @delimiter, '''),(''') + ''')) AS mytable(splitstring)' 
    
    EXEC(@filter)
    


    Aparna

    Thursday, November 30, 2017 7:31 PM
  • Thanks Aparna
    Monday, December 4, 2017 6:50 AM
  • Thank you @Ousama EL HOR
    Monday, December 4, 2017 6:51 AM
  • You are welcome DM2017.

    Please mark the correct answer, that can help other members.


    Please mark as answered, If you feel happy with this answer.

    Monday, December 4, 2017 10:19 AM