SEO

#1 (**permalink**) 02-29-2008, 03:53 AM

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?

#2 (**permalink**) 02-29-2008, 03:53 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL matching two multiple valued strings