Like pattern

Question

Like pattern

RockingAdmin

SSCrazy

Points: 2828
More actions
October 27, 2009 at 2:35 pm

#96020

hi
i am unable to retrieve proper data using like pattern
ex:
create proc OrgData
(@OName varchar(100)
)
as
begin
select orgId,OrgName,Ordloc from OrgType where OrgName like '%' + @OName +'%'
end
it was executed properly. and output showing was wrong
when we gave single character as input parameter,it shows proper results. when passing more char values it was showing wrong answers or null.
can any one tell me the sol.
Thanks
Rock

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

In order to tell you what is happening, you'd have to include some sample data and results to demonstrate what you mean by wrong. To be honest I doubt very much that you are getting "wrong" results, just not the results you expect. SQL Server is pretty good at returning exactly what you have asked for.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Garadin One Orange Chip Points: 29613 More actions · Answer 2

I'm gonna hazard a guess here based on the normal thought process that leads to this question and guess that you're saying:

OName = 'C'

works fine, but

OName = 'C,D,E,F'

does not work.

Is that accurate? If so, you can't provide a list in this fashion. Doing that makes SQL look for a value that literally contains the string 'C,D,E,F', not each individual value.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

dmoldovan SSCertifiable Points: 6334 More actions · Answer 3

I'm still not sure what result do you want to obtain...howhver try to use '[C-F]' / [CDEF] instead of 'C, D, E, F'.

See also BOL ---> "Pattern Matching in Search Conditions"

Garadin One Orange Chip Points: 29613 More actions · Answer 4

dmoldovan (10/29/2009)
I'm still not sure what result do you want to obtain...howhver try to use '[C-F]' / [CDEF] instead of 'C, D, E, F'.
See also BOL ---> "Pattern Matching in Search Conditions"

He can use PATINDEX with pattern matching as you mentioned, but this will also require dynamic SQL.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

dmoldovan SSCertifiable Points: 6334 More actions · Answer 5

Seth, I did not (yet) mention PATINDEX. I'd better include the link:

http://msdn.microsoft.com/en-us/library/ms187489(SQL.90).aspx

than just mention the topic title...sorry...

Garadin One Orange Chip Points: 29613 More actions · Answer 6

dmoldovan (10/29/2009)
Seth, I did not (yet) mention PATINDEX. I'd better include the link:
http://msdn.microsoft.com/en-us/library/ms187489(SQL.90).aspx
than just mention the topic title...sorry...

Heh, no, it was my fault. It's how I was considering doing it when I read it yesterday and I jumped to conclusions. For some reason I as soon as I think pattern matching my brain automatically switches to patindex.

Also, I just rechecked and I must have been typing something wrong, dynamic SQL is not required. So... basically, disregard my previous post entirely :Wow:! Note to self, coffee, then post.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

dmoldovan SSCertifiable Points: 6334 More actions · Answer 7

Seth, never mind, the tite is confusing anyway...

Here is something else that I think that it should be mentioned here:

http://www.databasejournal.com/features/mssql/article.php/3827091/T-SQL-Best-Practices.htm

- scroll down to the "Speed Up Your Searches by Prefixing Wild Card References" section...