CODEWITHSUNDEEP
sqlsql-servert-sql
SyntaxError
SyntaxError

Reputation: 3859

How to CONVERT/CAST a column in sql query

can someone help with this. I'm having a problem with this sql query. I need to convert price1_split to int. But it seems it also gets the concatenation i made beside it.

SELECT product_number,product_name,description,
price1+ ' ' + CONVERT(INT, price1_split) + '% |' + 
price2+ ' ' + CONVERT(INT, price2_split) + '% |' + 
price3+ ' ' + CONVERT(INT, price3_split) + '%'  as price_split
from tbl_products

error msg says: Conversion failed when converting the varchar value '% |' to data type int.

Upvotes: 1

Views: 4578

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270757

Since you ultimately need a string value separated by % |, you should not be casting these to integers at all. You would need to cast them to INT if you were adding the values together, but you are not adding them, you're concatenating them onto strings. Assuming they are already string (CHAR, VARCHAR) values, just concatenate them onto the other character elements.

SELECT product_number,product_name,description,
  price1+ ' ' + price1_split + '% |' + 
  price2+ ' ' + price2_split + '% |' + 
  price3+ ' ' + price3_split + '%'  as price_split
from tbl_products

If however, they are float values you are trying to truncate to integers, you can CONVERT() them to INT and then CONVERT() them back to strings to concatenate. In this case, it would probably be better to use FLOOR() for the truncation as in CONVERT(VARCHAR(n), FLOOR(price1_split))

-- Using CONVERT(INT) for truncation
SELECT product_number,product_name,description,
  price1+ ' ' + CONVERT(VARCHAR(32), CONVERT(INT, price1_split)) + '% |' + 
  price2+ ' ' + CONVERT(VARCHAR(32), CONVERT(INT, price2_split)) + '% |' + 
  price3+ ' ' + CONVERT(VARCHAR(32), CONVERT(INT, price3_split)) + '%'  as price_split
from tbl_products

Upvotes: 4

Related Questions