Displaying Currency without the Decimal in SQL

I have a Column in a Table that has Currency input into it.  I need to display this Currency as part of a String without the Decimal.

How do I go about doing this?

This is what I have thus far
SELECT '0000012345678901'+LTRIM(CAST(ITEMNO AS VARCHAR(20)))+''+CAST(checknet AS VARCHAR(20))+''+CONVERT(varchar(10),checkdt, 112)+CASE(ckstatus)
WHEN 'O' THEN '' WHEN 'V' THEN 'C' END +''  as Val
FROM mytable

Where you see +CAST(checknet AS VARCHAR(20)) is where I need to extract the information from that Column and display it without the Decimal.

Thank you
Rmumpower00Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
musickmannConnect With a Mentor Data AnalystCommented:
Would using the REPLACE (string_expression, search_string,  replacement_string) function work for you? Might look something like this:


 REPLACE (CAST(checknet AS VARCHAR(20)), '.',  '')
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Rmum,

Probably the easiest way is just to cast it to an integer.

  SELECT cast (column * 100 as INTEGER) FROM ...

That will preserve the two decimal places and display the entire value.



Good Luck,
Kent
0
 
Rmumpower00Author Commented:
Kent,
Could you possibly put what you're talking about into the string that I listed?
0
 
Rmumpower00Author Commented:
Musickmann That worked!!!!!!!!!!

Thank you!
0
 
Kent OlsenData Warehouse Architect / DBACommented:
SELECT
  '0000012345678901'+LTRIM(CAST(ITEMNO AS VARCHAR(20)))+''+
  cast (cast (column * 100 as INTEGER) as varchar(20) +
  ''+CONVERT(varchar(10),checkdt, 112)+
  CASE(ckstatus) WHEN 'O' THEN '' WHEN 'V' THEN 'C' END +''  as Val
FROM mytable

Though the double casting might make musickmann's suggestion to use REPLACE easier.


Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.