How to make a varchar 14 len by adding leading 0's

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)

How to make a varchar 14 len by adding leading 0's

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-22 : 10:04:50

Hi All,

I have a table called Table1 which has a field called ProductID (nvarchar type). The records are numeric, and each record may have different length of characters. The maximum length of character is 13. The records are like this

ProductID
7830005602
7378
64975259599
1380010072

As you can see it has records of different length.
In my SELECT a) first I want to make the ProductID length of 13 characters by adding leading zero's, if lenght of characters is already 13 then I will not add any leading zeros. b) Once all records have a length of 13 character then I want to put a dash "-" after first 8 characters. This way the final lenght of characters would be 14. I want to do a) & b) in one single SELECT statement.

So my final output for above example would be,
00078300-05602
00000000-07378
00649752-59599
00013800-10072

Any quick help would be highly appreciated.

Thanks,

Zee

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-22 : 10:10:56

select left(right('0000000000000'+ productID,13),8)+'-'+right('0000000000000'+ProductID,5)

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:15:03

[code]DECLARE @Sample TABLE (ProductID VARCHAR(200))

INSERT @Sample
SELECT '7830005602' UNION ALL
SELECT '7378' UNION ALL
SELECT '64975259599' UNION ALL
SELECT '1380010072'

SELECT ProductID AS Old,
STUFF(RIGHT('0000000000000' + ProductID, 13), 9, 0, '-') AS New
FROM @Sample[/code]

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

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-22 : 10:17:39

Thasnks guys. It wroked :)....

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-22 : 10:18:00

I am using this one which is also working...

select left( right ( replicate('0', 14) + cast(Productid as varchar(14)), 14), 8 ) + '-' + right ( replicate('0', 14) + cast(Productid as varchar(14)), 5)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-22 : 10:24:19

Why CAST?
You wrote in orignal post that ProductID is NVARCHAR...

And why use 4 functions and 4 concatenations, when Elancaster suggested to use 3 functions and 4 concatenations and I suggested to use 2 functions and 3 concatenations?

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

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-22 : 10:38:31

Peso,

I aggree...

Thanks,

Zee

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-23 : 03:22:58

Where do you want to show data?
Seems you should do this formation in front end application

Madhivanan

Failing to plan is Planning to fail

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-04-23 : 08:48:43

My dear friend,

chk it out this query..

select left(right('0000000000000'+ convert(varchar,7830005602),13),8)+'-'+right('0000000000000'+ convert(varchar,7830005602),5)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 08:54:31

Which is different from what Elancaster suggested?

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

dass05555
Yak Posting Veteran

55 Posts

Posted - 2008-04-23 : 09:15:44

As per elancaster query,he has not converted the datatype int to varchar.but his logic is correct.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-23 : 09:33:27