SQL matching two multiple valued strings

[Oat]

I am a little stomped and wandering if someone might have an idea how
to go about doing this.

following on from this guide
http://www.4guysfromrolla.com/webtech/031004-1.shtml on matching a
comma-delimited string, I would like to expand on this and match two
comma-delimited string in a sproc.

In my database, table A have a city field containing a comma delimited
string ie 'sydney, new york, chicago'. I am passing a similar
comma-delimited string to a sproc and returning matcheing id.

so, we have table A:
id/city
1/sydney, new york, chicago
2/new york, san antonio
3/beijing, sydney
4/london,beijing

passing string 'sydney, new york'

need to return: id 1,2,3 (1,2 match new york and 1,3 matching sydney)

any ideas?

[Ross Presser]

On 10 Aug 2004 10:51:01 -0700, Oat wrote:

I am a little stomped and wandering if someone might have an idea how
to go about doing this.

following on from this guide
http://www.4guysfromrolla.com/webtech/031004-1.shtml on matching a
comma-delimited string, I would like to expand on this and match two
comma-delimited string in a sproc. In my database, table A have a city field containing a comma delimited
string ie 'sydney, new york, chicago'. I am passing a similar
comma-delimited string to a sproc and returning matcheing id.

so, we have table A:
id/city
1/sydney, new york, chicago
2/new york, san antonio
3/beijing, sydney
4/london,beijing

passing string 'sydney, new york'

need to return: id 1,2,3 (1,2 match new york and 1,3 matching sydney)

any ideas?

First, define the dbo.Split(@lst, @splitOn) as they do in that article.

Next, define a second UDF:

create FUNCTION match( @s1 varchar(1000), @s2 varchar(1000) )
returns int
as
begin
declare @r int
select @r = count(*) from
(select value from dbo.Split(@s1,' ,')) V1,
(select value from dbo.Split(@s2,' ,')) V2
where V1.value = V2.value
return(@r)
end
GO

Now you can do

SELECT A.ID FROM A
WHERE dbo.match(A.cit y, 'sydney, new york') > 0

This won't be fast on large datasets. Normalization is a much better
technique.