converting numeric string data to text

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 2000 Forums

SQL Server Development (2000)

converting numeric string data to text

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-03-27 : 23:10:51

casey writes "We have part numbers that often begin with a zero (ex: 0793123456). They are stored as varchar in the database (SQL 7.0). I'm trying to write a query that will use commas as delimiters between fields writing the results to a file with a .csv suffix so that clicking on the file will launch excel. All seems to be working OK except the part number is being treated as a number by excel and the leading zero is dropped. I've tried to use cast and convert to change :the part number to a text field

select
location +',' +
cast(left(convert(char,a.part_no),10) as text) + ',' +
description
from inventory

but this results in this error:

Invalid operator for data type. Operator equals add, type equals text.

Any help is greatly appreciated"

JWY2010
Starting Member

3 Posts

Posted - 2010-07-19 : 15:32:27

So here is something I tried and works well.

Thinking out of the box, I came up with formatting a numeric value as a formula, which embeds the zero in the formula. It will look terrible as a CSV, but when Excel opens the file, it will have the leading zero!

James W. Young

JWY2010
Starting Member

3 Posts

Posted - 2010-07-19 : 15:33:23

select
'=right("''0' + cast(a.Zip AS varchar(5)) + '",5)' AS 'Zip'
from mytable

James W. Young

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-19 : 15:49:59

While that may work, it's a lot of effort (and padding) to work around a shortcoming in Excel.

JWY2010
Starting Member

3 Posts

Posted - 2010-07-19 : 16:50:11

It took me less time to write than it took Microsoft to fix it, or explain it for the 10 millionth time.... It does work and that's the operative key word.

I am open for any other solution. Got one?

James W. Young

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-19 : 16:59:19

If the Excel file is fairly small and only used by humans, that's a fine solution. If it's meant to be processed by another program/software/computer process that requires Excel format then SSIS or a custom macro would be better. Excel has the External Data features available that can grab directly from SQL Server. You can also write a VB macro to copy data via ADO Recordsets. These wouldn't use CSV files at all and would avoid the conversion problem entirely.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-19 : 17:50:45

This thread got revived after 9 years?

Anyway, if anyone looks at the original post, there was an error in the SQL code:

select 
location +',' +
cast(left(convert(char,a.part_no),10) as text )+ ',' + 
description 
from inventory

It probably should have been:

select 
	location +',' +
	convert(varchar(20),left(convert(varchar(20),a.part_no),10))+ ',' + 
	description 
from
	inventory

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources