Sql Server : How to test if a string has only digit characters

sql

prompt-engineering

best-practices

dataframe

byAnton Shumikhin·Dec 25, 2024

For a quick and efficient way to check if a string consists only of digits in SQL Server, we use the LIKE operator with the pattern '%[^0-9]%' to catch any non-digit characters. Here’s an out-of-the-box example:

DECLARE @TestString VARCHAR(100) = '12345'; -- You could also set this to '123abc' to test for non-digits. 
IF @TestString NOT LIKE '%[^0-9]%' -- Anyone else getting regex deja vu?
    PRINT 'Only digits'; -- Pure as a newborn integer!
ELSE
    PRINT 'Includes non-digits'; -- Just like my Pi-pie recipe.

This snippet checks whether @TestString is armored with numbers only, thanks to our pattern [^0-9] that flags any non-numeral intruder.

Deep Dive: Under the hood of Character Check

The ISNUMERIC Stumbling Block

While ISNUMERIC might look promising for our digit-check task, it's a wolf in sheep's clothing. It considers strings with currency symbols, +, - signs, and periods (that represent decimal points) as numeric. Hence, it yields you a bucketful of false positives. Here's a demo:

SELECT ISNUMERIC('$123'), ISNUMERIC('-123'), ISNUMERIC('123.45'); 
-- Outputs: 1, 1, 1 (Hmmm... Something's fishy here!)

That's clearly not what we're aiming at, since our goal is to find digits only.

Tackling Edge Cases: Be wary of the Empty Strings and Whitespaces

Pairing NOT LIKE with an extra empty or whitespace string check can improve accuracy, just like adding a sidekick to Batman:

IF LEN(@TestString) > 0 AND @TestString NOT LIKE '%[^0-9]%' AND @TestString NOT LIKE '%[ ]%'
    PRINT 'Only digits, no spaces'; -- Like a book with only number pages, pretty boring but true!
ELSE
    PRINT 'Includes non-digits or spaces'; -- We've added some mystery to our book.

This ensures we skim off those corner cases that might play hide and seek otherwise.

Advanced Techiques: Precision Queries with Alternate Tools

The Adventure of PATINDEX

Hound of Baskerville or LIKE is not your only option, you also have PATINDEX. Here’s how it would look:

IF PATINDEX('%[^0-9]%', @TestString) = 0 -- A digit by any other name would still be as numeric!
    PRINT 'Only digits'; -- The mystery of the numerical digits is solved!
ELSE
    PRINT 'Includes non-digits'; -- Back to the deduction board, Watson!

T-SQL Marvels: Patterns and Advanced Queries

You can use T-SQL to check based on patterns. Let's steal an idea from the Three Musketeers and all check together:

SELECT IIF(SUM(CASE WHEN SUBSTRING(@TestString, v.number, 1) LIKE '[0-9]' THEN 0 ELSE 1 END) = 0, 
           'Only digits', 
           'Includes non-digits')
FROM master.dbo.spt_values v
WHERE v.type = 'P' AND v.number BETWEEN 1 AND LEN(@TestString);
-- For when you absolutely, positively have to check every single character, accept no substitutes.

This bit of wizardry uses a tally table to independently investigate each character in the string.

Culture Shock: Numbers can be Deceptive

Numbers can dress differently in different locales with commas(,) or periods(.) as thousand separators or decimal points. For digits-only checks, commas and periods should be treated as intruders, just like non-digit characters.

explain-codes / Sql / Sql Server : How to test if a string has only digit characters

Linked

How do I check if a string represents a number (float or int)?



Convert a string to int using SQL query



Detect if value is number in MySQL



How can I check if a string represents an int, without using try/except?



Pure JavaScript: a Function Like jQuery's isNumeric()

