Using Like to check against a multivalue 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

General SQL Server Forums

New to SQL Server Programming

Using Like to check against a multivalue string

Author

Topic

its_me
Starting Member

6 Posts

Posted - 2012-09-28 : 14:28:13

Hi All --

I am trying to write my query like this:

select fields from table t where someField = 'someThing'
and ', '|| t.REGION || ', ' like '%, ' || 'abc' ||', %'

But this only works if I am comparing a single value. If my string has multiple values, this qry returns no results.

my string can contain multiple values - For Ex: 'abc, def, xyz, hys' or it can be a single value like 'abc'.

Any ideas? I highly appreciate any help or ideas around this.

Thanks Much!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-28 : 14:39:12

Are you using Microsoft SQL Server? This forum specializes in MS SQL Server, so experts on other DBMS are likely to be far and few in between. You might get better/faster answers at other forums such as dbforums.com

its_me
Starting Member

6 Posts

Posted - 2012-09-28 : 14:41:57

I am using MS SQL Server

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-28 : 14:51:18

I was asking only because that || operator is not valid in SQL Server. If your input string contains exact region names, you can use something like this:

SELECT fields FROM table1 t
WHERE somefield = 'something'
AND ','+'abc,efg,def'+',' LIKE '%,'+t.REGION+',%';

But, two problems with it: a) it is likely to be not very efficient. b) it won't work if you are looking for partial names.

To make it efficient and add the ability to look for partial strings, you will need a string splitter. A good one is here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ You can copy and paste the code in Fig. 21 and run it to install the function.

Once you have the function, you can use it like this:

SELECT
	fields
FROM
	table1 t
	INNER JOIN  dbo.DelimitedSplit8K('abc,efg,def',',') d
		ON t.REGION LIKE '%'+d.Item+'%';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources