Only assign a variable if it is a numeric value

Question

Is it possible to do something like this?

declare @ShouldBeNumber as varchar(10)
set @ShouldBeNumber = 1

declare @Number as varchar(10)

select @Number = case when ISNUMERIC(@ShouldBeNumber) then @Number = @ShouldBeNumber else @Number = '' end

i.e. only assign the variable if it is numeric. I have spent the last hour Googling this and have not found anything.

For example, I have looked here: How to check if a variable has a value in a SQL Server 2008 stored procedure

Does your code not work? Can you provide sample data and desired results? — Gordon Linoff
– Gordon Linoff, Commented Apr 16, 2020 at 14:15

Gordon Linoff · Accepted Answer · 2020-04-16 14:16:28Z

1

I think you just want:

select @Number = (case when ISNUMERIC(@ShouldBeNumber) = 1
                       then @ShouldBeNumber else @Number
                  end)

I assume isnumeric() does what you want, although it might have some unwanted behavior depending on your needs (accepting negative numbers, decimals, and exponential notation for instance). The else clause is a no-op; it just assigns the existing value back to @Number.

answered Apr 16, 2020 at 14:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kevin Over a year ago

@w0051977 Just make sure this does what you want. This will result in @ number being null if @ ShouldBeNumber is not a number. Your post made it look like you want it to be a empty string

w0051977 Over a year ago

@Kevin, thanks. I changed my code to: else Number (with an @ sign at the beginning) after I reviewed the answer earlier today.

w0051977 Over a year ago

are you able to take a look at my other question here: stackoverflow.com/questions/61273521/… ?

Suraj Kumar · Accepted Answer · 2020-04-16 16:43:39Z

1

You can also use the try_cast() function as shown below.

DECLARE @ShouldBeNumber AS VARCHAR(10)
SET @ShouldBeNumber = 'A'
DECLARE @number AS VARCHAR(10)

Select @number = isnull(cast(try_cast(@ShouldBeNumber as float) as varchar(10)),'')

SELECT @number

Live db<>fiddle demo.

edited Apr 16, 2020 at 16:43

answered Apr 16, 2020 at 15:51

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

2 Comments

Kevin Over a year ago

you don't really need the case statement. you can just do select @ number = isnull(cast(try_cast(@ShouldBeNumber as int) as varchar),'')

Suraj Kumar Over a year ago

@Kevin Changed as per your suggestion to improve the answer.

Collectives™ on Stack Overflow

Only assign a variable if it is a numeric value

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related