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
 Incorrect column expression with CHAR in MS Query

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.
Go to Top of Page
   

- Advertisement -