Home » SQL & PL/SQL » SQL & PL/SQL » how to exclude ',' from character field and convert it into number Field ?
how to exclude ',' from character field and convert it into number Field ? [message #306913] Mon, 17 March 2008 04:31 Go to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
hi,
I have the data in character field.
Ex.)
1,500.00
25,000.00
1,000.00

I want to convert it to number by excluding ',' like
1500.00
25000.00
1000.00
like this...
please assist...

Regards,
Faz..
Re: how to exclude ',' from character field and convert it into number Field ? [message #306914 is a reply to message #306913] Mon, 17 March 2008 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question. Why did you post this in expert forum and not in newbie one?

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Follow them including what is said about formatting.

Regards
Michel


[Updated on: Mon, 17 March 2008 04:34]

Report message to a moderator

Re: how to exclude ',' from character field and convert it into number Field ? [message #306922 is a reply to message #306914] Mon, 17 March 2008 05:06 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
ok.. sorry....
Re: how to exclude ',' from character field and convert it into number Field ? [message #306933 is a reply to message #306913] Mon, 17 March 2008 05:42 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Hi,

Think about how you will do it in notepad. Like for example you have list of strings like this in your notepad. You want to get rid off the coma. What will you do. You will do exactly the same thing in oracle. I know I am giving you a clue like in treasure hunt. But this is the best way you will learn.

Happy learning. Also from next time please read the forum guidelines before posting.

Regards

Raj
Re: how to exclude ',' from character field and convert it into number Field ? [message #306938 is a reply to message #306913] Mon, 17 March 2008 05:55 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
please assist fo the above query..

Regards,
Faz...

Re: how to exclude ',' from character field and convert it into number Field ? [message #306949 is a reply to message #306938] Mon, 17 March 2008 06:13 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
I found the solution...

select to_number(REGEXP_REPLACE('1,500.00',',')) from dual ;

the o/p will be
1500.00

Thanks & Regards,
Fazal...
Re: how to exclude ',' from character field and convert it into number Field ? [message #306950 is a reply to message #306938] Mon, 17 March 2008 06:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Like for example you have list of strings like this in your notepad

Answer to the above question. I will help you how to do it in oracle.

Regards

Raj
Re: how to exclude ',' from character field and convert it into number Field ? [message #306953 is a reply to message #306949] Mon, 17 March 2008 06:17 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
I found the solution...

select to_number(REGEXP_REPLACE('1,500.00',',')) from dual ;

the o/p will be
1500

Thanks & Regards,
Fazal...
Re: how to exclude ',' from character field and convert it into number Field ? [message #306954 is a reply to message #306950] Mon, 17 March 2008 06:18 Go to previous messageGo to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
i didnt get you...help needed...
Re: how to exclude ',' from character field and convert it into number Field ? [message #306962 is a reply to message #306953] Mon, 17 March 2008 06:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You need a regular expression only when you talk about patterns occuring in a series but this looks more or less like a straight forward replace to me. Something like this
  1    with t
  2    as
  3    (select '10,000.80' num from dual
  4     union all
  5     select '20,050.00' from dual
  6     union all
  7     select '5,000.50' from dual
  8     union all
  9     select '400.00' from dual
 10    )
 11*  select num, replace(num,',','') from t
SQL> /

NUM       REPLACE(N
--------- ---------
10,000.80 10000.80
20,050.00 20050.00
5,000.50  5000.50
400.00    400.00

Regards

Raj
Re: how to exclude ',' from character field and convert it into number Field ? [message #306965 is a reply to message #306954] Mon, 17 March 2008 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
REPLACE
TO_NUMBER
TO_CHAR

I think you'll find the functions even I don't post the links.

Regards
Michel

[Updated on: Mon, 17 March 2008 06:40]

Report message to a moderator

Re: how to exclude ',' from character field and convert it into number Field ? [message #307137 is a reply to message #306913] Mon, 17 March 2008 23:25 Go to previous messageGo to next message
dev01
Messages: 13
Registered: March 2005
Junior Member
May be this

select to_char(to_number(' 1,500.00','99999999D00'),'99999999D00') from dual
Re: how to exclude ',' from character field and convert it into number Field ? [message #307167 is a reply to message #307137] Tue, 18 March 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe it is wrong:
SQL> select to_char(to_number('1,500.00','99999999D00'),'99999999D00') from dual ;
select to_char(to_number('1,500.00','99999999D00'),'99999999D00') from dual
                         *
ERROR at line 1:
ORA-01722: invalid number

Regards
Michel
Re: how to exclude ',' from character field and convert it into number Field ? [message #307207 is a reply to message #306913] Tue, 18 March 2008 02:32 Go to previous messageGo to next message
dev01
Messages: 13
Registered: March 2005
Junior Member
SQL> show parameter nls_territory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
nls_territory                        string      AMERICA


So if we change the nls_numeric_characters parameter, it won't work

SQL> alter session set nls_numeric_characters = ",."
  2  /

Session altered.

SQL> select to_number('1,500.00','99999999D00') from dual
  2  /
select to_number('1,500.00','99999999D00') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


Change back to American style
SQL> alter session set nls_numeric_characters = ".,"
  2  /

Session altered.

SQL> select to_number('1,500.00','99999999D00') from dual
  2  /

TO_NUMBER('1,500.00','99999999D00')
-----------------------------------
                               1500
Re: how to exclude ',' from character field and convert it into number Field ? [message #307209 is a reply to message #307207] Tue, 18 March 2008 02:36 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I know the reason, just pointing your "solution" is not complete.
Maybe it is:
SQL> select to_char(to_number('1,500.00','9G999G999D00','nls_numeric_characters=''.,''')
  2                ,'99999999.00')
  3  from dual;
TO_CHAR(TO_N
------------
     1500.00

1 row selected.

Regards
Michel

[Updated on: Tue, 18 March 2008 02:37]

Report message to a moderator

Previous Topic: DB user
Next Topic: Converting rows into column
Goto Forum:
  


Current Time: Wed Dec 07 03:02:31 CST 2016

Total time taken to generate the page: 0.07994 seconds