Searching in a concatenated String

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 2005 Forums

Transact-SQL (2005)

Searching in a concatenated String

Author

Topic

SLM09
Starting Member

31 Posts

Posted - 2010-04-01 : 09:04:51

Hi all,

Is it possible to declare a string, concatenate its contents through case statements, and then have a WHERE x IN @declared_string? Ultimately, I want to be able to choose which timezones will be "legal" given criteria in Case statements, and then have the where include the legal timezones.

As a simplified example:

DECLARE @timezones as varchar(50)
SET @timezones = '(' + '''' + 'east' + '''' + ', ' + '''' + 'west' + '''' + ')'

SELECT @timezones

SELECT name
FROM dbo.data
WHERE time_zone in @timezones

The first select runs showing the timezones have made a legal string that could be searched in, but I am still erroring out. I guess you can't search "in" a declared string.

If anyone knows how to get this to work, or another way to do this, I would be greatly appreciative.

Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-01 : 09:14:52

Hi,

The value SET @timezones = '(' + '''' + 'east' + '''' + ', ' + '''' + 'west' + '''' + ')'
will become "('east','west')". It will treated as a single value but not two comma separated values.

Refer below link:
http://www.sommarskog.se/arrays-in-sql-2005.html

regards,
Bohra

SLM09
Starting Member

31 Posts

Posted - 2010-04-01 : 09:17:31

Awesome, thanks

Found this link minutes after posting that
http://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause

Didn't know you could use "CASE" in the WHERE like that. Problem solved, but was still curious about the above code. Oh well, thanks again!

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 09:34:33

" Didn't know you could use "CASE" in the WHERE like that"

Tends not to run very efficiently though (may not be a problem for what you are doing ...)

" WHERE time_zone in @timezones"

@timezones will be seen as a single value, not a list of values.

You can use Dynamic SQL to work around that, but that's not the best idea.

You can use a function to "split" the delimited values into a pseudo table and JOIN to that

Or you can hack some code like this:


WHERE	@timezones LIKE '%''' + time_zone + '''%'

which is also fairly inefficient, but handy for "quick-and-dirty"

SLM09
Starting Member

31 Posts

Posted - 2010-04-01 : 09:35:13

Heh, today has not been my day. No sooner than posting the last comment did I realize I will still need this to be dynamic. Timezone is fine since I only need 4 Cases (east, cent, mtn, pac), but my next field is dynamic and could have 1 or 50 possibilities. Is it possible to use the general logic from the link I posted:

WHERE OrderNumber LIKE
CASE WHEN IsNumeric(@OrderNumber) = 1 THEN
@OrderNumber

... but somehow convert it to an "in" so I can continuously add to the list of legal variables?

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 09:36:19

Yours crossed with my post I think. Does mine help?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 09:37:57

Did you mean this?

WHERE ','+@timezones+',' LIKE '%,''' + time_zone + ''',%'

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 09:45:19

Don't need that I think Madhi - @timezones already contains values delimited with single-quote character

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-01 : 09:57:43

Ok. In that case

DECLARE @timezones as varchar(50)
SET @timezones = '(' + '''' + 'east' + '''' + ', ' + '''' + 'west' + '''' + ')'

should be

DECLARE @timezones as varchar(50)
SET @timezones = ',' + '''' + 'east' + '''' + ', ' + '''' + 'west' + '''' + ','

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources