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 |
Rock_query
Yak Posting Veteran
55 Posts |
Posted - 2014-01-16 : 11:34:35
|
I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.I am not using CAST in the design grid. Is this even possible?I am modifying the underlying SQL code. Here is the line that is giving me trouble:CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequence” What am I doing wrong? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-01-16 : 12:17:14
|
quote: Originally posted by Rock_query I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.I am not using CAST in the design grid. Is this even possible?I am modifying the underlying SQL code. Here is the line that is giving me trouble:CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequence” What am I doing wrong?
What is the trouble it is giving you?If you want to pad with zeros, you should do something like this:RIGHT('000' + CAST(GL02GLF.GLF_SEQ_NUM as VARCHAR(3)) However, if you are using it in Excel, that many not be sufficient, because Excel will interpret as a number and very helpfully strip out the padding zeros. If you want to avoid it, format the excel cell as text. |
|
|
|
|
|
|
|