get value "4869" like'XXX XX 4869' in sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

get value "4869" like'XXX XX 4869' in sql

Author

Topic

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2013-12-16 : 06:23:20

In DB, The Value is like below

SSN
---
4689
4789
456784567
467897789
048561234
789564678

Output need like below.
----------------------
XXX XX 4689
XXX XX 4789
456 78 4567
467 89 7789
048 56 1234
789 56 4678

How can we write the query in Sql.

V.NAGARAJAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 06:27:25

[code]
SELECT SSN, STUFF(STUFF(COALESCE(REPLICATE('X',9-LEN(SSN)),'') + SSN,4,0,' '),7,0,' ') AS Modified
FROM Table
[/code]

Change it into an update if you want change to be applied to table as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2013-12-16 : 06:29:57

Thank you very much.

quote:
Originally posted by visakh16

SELECT SSN, STUFF(STUFF(COALESCE(REPLICATE('X',9-LEN(SSN)),'') + SSN,4,0,' '),7,0,' ') AS Modified
FROM Table

Change it into an update if you want change to be applied to table as well

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

V.NAGARAJAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources