CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-like
Eduardo Ferrreira
Eduardo Ferrreira

Reputation: 11

Regexp_Like to Validate Uppercase Characters [A-Z] and Numbers [0-9] Only

I would like a query using regexp_like within Oracle's SQL which only validates uppercase characters [A-Z] and numbers [0-9] 

SELECT * 
  FROM dual 
 WHERE REGEXP_LIKE('AAAA1111', '[A-Z, 0-9]')

Upvotes: 1

Views: 9799

Answers (2)

edi
edi

Reputation: 937

  1. List item

The select Statement probalby should look like

SELECT 'Yes' as MATCHING 
  FROM dual 
 WHERE REGEXP_LIKE ('AAAA1111', '^[A-Z0-9]+$')

Which means that starting from the very first ^ to the last $ letter every character should be upper case or a number. Important: no comma or space between Z and 0. The + stands for at least one or more characters.

Edit: Based on the answer of Barbaros another way of selecting would be possible

SELECT 'Yes' as MATCHING
  FROM DUAL 
 WHERE regexp_like('AAAA1111','^[[:digit:][:upper:]]+$')

Edit: added a DBFiddle

A quick help may be found here and for oracle regular expressions here.

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use :

select str as "Result String" 
  from tab
 where not regexp_like(str,'[[:lower:] ]') 
   and regexp_like(str,'[[:alnum:]]')

where not regexp_like with POSIX [^[:lower:]] pattern stands for eliminating the strings containing lowercase,

and regexp_like with POSIX [[:alnum:]] pattern stands for accepting the strings without symbols

( containing only letters and numbers even doesn't contain a space because of the trailing space at the end part of [[:lower:] ] )

Demo

Upvotes: 1

Related Questions