finding matches using LIKE logic. I''m sure there''s an easier way...

• Greg Johnson-186398

  SSCommitted

  Points: 1703

  More actions

  January 21, 2005 at 9:29 am

  #61228

  I want to be able to match

  lower, lower, then at least 1 and up to 6 caps

  so, the following strings should match:

  'cyAAS'

  'qmB'

  'oaTMEALY'

  Right now, I've got:

  select blah

  from blah

  where MyStringCol like '[a-z][a-z][A-Z]'

  or MyStringCol like '[a-z][a-z][A-Z][A-Z]'

  or MyStringCol like '[a-z][a-z][A-Z][A-Z][A-Z]'

  or MyStringCol like '[a-z][a-z][A-Z][A-Z][A-Z][A-Z]'

  or MyStringCol like '[a-z][a-z][A-Z][A-Z][A-Z][A-Z][A-Z]'

  or MyStringCol like '[a-z][a-z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]'

  I feel like a schmuck writing that code. What I'm looking for is some way of saying "any number of [these valid chars]"

  Ask questions if I haven't been clear, I'll be online and respond immediately until this is sorted (or until happy hour).

  Thanks,

  Greg

• Osoba

  SSCommitted

  Points: 1964

  More actions

  January 21, 2005 at 10:40 am

  #538548

  May need to edit, but how about something like this:

  select [ID],[DESC] from

  where

  --Lower case at positions 1-2

  PATINDEX('%[a-z]%',(cast([id] as varchar(50)) COLLATE Latin1_General_BIN))<3 and

  --CAPS position at 3 or more

  PATINDEX('%[A-Z]%',(cast([id] as varchar(50)) COLLATE Latin1_General_BIN))>2 and

  --CAPS position less than 7

  PATINDEX('%[A-Z]%',(cast([id] as varchar(50)) COLLATE Latin1_General_BIN))<7

   

  ..hope that helps