Pattern Matching - Searching for Numeric or Alpha or Alpha-Numeric characters in a string

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.

Wildcard

Meaning

%

Any string of zero or more characters.

_

Any single character.

[ ]

Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).

Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA

Patrick Briggs wrote:

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

It is not quite the same. The LIKE pattern in TSQL will also match values like '0123A' and '1248mkfaliw' whereas the one in Access doesn't. The correct way to specify it is to do below:

WHERE Gift_Date NOT LIKE replicate('[0-9]', 4)

The replicate function just simplifies the repetition of the same pattern multiple times.

sql