Home » SQL & PL/SQL » SQL & PL/SQL » Need Only Numbers
Need Only Numbers [message #320445] Thu, 15 May 2008 02:41 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,
suppose if there's a varchar2 column in which we have saved numbers like '1234' , '23456' ,'213456CR','4535FDR'
now from above example i need only those values which are purely numbers and i want to replace all other values with '0'
how to do it
e.g i need only 1234,1254 and so on
Please help me in this regard.

thanx in advance
Re: Need Only Numbers [message #320450 is a reply to message #320445] Thu, 15 May 2008 02:53 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
This is very commonly asked. have a look at the translate function.
Re: Need Only Numbers [message #320456 is a reply to message #320445] Thu, 15 May 2008 03:12 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
ok i understand

let me tell you what i want
i have a table ABC with only one column named as col1 of type varchar2

col1
123
a12
345
ab5
677

i want to update the whole values in col1 which have any character
e.g i want to update values a12,ab5 with 0 without touching the values 123,345,and 677.
will u please give me the example to do it.
thanx a lot for your cooperations

Re: Need Only Numbers [message #320458 is a reply to message #320456] Thu, 15 May 2008 03:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Have you looked up the syntax and examples in the link that I provided? Make an attempt to solve your problem, post what you did and we will help you to work through it if you do not get it right. But put some effort in and try it for yourself.
Re: Need Only Numbers [message #320459 is a reply to message #320456] Thu, 15 May 2008 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select val, translate(val,'a1234567890','a') t from t;
VAL        T
---------- ----------
123
a12        a
345
ab5        ab
677

5 rows selected.

I let you create the update statement.
And I advice you to change the datatype of your column.

Regards
Michel
Re: Need Only Numbers [message #320460 is a reply to message #320456] Thu, 15 May 2008 03:21 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is this what you want?
UPDATE this_table SET
  col1 = 0
  WHERE col1 <contains anything but numbers>;
If so, <part> might be easily resolved by use of regular expressions.
Previous Topic: Need help with Sql query
Next Topic: Type object declaration for cur% rowtype
Goto Forum:
  


Current Time: Wed Dec 07 08:55:24 CST 2016

Total time taken to generate the page: 0.10924 seconds