How to check numeric value in sql?

Question

Here is my code. I want only numeric character. If it is other than numeric, should not print.

Here @msg is substring of one of string,which dynamically created.So I can not change type of @msg to int.It can be ',,3' or '4,,,',etc.Sometime @msg contain comma,so I want to check for numeric.Isnumeric() consider comma as numeric.I gives 1 as result.How to handle it?

Declare @msg varchar(5)
    set @msg = ',5'
    if(isnumeric(@msg) = 1)
    begin
        print 'Numeric'
        -- logic
    end

Can you remove the `,` without consequences? There is no ',4,5,6,,7' as input? — rene, Feb 17 '14 at 11:23
Note due to localization both "1.234.567,89" and "1,234,567.89" can be valid/invalid formated numbers. — jean, Feb 17 '14 at 14:02
Not sure if I understand you correctly, you consider containing `ONE comma as numeric but containing multiple commas as not numeric` or as `not numeric in both cases`? as for the last you could just add `AND CHARINDEX(',', @msg) = 0` to your `IF` — DrCopyPaste, Feb 17 '14 at 15:58

score 0 · Answer 1 · edited May 23 '17 at 10:25

IsNumeric certainly has idiosyncrasies. They are well discussed in the community. See here for some discussion seesimple talk.

What you actually need to do is not quite clear between the questions and comments, but you might get by with something as simple as checking to make sure there isn't a comma either:

DECLARE @msg VARCHAR(5)
SET @msg = ',5'
IF IsNumeric(@msg) = 1 AND CharIndex(',',@msg,1) = 0
BEGIN
    PRINT 'Numeric'
    -- logic
END

For more ideas on how to do a better IsNumeric see similar questions like efficient-isnumeric-replacements-on-sql-server.

score 0 · Answer 2 · edited Nov 05 '14 at 19:02

0

I think you just want to validate that your variable contains only numeric value or not. use the query..

declare @ClientID varchar(100)
declare @filename varchar(1000)
set @filename = '3081PDShivamCSS_MDaily.Dat'
set @ClientID = substring('3081PDShivamCSS_MDaily.Dat', 1,4)

If @ClientID Not LIKE '%[^0-9]%'
Print Numeric

edited Nov 05 '14 at 19:02

GingerPlusPlus

5,336
1
29
52

answered Nov 05 '14 at 18:44

user4219959

1

How to check numeric value in sql?

2 Answers2