zero padding - 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

SQL Server 2005 Forums

Transact-SQL (2005)

zero padding

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-03-01 : 14:07:11

I wrote this case statement to handle an issue I am havin with data in two different tables.

case
when len(file_occurence_no)=1 then ('000000'+cast(rtrim(file_occurence_no) as char(7)))
when len(file_occurence_no)=2 then ('00000'+cast(rtrim(file_occurence_no) as char(7)))
when len(file_occurence_no)=3 then ('0000'+cast(rtrim(file_occurence_no) as char(7)))
when len(file_occurence_no)=4 then ('000'+cast(rtrim(file_occurence_no) as char(7)))
when len(file_occurence_no)=5 then ('00'+cast(rtrim(file_occurence_no) as char(7)))
when len(file_occurence_no)=6 then ('0'+cast(rtrim(file_occurence_no) as char(7)))
else cast(rtrim(file_occurence_no) as char(7))
end as [file]

I need to do this because the data in one table is padded with zeros and the other is not, so when I try to join, I lose most records.

I also tried this , but it didn't work, and I am sure why?
right ((('0000000'+cast(rtrim(file_occurence_no) as char(7)))),7) as [files]

Thanks
I was wondering why if there is a more efficient way a

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 14:30:50

If file_occurence_no is numeric, then

replicate('0', 7 - len(cast(file_occurence_no as char(7)))) + cast(file_occurence_no as char(7))

otherwise, if it is already a char(7)

replicate('0', 7 - len(file_occurence_no)) + file_occurence_no

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-03-01 : 14:32:49

An example of datamight be as follows.

In tableA
10
300
5000
7000
18000
760000
5680000

In tableB
0000010
0000300
0005000
0007000
0018000
0760000
5680000

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-01 : 14:41:06

just convert both to numeric data types and they will relate just fine, albeit slowly (since no indexes can be used).

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources