CODEWITHSUNDEEP
sql-server
Lee Y.
Lee Y.

Reputation: 119

SQL Server - count of COLUMN X where COLUMN X has a letter in it

In my database, I have a field called "recipient ID". The "recipient ID" should be only numerical numbers. To ensure only numerical numbers are in the "recipient ID" column, I want to do a count of how many records where the "recipient ID" has an alpha character in it.
How do I do that? (if patindex is used, how do I set the parameters ?)

Upvotes: 0

Views: 29

Answers (1)

Martin Smith
Martin Smith

Reputation: 452957

Assuming that you are only interested in ASCII alphabetic characters then you can use the below.

SELECT COUNT(*)
FROM YourTable
WHERE ColumnX LIKE '%[A-Za-z]%'

You should compare with

SELECT COUNT(*)
FROM YourTable
WHERE ColumnX LIKE '%[^0-9]%'

(the second one finds rows where the column contains any character that is not a digit)

Upvotes: 1

Related Questions