How to Determine If a String Is Numeric in SQL

How to get if a string is a number in T-SQL

The function ISNUMERIC returns whether a string is numeric, but will return true for non-integers.

So you could use:

WHERE ISNUMERIC(str) AND str NOT LIKE '%.%' AND str NOT LIKE '%e%' AND str NOT LIKE '%-%'

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)

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]%'

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)

How to check a string is numeric using Regex in SQL Server?

You can use TRY_CAST() or TRY_CONVERT() without the need to Regex expression

SELECT CASE WHEN TRY_CAST(@Amount AS INT) IS NULL THEN 0 ELSE 1 END IsNumericResult

You can change INT there to numeric or decimal as needed.

eg:

SELECT V Value,
       CASE WHEN TRY_CAST(V AS DECIMAL(10,2)) IS NULL
            THEN 0
            ELSE 1
       END IsNumericResult
FROM
(VALUES ('10.50'), ('+'), ('$'), ('-'), ('11')) T(V);

Here is a db<>fiddle to see how it's working.

How can I determine if a string is numeric in SQL?

You can use REGEXP_LIKE:

SELECT 1 FROM DUAL
WHERE REGEXP_LIKE('23.9', '^\d+(\.\d+)?$', '') 

Check if a varchar is a number (TSQL)

ISNUMERIC will do

Check the NOTES section too in the article.

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]%'

cross-dbms way to check if string is numeric

Below are three separate implementations for each of SQL Server, MySQL and Oracle. None use (or can) the same approach, so there doesn't seem to be a cross DBMS way to do it.
For MySQL and Oracle, only the simple integer test is show; for SQL Server, the full numeric test is shown.

For SQL Server:
note that isnumeric('.') returns 1.. but it can not actually be converted to float. Some text like '1e6' cannot be converted to numeric directly, but you can pass through float, then numeric.

;with tmp(x) as (
    select 'db01' union all select '1' union all select '1e2' union all
    select '1234' union all select '' union all select null union all
    select '1.2e4' union all select '1.e10' union all select '0' union all
    select '1.2e+4' union all select '1.e-10' union all select '1e--5' union all
    select '.' union all select '.123' union all select '1.1.23' union all
    select '-.123' union all select '-1.123' union all select '--1' union all
    select '---1.1' union all select '+1.123' union all select '++3' union all
    select '-+1.123' union all select '1 1' union all select '1e1.3' union all
    select '1.234' union all select 'e4' union all select '+.123' union all
    select '1-' union all select '-3e-4' union all select '+3e-4'  union all
    select '+3e+4' union all select '-3.2e+4' union all select '1e1e1' union all
    select '-1e-1-1')

select x, isnumeric(x),
    case when x not like '%[^0-9]%' and x >'' then convert(int, x) end as SimpleInt,
    case
    when x is null or x = '' then null -- blanks
    when x like '%[^0-9e.+-]%' then null -- non valid char found
    when x like 'e%' or x like '%e%[e.]%' then null -- e cannot be first, and cannot be followed by e/.
    when x like '%e%_%[+-]%' then null -- nothing must come between e and +/-
    when x='.' or x like '%.%.%' then null -- no more than one decimal, and not the decimal alone
    when x like '%[^e][+-]%' then null -- no more than one of either +/-, and it must be at the start
    when x like '%[+-]%[+-]%' and not x like '%[+-]%e[+-]%' then null
    else convert(float,x)
    end
from tmp order by 2, 3

For MySQL

create table tmp(x varchar(100));
insert into tmp
    select 'db01' union all select '1' union all select '1e2' union all
    select '1234' union all select '' union all select null union all
    select '1.2e4' union all select '1.e10' union all select '0' union all
    select '1.2e+4' union all select '1.e-10' union all select '1e--5' union all
    select '.' union all select '.123' union all select '1.1.23' union all
    select '-.123' union all select '-1.123' union all select '--1' union all
    select '---1.1' union all select '+1.123' union all select '++3' union all
    select '-+1.123' union all select '1 1' union all select '1e1.3' union all
    select '1.234' union all select 'e4' union all select '+.123' union all
    select '1-' union all select '-3e-4' union all select '+3e-4'  union all
    select '+3e+4' union all select '-3.2e+4' union all select '1e1e1' union all
    select '-1e-1-1';

select x,
    case when x not regexp('[^0-9]') then x*1 end as SimpleInt
from tmp order by 2

For Oracle

case when REGEXP_LIKE(col, '[^0-9]') then col*1 end

---

Related Topics