Solved

SQL format of number

Posted on 2011-03-09
5
967 Views
Last Modified: 2012-05-11
I have a field Fiscal Year that returns the year as 2,009.  How do I in my SQL remove the , from the 2,009 value?
0
Comment
Question by:garyjgs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35084939
try

select substr(year, 1,1) || substr(year,3,3)
from your_tbale
0
 
LVL 18

Accepted Solution

by:
Dave Ford earned 500 total points
ID: 35085087

It's probably easiest to just convert it to a varchar.

e.g.

select varchar(theValue) as theYear
  from deleteme

THEYEAR
2009

Open in new window

0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 35085470

Hi Gary,

If the value '2,000' is stored in the table, momi's suggestion works fine.

I prefer the replace() function though.


  SELECT replace (TheYear, ',', '') FROM mytable;


Good Luck,
Kent
0
 

Author Closing Comment

by:garyjgs
ID: 35086079
Worked as suggested.  Thanks.
0
 

Author Comment

by:garyjgs
ID: 35086084
Worked as suggested. Thanks.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

707 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