SQLTeam.com | Weblogs | Forums

Wildcard with like operator


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)


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


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


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


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.


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)


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

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.


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?


"will be" or perhaps "might be"?