[Resolved] Convert Int to Char

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)

[Resolved] Convert Int to Char

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-14 : 16:52:01

In my query I am ulimately converting my integer (bp.gross_qty) to a character. This works fine except for when the integer is a negative number it is not being reflected in the character variable. Is there a way to do this using my query line? Thank you.


case when convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', '')) IS NULL THEN ''
  else convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', ''))
end as qty_gross,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-14 : 17:22:52

I think the issue is that you are putting a DECIMAL with a percision of 10, when the value goes negative, it'll be up to 11 characters long (after removing teh decimal point) and you are trying to put that into a CHAR(10).

So you can truncate the value or expand to CHAR(11)

Also, with the implicit converstions going on, you'll probably want to CAST the entire operation to the proper datatype (Decimal(10,2)?).

Also, you can replace the CASE expression with a COALESCE function:

COALESCE(convert(char(10), replace(convert(decimal(10,2), bp.gross_quantity / 42.0), '.', '')), '') as qty_gross,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-14 : 17:28:54

The STR() function is better for formatting numbers as strings:

SELECT COALESCE(STR(bp.qross_quantity/42.0*100, 10, 0),'')

And it's faster to multiply by 100 if you just want to remove the decimal point.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-15 : 13:12:59

Now got following:

bp.net_quantity / 42.0 as qty_net,

Result: -485.880952

and

case when convert(char(11), replace(convert(decimal(10,2), bp.net_quantity / 42.0), '.', '')) IS NULL THEN ''
         else convert(char(11), replace(convert(decimal(10,2), bp.net_quantity / 42.0), '.', ''))
       end as qty_net,

Result: -48588

The result now show negative sign if result i negative and implicit 2 decimals)

Now, I have one more step to climb:

I need to get the sum of all records like

sum(bp.net_quantity / 42.0) as qty_net,

and I am not sure how to do that in my other query (case...) string.

Thank you.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-15 : 14:43:47

Think I figured it out:

case when convert(char(11), replace(convert(decimal(10,2), sum(bp.net_quantity / 42.0)), '.', '')) IS NULL THEN ''
         else convert(char(11), replace(convert(decimal(10,2), sum(bp.net_quantity / 42.0)), '.', ''))
        end as qty_net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources