Sql Server:How to Test If a String Has Only Digit Characters

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

Use Not Like

where some_column NOT LIKE '%[^0-9]%'

Demo

declare @str varchar(50)='50'--'asdarew345'

select 1 where @str NOT LIKE '%[^0-9]%'

Check if a string contains only number

I would suggest try_convert():

select (case when try_convert(col, float) is not null then 'valid' else 'invalid' end)

The one possible downside is exponential format; 1e6 is a valid number for instance.

An alternative is the where approach; you just need more complete logic:

select (case when col like '%[^0-9.]%' then 'invalid'
when col like '%.%.%' then 'invalid'
else 'valid'
end)

Check if a variable contains any non-numeric digits in SQL Server

Check for any characters that are not in the range 0 to 9

^ is not in LIKE expressions

IF @rptID LIKE '%[^0-9]%'
--throw error

Check if string consists of letters or numbers only in Transact-SQL

See this similar question about obtaining only fields with alphanumeric data

You can do the following:

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

How to detect if a string contains at least a number?

Use this:

SELECT * FROM Table WHERE Column LIKE '%[0-9]%'

MSDN - LIKE (Transact-SQL)

SQL Server : how to check if a string contains letters only?

We can use SQL Server's enhanced LIKE operator here:

SELECT col
FROM yourTable
WHERE col NOT LIKE '%[^A-Za-z]%';

The logic here is any col value which does not have one or more non letter characters therefore must be only letters.

Fastest way to check if a character is a digit?

I'd be very surprised if you would ever be able to detect any difference between WHERE col LIKE '[0-9]' and any other methods you come up with. But I agree with Denis, put that away in a function so that you use the same check consistently throughout all your code (or at least, if you're avoiding UDFs because of large scans etc., put a marker in your code that will make it easy to change on a wide scale later).

That said, you are most certainly going to see more of a performance hit just by using a scalar UDF than what method you use to parse inside the function. You really ought to compare performance of the UDF vs. doing that inline using CASE. e.g.

SELECT Postal = CONVERT(INT, CASE WHEN SUBSTRING(postal,2,1) LIKE '[0-9]' 
THEN SUBSTRING(postal, 2,1) END)
FROM ...

This will yield NULL if the character is not numeric.

If you are only dealing with checking local variables, it really is not going to matter what parsing method you use, and you are better off focusing your optimization efforts elsewhere.

EDIT adding suggestion to demonstrated JOIN clause. This will potentially lead to less constant scans but is a lot more readable (far fewer substring calls etc):

;WITH v AS 
(
SELECT /* other columns, */ patientPostal,
ss = SUBSTRING(v.patientPostal,2,1),
FROM [whatever table is aliased v in current query]
)
SELECT /* column list */
FROM [whatever table is aliased z in current query]
INNER JOIN v ON z.postal = CONVERT(INT, CASE
WHEN v.ss = '0' THEN ss
WHEN v.ss LIKE '[1-9]' THEN LEFT(v.patientPostal, 3)
END);

How would I determine if a varchar field in SQL contains any numeric characters?

Sql Server allows for a regex-like syntax for range [0-9] or Set [0123456789] to be specified in a LIKE operator, which can be used with the any string wildcard (%). For example:

select * from Address where StreetAddress not like '%[0-9]%';

The wildcard % at the start of the like will obviously hurt performance (Scans are likely), but in your case this seems inevitable.

Another MSDN Reference.

Check if a varchar is a number (TSQL)

ISNUMERIC will do

Check the NOTES section too in the article.



Related Topics



Leave a reply



Submit