How do I check the end of a particular string using SQL pattern matching?

Question

I am trying to use sql pattern matching to check if a string value is in the correct format.

The string code should have the correct format of:

alphanumericvalue.alphanumericvalue

Therefore, the following are valid codes:

D0030.2190
C0052.1925
A0025.2013

And the following are invalid codes:

D0030
.2190
C0052.
A0025.2013.
A0025.2013.2013

So far I have the following SQL IF clause to check that the string is correct:

IF @vchAccountNumber LIKE '_%._%[^.]'

I believe that the "_%" part checks for 1 or more characters. Therefore, this statement checks for one or more characters, followed by a "." character, followed by one or more characters and checking that the final character is not a ".".

It seems that this would work for all combinations except for the following format which the IF clause allows as a valid code:

A0025.2013.2013

I'm having trouble correcting this IF clause to allow it to treat this format as incorrect. Can anybody help me to correct this?

Thank you.

Answer 1

This stackoverflow question mentions using word-boundaries: [[:<:]] and [[:>:]] for whole word matches. You might be able to use this since you don't have spaces in your code.

Answer 2

This is ANSI SQL solution

This LIKE expression will find any pattern not alphanumeric.alphanumeric. So NOT LIKE find only this that match as you wish:

IF @vchAccountNumber NOT LIKE '%[^A-Z0-9].[^A-Z0-9]%'

However, based on your examples, you can use this...

LIKE '[A-Z][0-9][0-9][0-9][0-9].[0-9][0-9][0-9][0-9]'

...or one like this if you 5 alphas, dot, 4 alphas

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]'

The 2nd one is slightly more obvious for fixed length values. The 1st one is slighty less intuitive but works with variable length code either side of the dot.

Other SO questions http://stackoverflow.com/questions/2815762 and http://stackoverflow.com/questions/2358147