REPLACE Function — Multiple Times (T-SQL)


  • 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.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/91b8c523-c6c3-4179-a517-193f7180d6a1/how-to-discard-patterns-in-a-column?forum=transactsql

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

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s