How to split a string in sql server using stored procedure and insert the data to table

Question

<pre>update d 
  set d.Price = null 
from dbo.SalDocumentDetail d 
left join dbo.StkWarehouse w on w.WarehouseID = d.WarehouseID 
where DocumentID=" + 1 + " 
and DocumentTypeID=" + 2 + " 
and FiscalYear= " + 2016 + " 
and isnull(isPrescription,0) <>1 
and w.POSType is null 
and ProductName BETWEEN ''C' 'AND' 'M''
and Country LIKE ''%land%'''</pre>

Actually this string is only a sample one my original string is very large . i am not getting a point that if i break this string than how many variables i have to make to capture the data also after splitting the string i want that to be inserted into data table containing columns as Felid and Value?

I want my result like :
<pre>
Felid                         Value
 DocumentID=                    1 
 DocumentTypeID=                2 
 FiscalYear=                   2016 
 isnull(isPrescription,0) <>=     1 
 w.POSType is=                 null 
 ProductName=                   C
 ProductName=                   M 
 Country=                       land 
</pre>

khalid your question is unclear. where is the string, what is the schema for dbo.SalDocumentDetail and dbo.StkWarehouse what is the desired result. Are you wanting columns as rows? — Matt, Jun 26 '16 at 20:02
I want function split after Where like my result i want two columns Felid and Value This i want split after (where DocumentID=" + 1 + " and DocumentTypeID=" + 2 + " and FiscalYear= " + 2016 + " and isnull(isPrescription,0) <>1 and w.POSType is null and ProductName BETWEEN ''C' 'AND' 'M'' and Country LIKE ''%land%''') — khalid, Jun 26 '16 at 22:46
khalid try taking a look at your question simply restating your question in the same manner is not going to make me understand. Plenty of people have looked at it and thought about it and presumably not understood what you want otherwise you would see more action and help. So you need to help us understand better by providing more detail in your question by editing and letting us know schema and search/where string. — Matt, Jun 26 '16 at 22:53
I post Answer Look the function I think you will understand me — khalid, Jun 26 '16 at 23:06
so you want to take this as entire string 'Felid Value DocumentID= 1 DocumentTypeID= 2 FiscalYear= 2016 isnull(isPrescription,0) <>= 1 w.POSType is= null ProductName= C ProductName= M Country= land ' and split it up to a table of Filed & Value — Matt, Jun 26 '16 at 23:13
Yes I want that Please I tried to solve it but I don't Find any solution — khalid, Jun 26 '16 at 23:29
another question are really just trying to use the string to filter in a where condition like the first code in your question? If so it won't do you that much good to split the string to rows. You would be better off splitting them to columns or simply searching the string in the where statement it self. — Matt, Jun 26 '16 at 23:32
yes I want split after where condition if find any 'and,or,like,is,between ' split it like my result example: columns : Felid columns : Value Rows : DocumentID= Rows: 1 — khalid, Jun 26 '16 at 23:46

score 0 · Answer 1 · answered Jun 26 '16 at 23:04

<pre>I Use this function but this function split 'and' not split like what i want in my result i want split 'and,or,like,is,between ' if function find any this split it to two columns (Felid and Value)</pre>

<pre>ALTER FUNCTION [dbo].[fnSplitString] 
(@List NVARCHAR(MAX),@Delimiter  NVARCHAR(255))
RETURNS @Items TABLE(Felid NVARCHAR(Max),Valu nvarchar(MAx))
WITH SCHEMABINDING
AS BEGIN
DECLARE @ll INT=LEN(@List)+1,@ld INT=LEN(@Delimiter); 
WITH a AS
(SELECT     
[end]=COALESCE(NULLIF(CHARINDEX(@Delimiter,@List,1),0),@ll),                      
[VlaueFelid]=SUBSTRING(@List,(select
CHARINDEX('where',@List)+5),COALESCE(NULLIF(CHARINDEX('=',  @List,0),0),@ll) ) ,
[Value]=SUBSTRING(@List,(select CHARINDEX('="',@List)+2),(select CHARINDEX('and',@List))-(select C`enter code here`HARINDEX('="',@List)+3))              
UNION ALL
SELECT 
[end]=COALESCE(NULLIF(CHARINDEX(@Delimiter,@List,[end]+@ld), 0),@ll),                    
[VlaueFelid]=SUBSTRING(@List,[end]+@ld,   COALESCE(NULLIF(CHARINDEX('=',@List, [end]+@ld),0),@ll)-[end]-@ld),
[Value]=SUBSTRING(@List,[end]+@ld+16,  COALESCE(NULLIF(CHARINDEX('=',@List,[end]+@ld),0),@ll)-[end]-@ld-5)                      
 FROM a WHERE [end]< @ll) INSERT @Items SELECT[VlaueFelid],[Value] FROM a WHERE LEN([VlaueFelid])>0 RETURN;
END</pre>

Yeah Khalid this had issues with your string for sure. However, there are plenty of answers on how to split a string to rows I suggest doing a search for some and read up. here is one to get you started. http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Matt, Jun 26 '16 at 23:27
OK Matt Thanx for help but my issues multiple cass how to make function split string if found 'and,or,is,between,like' i read in many website i don't have solution — khalid, Jun 27 '16 at 00:18

How to split a string in sql server using stored procedure and insert the data to table

1 Answers1