June 26, 2019 at 8:35 pm
I have a column named TPN which is a varchar type. 90% of the data are numeric values 5-9 in length. In some rows where there is no numeric values, some rows may have character values i.e. "TPN" , "TBC", NULL, empty string value
I want to pad all numeric values with prefix of zeros where it has numeric values less than 9 digits.
How can update my data to do this?
June 26, 2019 at 9:51 pm
update {your-table}
set TPN = right('000000000' + rtrim(ltrim(TPN)), 9)
where isnumeric(TPN) > 0
July 3, 2019 at 12:33 pm
If you want to be stricter on what should be interpreted as numeric (and are using 2012 or later), you could change the WHERE clause to
where try_cast(TPN as int) is not null
July 3, 2019 at 1:19 pm
UPDATE dbo.table_name
SET TPN = RIGHT(REPLICATE('0', 8) + CAST(TPN AS varchar(9)), 9)
WHERE TPN NOT LIKE '%[^0-9]%'
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 5, 2019 at 11:25 am
Thanks! I forgot about Try_cast .... Need to start using the new functions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply