Needs to add zero "0" - 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

Needs to add zero "0"

Author

Topic

hizakemi
Starting Member

33 Posts

Posted - 2006-03-02 : 16:53:50

Hi, I need to add zero in records, which has the length less than 6. For example, if the records have '22222' I need to be '022222'. How can I do that in sql tables

SELECT LEN(SAPCode) AS Length, SAPCode
FROM hierarchy
WHERE (LEN(SAPCode) < 6)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-02 : 16:55:52

Select + REPLICATE('0', 6 - DATALENGTH(CAST(SAPCodeAS VARCHAR(6)))) + convert(varchar(6), SAPCode) from hierarchy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 16:56:51

SELECT SAPCode = CASE WHEN DATALENGTH(SAPCode) < 6 THEN '0' + SAPCode ELSE SAPCode END
FROM hierarchy

Tara Kizer
aka tduggan

hizakemi
Starting Member

33 Posts

Posted - 2006-03-02 : 17:11:07

Thank you very much. Your code work very good!
Thk

quote:
Originally posted by Srinika

Select + REPLICATE('0', 6 - DATALENGTH(CAST(SAPCodeAS VARCHAR(6)))) + convert(varchar(6), SAPCode) from hierarchy

hizakemi
Starting Member

33 Posts

Posted - 2006-03-02 : 17:11:51

Thank you. Work very nice!

quote:
Originally posted by tkizer

SELECT SAPCode = CASE WHEN DATALENGTH(SAPCode) < 6 THEN '0' + SAPCode ELSE SAPCode END
FROM hierarchy

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-02 : 17:14:41

If your data coulmns are varchar or nvarchar, this code wiil do it. If they are integer or numeric you will have to cast them to varchar first.


select
	SAPcode,
	NewSAPCode = right('000000'+SAPCode,6)
from
(
select SAPCode = '' union all
select SAPCode = '2' union all
select SAPCode = '22' union all
select SAPCode = '222' union all
select SAPCode = '2222' union all
select SAPCode = '22222' union all
select SAPCode = '222222'
) a

Results:


SAPcode NewSAPCode 
------- ---------- 
        000000
2       000002
22      000022
222     000222
2222    002222
22222   022222
222222  222222

(7 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-03 : 03:12:21

hizakemi, you should post which method you took into consideration as you said all methods worked well. I take MVJ's code as an example


select
	SAPcode as Actual_Code,
	MVJ=right('000000'+SAPCode,6), Tara = CASE WHEN DATALENGTH(SAPCode) < 6 THEN '0' + SAPCode ELSE SAPCode END 
,Srinika=REPLICATE('0', 6 - DATALENGTH(CAST(SAPCode as VARCHAR(6)))) + convert(varchar(6), SAPCode) 
from
(
select SAPCode = '' union all
select SAPCode = '2' union all
select SAPCode = '22' union all
select SAPCode = '222' union all
select SAPCode = '2222' union all
select SAPCode = '22222' union all
select SAPCode = '222222333'
) a where len(SAPCode)<6

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources