sql script to format data ? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sql script to format data ?

Author

Topic

jooorj
Posting Yak Master

126 Posts

Posted - 2011-04-24 : 18:42:15

suppose i have the following output: 42001230 and I want to format it like :4200-1-2-30
or other : 20110112 >> to >> 20-11-011-2
thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-24 : 19:07:38

If you know the positions where you want to insert the hyphens, you can use STUFF function in a nested manner such as this:


declare @var varchar(32);
set @var = '42001230';
select stuff(@var,7,0,'-');
select stuff(stuff(@var,7,0,'-'),6,0,'-');
select stuff(stuff(stuff(@var,7,0,'-'),6,0,'-'),5,0,'-');