Answered by:
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..
- 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 versionThursday, 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 AparnaMonday, December 4, 2017 6:50 AM
-
Thank you @Ousama EL HORMonday, 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