In select putting dashes in a varchar type field!!

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 2000 Forums

Transact-SQL (2000)

In select putting dashes in a varchar type field!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-08-02 : 11:11:02

Hi All,

I have a table called Table1. It has a field called con_upc_no with varchar type. All records in con_upc_no field has a length of 13 characters, and have the following type of records.

0007940082790
0007940082344

In my select I want to get it like following.

000-7940-082790
000-7940-082344

What is the simplest way to get it in above format?.

Looking for a quick response.

Thanks,

Zee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 11:19:16

[code]DECLARE @Sample TABLE (Col1 VARCHAR(20))

INSERT @Sample
SELECT '0007940082790' UNION ALL
SELECT '0007940082344'

SELECT Col1,
STUFF(STUFF(Col1, 8, 0, '-'), 4, 0, '-'),
LEFT(Col1, 3) + '-' + SUBSTRING(Col1, 4, 4) + '-' + RIGHT(Col1, 6)
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"