?
Solved

Displaying Currency without the Decimal in SQL

Posted on 2012-03-13
5
Medium Priority
?
420 Views
Last Modified: 2012-03-13
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
0
Comment
Question by:Rmumpower00
  • 2
  • 2
5 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37717228
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
 

Author Comment

by:Rmumpower00
ID: 37717262
Kent,
Could you possibly put what you're talking about into the string that I listed?
0
 
LVL 2

Accepted Solution

by:
musickmann earned 2000 total points
ID: 37717278
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
 

Author Comment

by:Rmumpower00
ID: 37717288
Musickmann That worked!!!!!!!!!!

Thank you!
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37717299
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question