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
9:29 PM
Post a Comment

Popular posts from this blog

Making text bold in Adobe Acrobat

If you want to make a text bold in Adobe Acrobat, you will have to 1. first select Advanced Editing Toolbar. 2. In advanced edit toolbar, select TouchUp Text tool. 3. Then select the text you want to make bold, right click, and select properties. 4. Under Text tab, you will find Font option. 5. Select Font option and you will see Bold option for the font that is being used. For example, if selected text is font type Verdana , then under Fonts option, you should see a Verdana Bold option. If you do not see respective Bold Font, then you would have to pick a font that supports Bold Option. I know, its unlike Word but atleast we know the way now how to do make text bold in Adobe Acrobat.
Read more

Translators not loaded --- Translators error in Dreamweaver for coldfusion.htm

I use Dreamweaver 8 on Windows XP and recently started to get this error message for no reason. It just started one day. the following translators were not loaded due to errors coldfusion.htm has configuration information that is invalid. This could also happen for XSLT.htm has configuration information that is invalid, ASP.htm: has configuration information that is invalid. ASP.NET.htm: has configuration information that is invalid. and so on. The solution to this problem is to delete FileCache_X.dat files from the Dreamweaver user configuration folder, where X is a random number and it could be different for your system. See below Dreamweaver 8 on Windows XP: C:\Documents and Settings\[username]\Application Data\Macromedia\Dreamweaver 8\Configuration All the best. I was able to successfully solve this problem
Read more

Hawks v/s PowerBuilders. July 23 2009. Match 13.

Just last night, I finished writing about Kareem from Dalcha. Today it was another Karim from Power Builders, who hit some crazy Afridi style sixes to guide Power Builders to a victory. The game was full of excitement and pressure. The Power Builders gave a complete team performance, much like their opponents, Hawks. Sagar won the toss and elected to bat first. The opening duo, Sashi and Bhavin started well. They put on 18 runs of the first 2 overs. Bhavin scored 16 and Sashi made 8. The Hawks went for their shots and were caught on the boundary. Satish of Power Builders was very impressive as He caught 3 catches in the deep. Vinay was playing well and was making the life difficult for fielders, especially at long on as the ball would turn twice or thrice before reaching the fielder. You all know who was standing there. :-). Vinay was caught on the boundary by an absolutely brilliant one handed catch by Harsha. Vinay top scored for Hawks with 22 runs and in the process, hit some beauti...
Read more