Как найти число в строке sql

asked Jul 17, 2010 at 1:37

Is there an easy way to figure out if a varchar is a number?

Examples:

abc123 —> no number

123 —> yes, its a number

I ran into the need to allow decimal values, so I used not Value like '%[^0-9.]%'

Damien_The_Unbeliever noted that his was only good for digits

Wade73 added a bit to handle decimal points

neizan made an additional tweak as did notwhereuareat.

Unfortunately, none appear to handle negative values and they appear to have issues with a comma in the value…

Here’s my tweak to pick up negative values and those with commas

declare @MyTable table(MyVar nvarchar(10));
insert into @MyTable (MyVar) 
values 
(N'1234')
, (N'000005')
, (N'1,000')
, (N'293.8457')
, (N'x')
, (N'+')
, (N'293.8457.')
, (N'......')
, (N'.')
, (N'-375.4')
, (N'-00003')
, (N'-2,000')
, (N'3-3')
, (N'3000-')
;

-- This shows that Neizan's answer allows "." to slip through.
select * from (
select 
    MyVar
    , case when MyVar not like N'%[^0-9.]%' then 1 else 0 end as IsNumber 
from 
    @MyTable
) t order by IsNumber;

-- Notice the addition of "and MyVar not like '.'".
select * from (
select 
    MyVar
    , case when MyVar not like N'%[^0-9.]%' and MyVar not like N'%.%.%' and MyVar not like '.' then 1 else 0 end as IsNumber 
from 
    @MyTable
) t 
order by IsNumber;

--Trying to tweak for negative values and the comma
--Modified when comparison
select * from (
select 
    MyVar
    , case 
        when MyVar not like N'%[^0-9.,-]%' and MyVar not like '.' and isnumeric(MyVar) = 1 then 1
        else 0 
    end as IsNumber 
from 
    @MyTable
) t 
order by IsNumber;

DECLARE @A nvarchar(100) = '12'
IF(ISNUMERIC(@A) = 1)
BEGIN
    PRINT 'YES NUMERIC'
END

Neizan’s code lets values of just a «.» through. At the risk of getting too pedantic, I added one more AND clause.

declare @MyTable table(MyVar nvarchar(10));
insert into @MyTable (MyVar) 
values 
    (N'1234')
    , (N'000005')
    , (N'1,000')
    , (N'293.8457')
    , (N'x')
    , (N'+')
    , (N'293.8457.')
    , (N'......')
    , (N'.')
    ;

-- This shows that Neizan's answer allows "." to slip through.
select * from (
    select 
        MyVar
        , case when MyVar not like N'%[^0-9.]%' then 1 else 0 end as IsNumber 
    from 
        @MyTable
) t order by IsNumber;

-- Notice the addition of "and MyVar not like '.'".
select * from (
    select 
        MyVar
        , case when MyVar not like N'%[^0-9.]%' and MyVar not like N'%.%.%' and MyVar not like '.' then 1 else 0 end as IsNumber 
    from 
        @MyTable
) t 
order by IsNumber;

Do not forget to exclude carriage returns from your data!

As in:

SELECT 
  Myotherval
  , CASE WHEN TRIM(REPLACE([MyVal], char(13) + char(10), '')) not like '%[^0-9]%' and RTRIM(REPLACE([MyVal], char(13) + char(10), '')) not like '.' and isnumeric(REPLACE([MyVal], char(13) + char(10), '')) = 1 THEN 'my number: ' +  [MyVal]
             ELSE ISNULL(Cast([MyVal] AS VARCHAR(8000)), '')
        END AS 'MyVal'
FROM MyTable

In case you want to add a constraint on a field:

Positive integer with fixed length

ALTER TABLE dbo.BankBranchType  
ADD CONSTRAINT CK_TransitNumberMustBe5Digits 
CHECK (TransitNumber NOT like '%[^0-9]%'  
       AND LEN(TransitNumber) = 5)

To check the Number, Currency, and Amount, use the below SQL fragment.

@value NOT LIKE '%[^0-9.,]%'

For a quick win, refer to the below example:

Function example:

CREATE FUNCTION [dbo].[fnCheckValueIsNumber](
    @value NVARCHAR(255)=NULL
)RETURNS INT  AS BEGIN
    DECLARE @ReturnValue INT=0
    IF EXISTS (SELECT * WHERE @value NOT LIKE '%[^0-9.,]%') SELECT @ReturnValue=1
RETURN @ReturnValue;

Execution result

SELECT [dbo].[fnCheckValueIsNumber]('12345')
RESULT = 1

SELECT [dbo].[fnCheckValueIsNumber]('10020.25')
RESULT = 1

SELECT [dbo].[fnCheckValueIsNumber]('10,020.25')
RESULT = 1

SELECT [dbo].[fnCheckValueIsNumber]('12,345ABCD')
RESULT = 0