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 |
|
madscientist
Starting Member
30 Posts |
 Posted - 2007-12-06 : 13:06:52
|
| Hi there,I have a query that places leading zeros in front of values that aren't the required length of the column which is 10....EMP_LIA_LIM = REPLICATE('0', 10 - LEN(CAST (WC_ACCIDENT_LIMIT AS VARCHAR))) + CAST(WC_ACCIDENT_LIMIT AS VARCHAR)...The column WC_ACCIDENT_LIMIT is a float.The column EMP_LIA_LIM is char (10).When WC_ACCIDENT_LIMIT is something like 100000 it works and EMP_LIA_LIM becomes 0000100000. BUT when WC_ACCIDENT_LIMIT is something like 1000000, EMP_LIA_LIM becomes 00001e+006. I know this is still correct but I don't want it to show an exponent. Do I have to turn something off in MS SQL Server?Thank you for the help. You guys are great on here!!! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-12-06 : 13:15:25
|
| easier isEMP_LIA_LIM = right(REPLICATE('0', 10) + CAST(WC_ACCIDENT_LIMIT AS VARCHAR), 10)doesn't solve your problem though.how aboutEMP_LIA_LIM = right(REPLICATE('0', 10) + Convert(varchar(10),convert(decimal(18,6),WC_ACCIDENT_LIMIT)), 10)You can trim trailing 0's if you wish==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
madscientist
Starting Member
30 Posts |
Posted - 2007-12-06 : 14:16:38
|
| Thanks nrBut the last one didn't seem to do the trick. On execution it gives an error message "Arithmetic overflow error converting numeric to data type varchar."What does the "(decimal(18,6)" do?I can't trim trailing zeros though.Anyone else with other suggestions? |
|
|
|
BarryC
Starting Member
6 Posts |
Posted - 2007-12-06 : 14:23:43
|
| How about defining EMP_LIA_LIM as a varchar? |
|
|
|
madscientist
Starting Member
30 Posts |
Posted - 2007-12-06 : 16:02:30
|
| Well I found an ugly way to get around it.Since WC_ACCIDENT_LIMIT is a float originally, I then cast it to an integer and then the query worked.Thank you still for the ideas. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-12-07 : 10:27:41
|
| select right('0000000000' + convert(varchar(10), @WC_ACCIDENT_LIMIT), 10)e4 d5 xd5 Nf6 |
|
|
|
|
|
|
|
|
Padding Zeros