Not so simple SELECT

[lllyxer]

I have a LastName field which holds this data
LastName
Johnson|Vasques
Adams|Fox|Johnson
Vasques|Smith

Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.

The @LastName can be something like this: “Fox|Smith”.
I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).

In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith .

Thank you

 

[r937]

this will perform terribly no matter how you try to optimize it, because the real problem here is the pipe-delimited list stored in the single field

here's how you do it: in the stored proc, take your @Lastname parameter and extract each item separately

slap pipes around each item, and then slap percent sign wildcards around the pipes

then concatenate pipes to the front and back of LastName column, and compare to each wildcard item

like so:

 where '|'||LastName||'|' like '%|Fox|%'
    or '|'||LastName||'|' like '%|Smith|%'

r937.com | rudy.ca

 

[lllyxer]

This is actually not a physical table it's a TABLE variable which i populate dynamically inside a SP and it wont have more then 100 records at a time, so performance is not an issue here i think.


You said: "in the stored proc, take your @Lastname parameter and extract each item separately".
That's not going to work since @LastName parameter can have more then 2(like in my example) pipe delimited values.
It should be some kind of split function which will be split @LastName variable and return maybe a table with separate values, but i wont be able to use LIKE operator there since it will have more then 1 value to compare. Do not really know what to do now.

 

[r937]

sorry, i have never worked with table variables

yeah, some kind of split function, at worst, a loop (come on, we've all done it)

anyhow, i hope the pattern in the code i gave will help you understand what has to be done

r937.com | rudy.ca

 

[SQLSister]

if you are using sql server, google fn_split

"NOTHING is more important in a database than integrity." ESquared

 

[lllyxer]

****if you are using sql server, google fn_split*****
I'm not looking for the split function, i have it ready. The problem i'm facing is in my last reply before this one.

 

[AlexCuse]

You should not be so quick to dismiss advice. The split function will indeed help you here. Just use charindex in your join.

[COLOR=green]--set up test data
[/color][COLOR=blue]declare[/color] @myTableVar [COLOR=blue]table[/color] (i [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1), LastName [COLOR=blue]varchar[/color](666))

[COLOR=blue]insert[/color] @myTableVar (LastName)
[COLOR=blue]select[/color] [COLOR=red]'here|is|one|value'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'some|name'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'this|has|no|keywords'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'this|does|now|parameter'[/color]
union all [COLOR=blue]select[/color] [COLOR=red]'here|comes|one|more'[/color]

[COLOR=green]--assign your parameter value
[/color][COLOR=blue]declare[/color] @lastname [COLOR=blue]varchar[/color](666)
[COLOR=blue]set[/color] @lastname = [COLOR=red]'some|parameter|comes|here'[/color]

[COLOR=green]--get results
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] a.* 
[COLOR=blue]from[/color] @myTableVar a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] [COLOR=blue]Value[/color] [COLOR=blue]from[/color] dbo.Split(@lastname, [COLOR=red]'|'[/color])
) b
[COLOR=blue]on[/color] [COLOR=#FF00FF]charindex[/color](b.Value, a.LastName) > 0

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

ps- a re-read of your last post has led me to believe that you do not in fact have the split function. Here is one:

[COLOR=blue]CREATE[/color] [COLOR=#FF00FF]FUNCTION[/color] dbo.Split
(
	@list nvarchar(2000),
	@delim nvarchar(5)
)  
RETURNS @e [COLOR=blue]table[/color] 
(
		
	i [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1),
	[COLOR=blue]value[/color] [COLOR=blue]varchar[/color](100)
) 
[COLOR=blue]AS[/color]  
[COLOR=blue]BEGIN[/color]

[COLOR=blue]while[/color] ([COLOR=#FF00FF]charindex[/color](@delim,@list)>0)
[COLOR=blue]begin[/color]  
[COLOR=blue]insert[/color] @e ([COLOR=blue]value[/color])
[COLOR=blue]select[/color] [COLOR=#FF00FF]ltrim[/color]([COLOR=#FF00FF]rtrim[/color]([COLOR=#FF00FF]substring[/color](@List,1,[COLOR=#FF00FF]Charindex[/color](@delim,@list)-1))) 
    
[COLOR=blue]set[/color] @List = [COLOR=#FF00FF]substring[/color](@list,[COLOR=#FF00FF]charindex[/color](@delim,@list)+len(@delim),len(@list))

[COLOR=blue]end[/color]  

    [COLOR=blue]insert[/color] [COLOR=blue]Into[/color] @e ([COLOR=blue]value[/color])
    [COLOR=blue]select[/color] [COLOR=#FF00FF]ltrim[/color]([COLOR=#FF00FF]rtrim[/color](@List))

[COLOR=blue]return[/color]

[COLOR=blue]END[/color]

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

This is what led me to believe that you do not have it:

It should be some kind of split function which will be split @LastName variable and return maybe a table with separate values

The split function that SQLSister mentions does exactly this.

hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom