Spliting String based on a string

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..

Answer 1

 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 [e-mail].

Answer 2

Thanks @will. Unfortunately it's 2012 sql version  

Answer 3

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.

Answer 4

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 [e-mail].

Answer 5

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

Answer 6

Thanks Aparna

Answer 7

Thank you @Ousama EL HOR

Answer 8

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.