How can you retrieve the numeric values from varchar2 type column?

Hi Friends, One table is there. It is having varchar2 type column and this column having both numeric and alphanumeric values but I want to retrieve only numeric related values from that table. If it is possible then say the answer plz... I will wait for ur tremendous answer...

Questions by Balapradeep Reddy   answers by Balapradeep Reddy

Showing Answers 1 - 3 of 3 Answers

chakri292

  • Feb 11th, 2012
 

Hi,
You can use regexp.

for e.g.,

Code
  1. SELECT to_number(regexp_replace(hello123,[[:alpha:]],)) FROM dual;


Output:

--------------

123

  Was this answer useful?  Yes

Balapradeep

  • Feb 14th, 2012
 

Code
  1. SELECT * FROM table_name WHERE regexp_like(column_name,^[0-9]+$);

samarendra

  • Apr 3rd, 2012
 

Code
  1. SELECT column1 FROM TABLE WHERE column1 NOT LIKE %[^0-9]%

  Was this answer useful?  Yes

rohitosu

  • Apr 3rd, 2012
 

Query is missing quotes. The corrected query is..

Code
  1. SELECT * FROM table_name WHERE regexp_like(column_name,^[0-9]+$);

  Was this answer useful?  Yes

Sushma S

  • Apr 9th, 2012
 

Code
  1. SELECT * FROM table_name WHERE regexp_like(column_name,^[0-9]+$);

  Was this answer useful?  Yes

Sudipto

  • Apr 17th, 2012
 

Code
  1. SELECT * FROM table_name WHERE regexp_like (column_name, ^[0 - 9] + $);

  Was this answer useful?  Yes

Nisa

  • Apr 17th, 2012
 

Using regexp_replace function

Code
  1. SELECT to_number(regexp_replace(abc123efg456kjhdf,[[:alpha:]]))

  2. FROM dual;

  Was this answer useful?  Yes

Atish

  • Sep 13th, 2015
 

Code
  1. SELECT * FROM TABLE_NAME WHERE UPPER(COLUMN_NAME)=LOWER(COLUMN_NAME);
Above query will give numeric values only. Matching is done on the basis of ASCII characters. So, in case of alphanumeric values, both upper and lower ASCII values will be different while in case of numeric values, it will match.

  Was this answer useful?  Yes

Sayantan

  • Jun 1st, 2017
 

Select length(trim(translate(string, +-.0123456789,) from table; if the value is null then its numeric

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions