1

How can I check if a string consists of letters or numbers ONLY in Transact-SQL?

Here the string has datatype nchar, 'letters' specifically refer to latin characters. Database environment is Microsoft SQL Server 2014.

Example for desired result:

C172E returns True
412?A returns False  //'?' is neither a letter nor a number

I've done some searching but only found the built-in ISNUMERIC() function, which is for numbers only.

Is there a Transact-SQL solution for this problem?

Lang Zhou
  • 53
  • 1
  • 3
  • 7

3 Answers3

4

See this similar question about obtaining only fields with alphanumeric data

You can do the following:

FIELD NOT LIKE '%[^a-zA-Z0-9]%'

gordon so
  • 190
  • 1
  • 7
  • Thanks, after some testing I found out that this works for `nvarchar` type. There could be spaces in `nchar` string which needs to be considered, otherwise it would always return false. – Lang Zhou Dec 28 '20 at 17:39
2

You can use not like:

(case when str not like '%[^a-zA-Z0-9]%' then 1 else 0 end) as isalnum_flag

This is saying that the string has no non-alphanumberic characters.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You are looking at using Regex to do this. [a-zA-Z] is char, [0-9] is numeric, [a-zA-Z0-9] is both. You mention having to add in spaces? \s is white space characters (this could be more than just the fields.

This one is a bit more involved (more depth).

Gregory A Beamer
  • 16,870
  • 3
  • 25
  • 32