I faced a simple but challenging issue recently. A table had a nvarchar column with data in the following format: 91-39-45-3845
The Column was expected to be in the format 913945-3845 with the first two hyphens (-) removed.
Here’s how the requirement was achieved:
DECLARE @TT table
(
ItemDescription nvarchar(20)
)
INSERT INTO @TT VALUES('91-39-45-3845')
INSERT INTO @TT VALUES('45-24-85-5643')
INSERT INTO @TT VALUES('57-54-92-8835')
SELECT * FROM @TT
UPDATE @TT
SET ItemDescription = REPLACE(SUBSTRING(ItemDescription, 1, 7), '-', '')
+ SUBSTRING(ItemDescription, 8, DATALENGTH(ItemDescription))
SELECT * FROM @TT
OUTPUT:
Before the query was run
After the query was run
![image image](//lh3.ggpht.com/_0j4bzarlOBg/Sq3ugO6pSCI/AAAAAAAAAaY/KBXODg6kFBg/image_thumb%5B4%5D.png?imgmax=800)
About The Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of
DotNetCurry,
DNC Magazine for Developers,
SQLServerCurry and
DevCurry. He has also authored a couple of books
51 Recipes using jQuery with ASP.NET Controls and a new one recently at
The Absolutely Awesome jQuery CookBook.
Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.
Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook
2 comments:
Another method is
select stuff(replace(ItemDescription,'-',''),7,0,'-') from @TT
You can easily convert this to update
Madhivanan
http://beyondrelational.com/blogs/madhivanan
Thanks Madhivanan :)
Post a Comment