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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-07-11 : 11:15:39
|
| Keith writes "Hello - I am using the following SQL statement below to query against a table in our accounting system. Basically, it returns four columns... Client Number, Client Description, Matter Number, Matter Description. Most of the client numbers are four digits (e.g. 2222). However, a small amount of the client numbers are two or three digits (e.g. 10). In the following script, is there a way to append zeros to the beginning of the client numbers that are only two or three digits long? I don't want to run an update statement to modify some of the rows in the table. I just want to query a consistent set of data where all of the client numbers are listed with four digits.Thanks.Keithselect clnt_no, clnt_name, matt_no, matt_descr from v1entitywhere caolid <> '0' and clnt_no <> 'Delete' and matt_no <> 'Delete'order by clnt_no, matt_no " |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-07-11 : 11:20:31
|
[code]select right('0000' + [Client Number], 4)[/code]
KH |
 |
|
|
easy_goer
Starting Member
21 Posts |
Posted - 2006-07-11 : 12:06:05
|
| Thanks. Worked like a charm. Didn't realize it was that easy. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-12 : 02:28:53
|
| Note that if Client Number is on integer type then leading zeroes will be trucated. You should format the number at front end applicationMadhivananFailing to plan is Planning to fail |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-12 : 02:37:06
|
| another way...declare @clientNumber varchar(4)set @clientnumber='2'select replicate('0',4-(len(@clientNumber))) + @clientNumber--------------------keeping it simple... |
|
|
|
|
|
|
|
|
Query Script