1

Using SQL I'm trying to verify that a field contains only UPPERCASE characters, numbers and may contain a '-'.
I'm trying to weed out results that have lowercase characters or any symbol other than the '-'.

So far I have

WHERE ItemCode LIKE UPPER(ItemCode) Collate SQL_Latin1_General_CP1_CS_AS

However this allows for all other interesting characters and symbols.

Thanks for your help.

SQB
  • 3,926
  • 2
  • 28
  • 49
MikeR
  • 25
  • 1
  • 7
  • 1
    Probably you can use regular expressions in your RDBMS for this kind of filtering, but the syntax is rdbms specific. – Lajos Veres Oct 30 '13 at 13:46

1 Answers1

1
WHERE  ItemCode NOT LIKE '%[^-A-Z0-9]%' Collate Latin1_General_Bin

Should do the trick.

The -A-Z0-9 matches the - character or items in the sort order ranges A-Z,0-9. The ^ negates that expression.

So combining with NOT LIKE finds those values where there is not a character that is neither a number, upper case or -.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845