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
How to Get Last 7 Days Data from Current Datetime to Last 7 Days in SQL Server
Select Distinct Values from One Table and Join With Another Table
Calling an API from SQL Server Stored Procedure
How to Merge Multiple Rows into Single in Oracle
Add Single Quotes to Results in a Column from a SQL Query
Count All Records Per Day in a Specific Month
How to Check If Value Is Inserted Successfully or Not
How to Get the Last 12 Months from the Current Date
How to Make a Join of 3 Tables in Jpa
How to Select Last 5 Rows in a Table Without Sorting
Using Where Clause With Two Type of Conditions on Same Column
Insert Multiple Rows Without Repeating the "Insert into ..." Part of the Statement
Sql Take Just the Numeric Values from a Varchar
Update Multiple Rows in a Table from Another Table When Condition Exists
How to Get Latest Records for Each Ledger Account Based on Variationid
Sql to Generate a List of Numbers from 1 to 100
Unable to Get Min and Max Time from a Datetime Column from Every Backday