Solved

SQL format of number

Posted on 2011-03-09
5
962 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

738 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