Having trouble with wildcard/len syntax validation query

• cuseman03

  SSC Enthusiast

  Points: 194

  More actions

  July 29, 2019 at 5:42 pm

  #3666920

  hi.  So I have a field where the proper format should be

  XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX

  So the pattern is 8 hyphen 4 hyphen 4 hyphen 4 hyphen and lastly 12.  It can only be 0-9 or A-Z, no other characters.

  I was using something like this "like '%[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'  ...etc. but no luck

  I want to be able to find any value that does not conform to both rules in the syntax (# of characters and type)

  Example:

  E02D0914-BF24-43BC-ABC3-0D056E76C6BE

  For example, if the above had 7 characters before the first hyphen and not 8, it would be wrong.  If it had 8 characters but lets say one of them was $, it would be wrong.  Much like a phone number.  The first 3 HAS to be an area code and that can only be 3 numbers and HAS to be only numeric 0-9

  Thanks for any help

• crow1969

  Hall of Fame

  Points: 3001

  More actions

  July 29, 2019 at 7:57 pm

  #3666945

  The LIKE approach works for me.  Do you get bad results from this (note, make sure no new-line characters invade the LIKE string):

  drop table #temp
  
  create table #temp
  (col1 varchar(40))
  
  insert into #temp values 
  ('12345678-9abc-defg-hijk-lmnopqrstuvw'),-- just right
  ('12345678-9abc-defg-hijk-lmnopqrstuv'),-- group 5 too short
  ('12345678-9abc-defg-hij-lmnopqrstuvw'),-- group 4 too short
  ('12345678-9abc-defgg-hijk-lmnopqrstuvw'),-- group 3 too long
  ('12345678-9abc-defg-hijk-lmnopqrstuv$'),-- $ disallowed
  ('12345678-9abc-defg-hijk-lmnopqrstuvwxyz') -- group 5 too long
  
  select *
  from #temp
  where col1 like '[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]-[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]-[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]-[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]-[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]'
  

• Jeff Moden

  SSC Guru

  Points: 1004412

  More actions

  July 29, 2019 at 8:43 pm

  #3666959

  An stop using [A-Z] for this if these are GUIDs.  It should be "[A-D] if your server is case insensitive and {A-Da-d] if your server IS case sensitive.

  A better question would be, why on this good Green Earth are you storing GUIDs as a 36 byte character rendition instead of as a proper 16 Byte UNIQUEIDENTIFIER, which has all such checks built in.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)