Help in converting data type varchar to numeric

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

Transact-SQL (2005)

Help in converting data type varchar to numeric

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-03-15 : 14:34:08

Hi all,

I have a table called TABLE1
It has the following 2 fields with types:

SalesHistoryPrice(decimal type with precision 18 & scale 2)
StoreCount(int)

The records are like; for example
SalesHistoryPrice,StoreCount
10.5,12

In my select statement I want to display them like following:
10.5 (12)

I am using the following query:

select SalesHistoryPrice + ' (' + StoreCount + ')'
from TABLE1

I am getting an error:
Error converting data type varchar to numeric.

How can I fix this.

Any quick help would be highly appreciated.

Thanks....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 14:37:40

It is much easier to format your results at the client, or on a report, or your web page, or whatever is consuming this data. it is easier, faster, cleaner, and so on. How are you outputting/using the results from SQL?

If you really want to do in T-SQL, you must convert everything to VARCHAR's and then you can use the + operator to concatenate them all together into 1 big VARCHAR that you can return. Which means that you are no longer returning an int and a decimal, of course, but rather just a string of characters.

- Jeff
http://weblogs.sqlteam.com/JeffS

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-03-15 : 14:40:31

Yes I want to do it in T-SQL, & want to convert everything to varchar & then concatenate it. How can I do that?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 14:51:36

You use the CONVERT function in T-SQL to convert the numeric values to VARCHAR's.

i.e., CONVERT(varchar(length), someIntegerValue)

It's all in the SQL Server help file, books on line.

Again, I've gotta warn you that you are doing things the wrong way, you should not format your data in SQL, but that's what you want to do, go for it.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources