0

I need to set a condition in the where clause to return the values that contains letters. Here is an example of my data: one is pure numeric 123456789 and one is mixed AB1234567

I only want to return the mixed letter/numeric values.

Thanks

Edit: Both methods in the answers below work fine! You guys are awesome!

I found an easier way by using where col>'A' Tested the result against the answered scripts and they returned the same.

JohnD
  • 353
  • 4
  • 13
  • 26
  • possible duplicate of [Check if a varchar is a number (TSQL)](http://stackoverflow.com/questions/4603292/check-if-a-varchar-is-a-number-tsql) – squiguy May 27 '14 at 17:38
  • @squiguy The question that you have mentioned pertains to TSQL and not Oracle SQL. – Joseph B May 27 '14 at 17:41
  • @JosephB I didn't know if it was exactly similar, but the idea is the same. – squiguy May 27 '14 at 17:42
  • @squiguy Agreed. But, the accepted answer in the other question will not work for Oracle. So, it is not a duplicate. – Joseph B May 27 '14 at 17:44

2 Answers2

2

SELECT * FROM table WHERE REGEXP_LIKE(column, '[A-Za-z0-9]') and REGEXP_LIKE(column,'[^0-9]')

The query selects alpha numeric records and filter the numeric one this is done using negate ^

Demo here

vhadalgi
  • 7,027
  • 6
  • 38
  • 67
  • If I add another condition 'and column='123456789' behind your query, it will still return the numeric value. I need the query to completely omit the pure numeric records. – JohnD May 27 '14 at 17:49
  • Column containing `MX01386` and `7493559` returned both records. – xQbert May 27 '14 at 17:51
  • add the ' around the 2nd set of []'s and this works too! – xQbert May 27 '14 at 18:15
2
where LENGTH(TRIM(TRANSLATE(YourField, ' +-.0123456789', ' '))) is not null

sourced from: http://www.techonthenet.com/oracle/questions/isnumeric.php

Tested with data containing MX01386 7493559

is not null returned MX record is null returned numeric record.

What this does in essence is replace each value with a space then trims out that space if the resulting value is null, then it must be numeric. if it is not null, then it contains characters outside this list ' +-.0123456789'

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Thanks it works. Actually both of your methods works! But I will go with your method because I need to implement the script into an old legacy system that might not take REGEXP_LIKE. – JohnD May 27 '14 at 18:57