Replace, Substring, CharIndex magic / usage in SQL Server 2000

Hi,

This post covers 3 amazing functions in SQL Server 2000 ( I am sure you can use them in SQL 2005 or 2008 for that matter). They are Replace, Substring and CharIndex.

So my challenge was to replace ssn in a text and return the full text with only SSN part formatted as SSN : xxx-xx-1234 format. So I combined these functions this way.

1. first find the place/locations where the text SSN appears in the column using
charindex('ssn',status)

2. then use Substring to use the number returned from step 1 and give the lenght of characters to be returned
substring(status,charindex('ssn',status), 11)

3. Finally use replace function, to use step 2 and step 1 i.e. replace the characters returned by both steps with your text i.e.

select replace(status, substring(status,charindex('ssn',status), 11), 'SSN : xxx-xx-') from sometable where condition = condition.

Hope this helps.

Comments

FireMystDL said…
If anyone's interested in speed comparisons on how fast each is (we,, on SQL 2014 anyway), this LIKE vs SUBSTRING vs LEFT/RIGHT vs CHARINDEX article is a good read: http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex

Popular posts from this blog

Making text bold in Adobe Acrobat

Java Keytool Export Private Key, PKCS#12 or .p12 export or conversion from java keystore