SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Loop through array and build string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fralo
Posting Yak Master

153 Posts

Posted - 02/05/2013 :  09:59:58  Show Profile  Reply with Quote
Hi all,

In my stored procedure I have an incoming array of strings (e.g. 'ECSO10ARR', ECSO11ARR', ECSO12ARR').

ALTER PROCEDURE [dbo].[specso_WarrantsByZone_Report]
@yearval VARCHAR(500),
AS

I would like to loop through this list and then build a string which will serve as a condition in a WHERE clause, like this.

WHERE yearfield LIKE 'ECSO10ARR%' OR
yearfield LIKE 'ECSO11ARR%' OR
yearfield LIKE 'ECSO12ARR%'

I'm really stumped with this, so I would greatly appreciate your assistance.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/05/2013 :  10:18:09  Show Profile  Reply with Quote
See

http://visakhm.blogspot.in/2013/01/string-pattern-search-xml-based-method.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/05/2013 :  10:28:26  Show Profile  Reply with Quote
OR -
choose your favorite table valued "split function" (there are countless examples posted here on this site) and JOIN to it something like this:

from dbo.fn_splitFunction(@yearval, ',') f
join [yourTable] yt on yt.yearfield like f.output + '%'

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/05/2013 :  10:55:14  Show Profile  Reply with Quote
in your case it would be like


ALTER PROCEDURE [dbo].[specso_WarrantsByZone_Report]
@yearval VARCHAR(500),
AS 
SELECT 
FROM  (SELECT CAST('<Node><Element>' + REPLACE(@yearval,',','</Element><Element>') + '</Element></Node>' AS xml) AS XMLval) t
INNER JOIN yourtable t1
ON XMLval.exist('/Node/Element[.=sql:column("yearfield")]')=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

fralo
Posting Yak Master

153 Posts

Posted - 02/05/2013 :  11:25:47  Show Profile  Reply with Quote
Thanks guys for your help. The problem is that it's a little more involved. How would I add that logic to a query already containing JOINS amd multiple conditions, such as:

SELECT NAME
FROM MBI..MBI WITH (NOLOCK), MBI..MBI_ADD WITH (NOLOCK),
ART..ART WITH (NOLOCK), ART..ARR_AFSS WITH (NOLOCK)
WHERE ART.ARTTYPE = 'W' AND ART.WARSTATUS = 'W' AND
AND MBI_ADD.ECSOID = MBI.ECSOID AND
ART.ECSOID = MBI.ECSOID AND ART.ARTNO =
ARR_AFSS.ARTNO AND
//ADD CONDITION FOR YEAR VALUE HERE

Thanks for your patience with me and your help.

Edited by - fralo on 02/05/2013 11:26:35
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/05/2013 :  12:08:45  Show Profile  Reply with Quote

SELECT NAME
FROM MBI..MBI WITH (NOLOCK)
INNER JOIN MBI..MBI_ADD WITH (NOLOCK)
ON MBI_ADD.ECSOID = MBI.ECSOID
INNER JOIN ART..ART WITH (NOLOCK)
ON ART.ECSOID = MBI.ECSOID
INNER JOIN ART..ARR_AFSS WITH (NOLOCK)
ON ART.ARTNO = ARR_AFSS.ARTNO
INNER JOIN (SELECT CAST('<Node><Element>' + REPLACE(@yearval,',','</Element><Element>') + '</Element></Node>' AS xml) AS XMLval) t
ON XMLval.exist('/Node/Element[.=sql:column("Youryearfieldhere")]')=1
WHERE ART.ARTTYPE = 'W' 
AND ART.WARSTATUS = 'W' 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

fralo
Posting Yak Master

153 Posts

Posted - 02/05/2013 :  13:08:45  Show Profile  Reply with Quote
Thank so much Vis. Does your select cast handle LIKE? My incoming values will be like ECSO12ARR, ECSO13ARR but I need to search for anything that begins with that: 'ECSO12ARR%' OR 'ECSO13ARR%'.

Also, "Youryearfieldhere" refers to the database filed, correct?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/05/2013 :  13:24:02  Show Profile  Reply with Quote
>> Does your select cast handle LIKE?
The solution I posted does.

Be One with the Optimizer
TG
Go to Top of Page

fralo
Posting Yak Master

153 Posts

Posted - 02/05/2013 :  14:23:19  Show Profile  Reply with Quote
I see that TG, and I appreciate it tremendously. Would you know how to incorporate your FROM clause in my big SELECT?

Thank you.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/05/2013 :  15:31:18  Show Profile  Reply with Quote
Something like this. (I changed your old style join syntax to ANSI compliant)

SELECT NAME
FROM MBI..MBI WITH (NOLOCK)
inner join MBI..MBI_ADD WITH (NOLOCK)
       on MBI_ADD.ECSOID = MBI.ECSOID
inner join ART..ART WITH (NOLOCK)
       on ART.ECSOID = MBI.ECSOID 
inner join ART..ARR_AFSS WITH (NOLOCK)
       on ARR_AFSS.ARTNO = ART.ARTNO 

//ADD CONDITION FOR YEAR VALUE HERE
inner join dbo.fn_splitFunction(@yearval, ',') f
       on yearfield like f.<functionReturnColumn> + '%'

WHERE  ART.ARTTYPE = 'W' 
AND    ART.WARSTATUS = 'W' 


Be One with the Optimizer
TG
Go to Top of Page

fralo
Posting Yak Master

153 Posts

Posted - 02/05/2013 :  15:39:23  Show Profile  Reply with Quote
Is fn_splitFunction a built-in function because when I add it in, it says it's not recognized?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/05/2013 :  15:44:41  Show Profile  Reply with Quote
No, it is not built in (check my original post).

Sorry there are a lot to choose from. You can start looking through these highlighted sections:
CSV / Splitting delimited lists

Be One with the Optimizer
TG
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/06/2013 :  01:13:52  Show Profile  Reply with Quote
quote:
Originally posted by fralo

Thank so much Vis. Does your select cast handle LIKE? My incoming values will be like ECSO12ARR, ECSO13ARR but I need to search for anything that begins with that: 'ECSO12ARR%' OR 'ECSO13ARR%'.

Also, "Youryearfieldhere" refers to the database filed, correct?



it can. just use contains as per illustration below



declare @t table
(
id int,
field varchar(20)
)

insert @t
values(1,'test'),
(2,'giu'),
(3,'pieceofmeal'),
(4,'csvtext'),
(5,'master'),
(7,'pattern')

declare @t1 table
(
id int,
list varchar(1000)
)

insert @t1
values (1,'this,is,test,for,csv,search,piece'),
(2,'finding,string,pattern,within,given,csv,value')

SELECT t.id,field,list
FROM @t t
INNER JOIN (SELECT id,list,CAST('<Node><Row>' + replace(list,',','</Row><Row>') + '</Row></Node>' as xml) AS XMLList FROm @t1) t1
ON XMLList.exist('/Node/Row/text()[contains(sql:column("field"),.)]')=1


output
--------------------------------------------------------------
id	field	        list
--------------------------------------------------------------
1	test	        this,is,test,for,csv,search,piece
3	pieceofmeal	this,is,test,for,csv,search,piece
4	csvtext	        this,is,test,for,csv,search,piece
4	csvtext	        finding,string,pattern,within,given,csv,value
7	pattern	        finding,string,pattern,within,given,csv,value



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

fralo
Posting Yak Master

153 Posts

Posted - 02/06/2013 :  08:47:58  Show Profile  Reply with Quote
I've almost got it guys. If you can just bear with me one more moment...

I found this split function.

ALTER FUNCTION [dbo].[Split]
(
@RowData nvarchar(500),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

And am using this in my select:

FROM MNI..MNI WITH (NOLOCK)
INNER JOIN MNI..MNI_ADD WITH (NOLOCK)
ON MNI_ADD.ECSOID = MNI.ECSOID
INNER JOIN ARREST..ARREST WITH (NOLOCK)
ON ARREST.ECSOID = MNI.ECSOID
INNER JOIN ARREST..ARR_AFSS WITH (NOLOCK)
ON ARREST.ARRESTNO = ARR_AFSS.ARRESTNO
inner join dbo.Split(@yearval, ',') f
on ARREST.arrestno like f.<functionReturnColumn> + '%'

I don't know what to place in <functionReturnColumn>. I've tried what I think is the return value: @RtnValue.

But it doesn't like it.

You don't know how much I've appreciated your help.

Edited by - fralo on 02/06/2013 08:49:14
Go to Top of Page

fralo
Posting Yak Master

153 Posts

Posted - 02/06/2013 :  11:12:04  Show Profile  Reply with Quote
I was actually able to figure this one out.

inner join dbo.Split(@yearval, ',') f
on ARREST.arrestno like f.Data + '%'

Thanks to you both for all your assistance.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/06/2013 :  11:12:06  Show Profile  Reply with Quote
it should be f.Data as thats field function returns inside table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000