SQLTeam.com | Weblogs | Forums

Wildcard with like operator


#1

Can you please explain what does this mean ( '[cdfprst][orspl0-9]%' ). because I have a model that start with letter R and D but did not obtain or achieve by this code. thanks.

declare @model varchar(20)
declare @model4 varchar(20)
set @model = '[cdfprst][orspl0-9]%'
set @model4 = 'salv%'

select *
from @Sample
where (itemid like @model or itemid like @model4)


#2

First letter must be any of [cdfprst]
Second letter must be any of [orspl] or 0-9
followed by (%) zero or more characters


#3

thanks Kristen. I have an item that start with DIR and REP I need to add this to that code but not working.


#4

Perhaps something like this?

where (itemid like @model OR itemid LIKE 'DIR%' OR itemid LIKE 'REP%' 
       or itemid like @model4)

you can create additional @modelX variables for 'DIR%' and 'REP%' if you like


#5

Yes Kristen this is what I did. I did not include anymore to this code ( '[cdfprst][orspl0-9]%' ) , maybe will affect other logic or items. thank you.


#6

It might be that that was intended to handle prefixes like this (other combinations possible of course, but I note that the first set are in alphabetical order, and the second ones aren't, so apart from "which ones allow a numeric digit" my guess is that the second-poisition letters came from the sequence that they are specified in the second [orsp set - just in case that helps you "guess" what they might specifically relate to)

co
dr
fs
pp
rl
s[0-9]
t[0-9]

That said, if you say

LIKE '[cd][or]%' )

that does NOT only match CO... and DR... (which may be the requirement) but also CR... and DO... (which may match something undesirable, either now ... or in the future :frowning:

If they are actually specific prefixes I think it would be much better to match each one, explicitly, rather than use a wildcard match set that might well turn out to match something undesirable (because it is ambiguous)

You could do something like

SELECT COUNT(*)
FROM MyTable
WHERE itemid LIKE '[cdfprst][orspl0-9]%' 

and see how many you match. Then try messing around with the pattern to see which things are actually matched. Or perhaps this:

SELECT COUNT(*) as [Count], LEFT(itemid, 2) AS [Prefix]
FROM MyTable
WHERE itemid LIKE '[cdfprst][orspl0-9]%' 
GROUP BY LEFT(itemid, 2)
ORDER BY [Prefix]

to see what 2-character prefixes (matching the pattern) actually exist.


#7

Database collation will be case sensitive. In the expression it has written lower case. You are checking with upper case letters. Can you check collation property?


#8

"will be" or perhaps "might be"?