REPLACE Function — Multiple Times (T-SQL)Posted: October 29, 2013
I have a table with a name and number column. The number column has a certain pattern which needs to be discarded. Say, if a record has number like “1234560000789”, only two zeroes should be retained and the rest should be discarded. More than two consecutive zeroes are not allowed as per the business rule.
My take on this question is to convert the bigint to a varchar type. Since the largest bigint is 2^63-1 (9,223,372,036,854,775,807) so Ithe varchar only need size 19.
By using Replace function to replace three ‘000’ to two ’00’ six times to cover all possible bigint numbers. Here is a sample case:
CREATE table Test_Table (Name Varchar(50), ID BIGINT) --DML for the table INSERT INTO Test_Table VALUES('John', 2588500000054) ,('Dave', 65498220004999) ,('Smith', 264955) , ('Smith', 9000000000000000000) select *, Replace(Replace(Replace(Replace(Replace(Replace(Cast(ID as varchar(20)),'000','00'),'000','00'),'000','00'),'000','00') ,'000','00') ,'000','00') as ID2 from Test_Table drop table Test_Table