Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Loop through array and build string

Author  Topic 

fralo
Posting Yak Master

161 Posts

Posted - 2013-02-05 : 09:59:58
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

52326 Posts

Posted - 2013-02-05 : 10:18:09
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-05 : 10:28:26
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

52326 Posts

Posted - 2013-02-05 : 10:55:14
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

161 Posts

Posted - 2013-02-05 : 11:25:47
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 12:08:45
[code]
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'
[/code]

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

Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2013-02-05 : 13:08:45
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-05 : 13:24:02
>> 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

161 Posts

Posted - 2013-02-05 : 14:23:19
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-05 : 15:31:18
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

161 Posts

Posted - 2013-02-05 : 15:39:23
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-02-05 : 15:44:41
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

52326 Posts

Posted - 2013-02-06 : 01:13:52
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

161 Posts

Posted - 2013-02-06 : 08:47:58
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.
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2013-02-06 : 11:12:04
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

52326 Posts

Posted - 2013-02-06 : 11:12:06
it should be f.Data as thats field function returns inside table

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

Go to Top of Page
   

- Advertisement -