CODEWITHSUNDEEP
sqlstringoracle-databaseselect
Oliver
Oliver

Reputation: 23510

Oracle SQL - Find special non numeric characters in field

How can I identify the datas that have anything else than a number in them in a where clause ?

Upvotes: 6

Views: 84085

Answers (6)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1608

you could use Translate and filter out the values which have non numeric characters and then order on the columns.

select * from (
    select case  WHEN trn > 0 then NULL  else fld1 end New_column, fld1,trn FROM 
        (
        SELECT '123ab' fld1, LENGTH(TRIM(TRANSLATE('123ab', ' +-.0123456789',' '))) trn FROM dual UNION ALL
        SELECT 'a123b', LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))) FROM dual UNION ALL
        SELECT '1256.54', LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))) FROM dual UNION ALL
        SELECT '4567899', LENGTH(TRIM(TRANSLATE('4567899', ' +-.0123456789',' '))) FROM dual UNION ALL
        SELECT '-56',LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' '))) FROM dual
        )
)order by new_column

Upvotes: 0

Sushant Butta
Sushant Butta

Reputation: 530

Try this

SELECT *
FROM table
WHERE regexp_like(column_name,'[^0-9]+');`

This will pick all those rows which have non numeric characters in the column.

For details check this page.

Upvotes: 3

aiGuru
aiGuru

Reputation: 1180

While not perfect (doesn't account for characters like '#', '@', etc.) a quick and dirty solution is: 

SELECT * FROM <YOUR_TABLE>
WHERE UPPER(<YOUR_TABLE.your_column_name>) = LOWER(<YOUR_TABLE.your_column_name>)

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You can also use the TRANSLATE function to do this, as follows:

SELECT *
  FROM A_TABLE a
  WHERE LENGTH(TRANSLATE(a.FIELD, 'x0123456789', 'x')) IS NOT NULL

The expression LENGTH(TRANSLATE(a.FIELD, 'x0123456789', 'x')) will return NULL if the field contains only numeric characters. If non-numeric characters are present it will return the number of non-numeric characters.

Share and enjoy.

Upvotes: 5

ALZ
ALZ

Reputation: 1998

OR this one 

select * from <YOUR_TABLE>
where LENGTH(REPLACE(TRANSLATE(<YOUR_TABLE.your_column_name>,
                               ’0123456789′, ‘@@@@@@@@@@’), ‘@’, NULL)) > 0;

Upvotes: 2

Ollie
Ollie

Reputation: 17538

SELECT *
  FROM <table>
 WHERE REGEXP_LIKE(<column>, '[^[:digit:]]');

Hope it helps...

Upvotes: 14

Related Questions