CODEWITHSUNDEEP
sqloracle-database
JohnD
JohnD

Reputation: 353

Condition in where clause to return values that starts with letters?

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.

Upvotes: 0

Views: 274

Answers (2)

vhadalgi
vhadalgi

Reputation: 7189

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

Upvotes: 2

xQbert
xQbert

Reputation: 35333

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'

Upvotes: 2

Related Questions