CODEWITHSUNDEEP
sqloracle-database
user10111685
user10111685

Reputation:

Returning only values without a numeric character in it SQL

I am trying to only return values from a column that do not have a numeric character within that value. example: 

   yyyy INC 5.875% DUE 3/15/2014
   xxxx INC 6% DUE 4/15/2012
   x INC 6.625% DUE 9/15/2016
   xx INC 8.2% DUE 10/17/2003
   xy CORP REGISTERED 
   xy INC

I only want to return the last two records since there are no numeric characters in it.

I was playing around with 

not like '%[^0-9.]%'

I know that how to return values that do not start with a value with a (+). I am just confused on how to do it over an entire value.

Upvotes: 0

Views: 224

Answers (3)

ben
ben

Reputation: 329

you can also use the patindex function, similar to the regex example:

where patindex('%[0-9]%', value) = 0

Upvotes: 0

MT0
MT0

Reputation: 167972

You can use TRANSLATE to strip the numeric characters and then test whether the value has changed:

Oracle Setup:

CREATE TABLE your_table ( value ) AS
SELECT 'yyyy INC 5.875% DUE 3/15/2014' FROM DUAL UNION ALL
SELECT 'xxxx INC 6% DUE 4/15/2012'     FROM DUAL UNION ALL
SELECT 'x INC 6.625% DUE 9/15/2016'    FROM DUAL UNION ALL
SELECT 'xx INC 8.2% DUE 10/17/2003'    FROM DUAL UNION ALL
SELECT 'xy CORP REGISTERED'            FROM DUAL UNION ALL
SELECT 'xy INC'                        FROM DUAL;

Query:

SELECT *
FROM   your_table
WHERE  value = TRANSLATE( value, 'A1234567890', 'A' )

Output:

| VALUE              |
| :----------------- |
| xy CORP REGISTERED |
| xy INC             |

db<>fiddle here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

In Oracle, you would use regexp_like(). To say that no digits are in the string:

where not regexp_like(col, '[0-9]')

Or, if you prefer something more positive, you can say that all the characters are not digits:

where regexp_like(col, '^[^0-9]*$')

Upvotes: 2

Related Questions