Adding a number in the middle of existing number 2

[larrydavid]

Hello, I've identified several fields where the length is one or two digits too short. Therefore, it has been decided that these numeric values are to be updated as follows:

If the length is 9-digits, add a new 0 which would be the new 3rd digit from the right.

If the length is 8-digits, add two new 0's which would be the new 2nd & 3rd digit from the right.

I can get this far:

update test set field1 = ??? where len(field1) = 9

I've been looking at SUBSTRING, CHARINDEX and PATINDEX to solve this but am not sure how to put this together. I've been trying to find examples in SQL books online and elsewhere but haven't had much success. If anyone could show me a quick example to get me started that would really help.

Thanks,
Larry

 

[markros]

You need to look into STUFF function. Also, I suggest to start with SELECT first, e.g.

select Field1, case when LEN(Field1) = 9 then
stuff(Field1, 7,0,'0') case when len(Field1) = 8 then
stuff(Field1,7,0,'00') else Field1 end as ChangedField from Test

-------------
I assume that Field1 is character, otherwise we may first want to use STR(Field1) to make is character.

PluralSight Learning Library

 

[PWise]

SUBSTRING ( field1 , 1, 2 ) +'0'+SUBSTRING ( field1 , 3, 7 )
for 9 digit
SUBSTRING ( field1 , 1, 1 ) +'00'+SUBSTRING ( field1 , 2, 7 )
for 8 digits

 

[larrydavid]

Thank you Mark and PWise for your help. I went with the stuff function because I had some other variations which were best suited for the example with the case statement. Either suggestion could be implemented though, and I think you both very much.

Best Regards,
Larry