469,626 Members | 949 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

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?
Jul 20 '05 #1
1 4375
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.city, 'sydney, new york') > 0

This won't be fast on large datasets. Normalization is a much better
technique.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Xah Lee | last post: by
5 posts views Thread by Jamie Jackson | last post: by
10 posts views Thread by javuchi | last post: by
7 posts views Thread by Captain Dondo | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.