CODEWITHSUNDEEP
sqlregexoracle-database
Sid
Sid

Reputation: 582

Query to get records with special characters only

I am trying to write a query which will tell me if certain record is having only the special characters. e.g- "%^&%&^%&" will error however "%HH678*(*))" is fine (as it's having alphanumeric values as well. I have written following query however, it's working fine only for English alphabets and numbers, if column is having some other characters like mandarin then also it's not giving expected value.Any help is highly appreciated.

SELECT * FROM test WHERE REGEXP_LIKE(sampletext, '[^]^A-Z^a-z^0-9^[^.^{^}^ ]' );

Upvotes: 0

Views: 2521

Answers (2)

Sentinel
Sentinel

Reputation: 6449

To detect strings containing only characters other than unaccented alphabetic, numeric, or white space characters try this:

regexp_like(text,'^[^a-zA-Z0-9[:space:]]+$')

If you don't think punctuation characters are special than add [:punct:] to the class of characters to ignore.

If you are looking for a specific set of characters you can use a character class of just those characters of interest, for example some common accented characters (note the lack of a leading ^ in the character class []:

regexp_like(text,'^[àèìòùÀÈÌÒÙáéíóúýÁÉÍÓÚÝâêîôûÂÊÎÔÛãñõÃÑÕäëïöüÿÄËÏÖÜŸçÇßØøÅåÆæœ]+$')

Upvotes: 0

Avinash Raj
Avinash Raj

Reputation: 174706

You may try this,

regexp_like(text, '^[^A-Za-z0-9]+$')

This would match the text only if the input text contains special chars ie, only chars which are not of letters or digits.

Upvotes: 1

Related Questions