SELECT with a consistent varchar format? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

SELECT with a consistent varchar format?

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-02-03 : 12:02:12

I have a table Product with a field called ProductID of nvarchar(50) data type. There are more than 151,000 records. The values look like as follows (example).

1
10
100
1000
10000-00719
10009-20050
1001
1-00149-00530
10029-31181
3-17-90710-02330
3-17-90710-02484
3-17-91710-02477

As you can see the format and length of the ProductID field is not very consistent. The length of characters in the ProductID varies from 1 to 16, and it occurs with a hyphen, or with two hyphen, or without a hyphen.

Now I want to have a SELECT query which get all the ProductID in the following format only.

XXXXXXXX-XXXXX

As you can see the output ProductID must have a total of 14 characters only; including one hyphen after the first 8 characters from left. If the original ProductID has less characters the SELECT should put leading zeros.

So some examples are;
a) the ProductID 1 should output as 00000000-00001
b) the ProductID 3-17-90710-02330 should output as 31790710-02330
c) the ProductID 10029-31181 should output as 00010029-31181
d) the ProductID 1-00149-00530 should output as 00100149-00530

How can I achieve this in a SELECT query? Please help.

Thanks a in advance for a quick help.

Zee

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-03 : 12:11:17

You could use the stuff function for this. Test for the length of your product id and then pad out as needed. Use replace to remove the existing "-" characters in your current product id's.
Example:
select stuff('1',1,0,'00')

Mike
"oh, that monkey is going to pay"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-03 : 12:24:46

LLet me see if I can write this out following mfemenel's suggestion carefully. If it gives a syntax error, it's not my fault :-)


select 
  stuff(
     stuff(
       replace(ProductId,'-',''),
       1,
       0,
       replicate('0',13-len(replace(ProductId,'-','')))
       ),
     9,
     0,
     '-'
 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 12:29:46

[code]SELECT CASE WHEN CHARINDEX('-',Col)> 0 THEN RIGHT('00000000' +REPLACE(LEFT(Col,LEN(Col)-6),'-',''),8) + RIGHT(Col,6) ELSE '00000000-'+ RIGHT('00000'+ Col,5) END FROM Table[/code]

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-03 : 12:50:00

See, that's why I like this forum. I never knew replicate existed. Never stop learning.

Mike
"oh, that monkey is going to pay"

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-02-03 : 14:26:42

Visakh16 - I tried your query first and it worked perfectly. Thank you so much and everyone else for thier quick feedback.
:)

Zee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources