Geek asks and answers

Oracle SQL - Search for Special Non-Digit Characters in a Field

How can I define data that has something else than the number in it in the where clause?

+5
source share
5 answers
SELECT *
  FROM <table>
 WHERE REGEXP_LIKE(<column>, '[^[:digit:]]');

Hope this helps ...

+11
source

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.

+5
source 

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

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

, .

. .

+2

( "#", "@" ..), :

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

More articles:


All Articles