Update position in a NUMBER

Transact-SQL
1

I have a number 10010005471
I would like to up date position 3 in the number to 5
so the number should now be 1051005471

any number in the 3 position of the field labeled (CODE_NUM) that has a 0 i would like to update it to 5 the number above is an example

How would i do this

2

Try this:

declare @num bigint=10010005471;

select @num
      +case
          when @num>=100 and (@num/power(10,len(@num)-3))%10=0
          then power(10,len(@num)-3)*5
          else 0
       end
;

or this:


declare @num bigint=10010005471;

select @num
      +case
          when substring(cast(@num as varchar(20)),3,1)=0
          then power(10,len(@num)-3)*5
          else 0
       end
;
3

Or:


SELECT Stuff('10010005471', 3, 1, '5');
4

Try this:

UPDATE TABLE_NAME
SET CODE_NUM = STUFF(CODE_NUM, 3, 1, '5')
WHERE SUBSTRING(CODE_NUM, 3, 1) = '0'
1 Like
5

Nice one :+1: if we are dealing with a string type - but the "stuffing" should only be done if third position character is zero, so this probably needs to be wrapped in a case statement.

6

SQL will implicitly convert a numeric value to string for the stuff:

SELECT STUFF(12034, 3, 1, '5'), STUFF(12034.6, 3, 1, '5')

7

Correct, stuff function will, but substring won't.