1

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]')
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Eduardo Ferrreira
  • 11
  • 1
  • 2

2 Answers2

2
  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.

edi
  • 917
  • 7
  • 17
  • 1
    no it is correct - you can try it on [dbfiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=4307dfd8f1de03a50306a0d92f5a7db0) - of course you'd need to select a column and not from dual, but otherwise its fine. – edi Sep 05 '19 at 07:45
  • OK, you're right, you also considered the cases without space. – Barbaros Özhan Sep 05 '19 at 08:15
1

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Also a nice solution to use POSIX. I'd recommend for that solution to change the where condition to `where regexp_like(str,'[[:alnum:][:upper:]]')`. Strangely you'd still need a `[^[:space:]]` in order to avoid the spaces. – edi Sep 05 '19 at 08:36
  • thank you @edi . I already tried that but gives wrong result ( aside for space consideration also ). – Barbaros Özhan Sep 05 '19 at 08:45
  • [Here](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=dc9b08fd090f29bdfb15661a1fe9f593) I've build on your solution which works without spaces and looks like `where regexp_like(str,'^[[:alnum:][:upper:]]+$')` – edi Sep 05 '19 at 08:52
  • 1
    Unfortunately @edi , it still brings `EdUARDOFERRREIRA`, which contains lower-case letter , at least. – Barbaros Özhan Sep 05 '19 at 08:57
  • 2
    Yes, u're right :) - post to quickly... but ... this should do the trick `where regexp_like(str,'^[[:digit:][:upper:]]+$')` .... and the [fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=9c11b713158e592f0cd76e8d04d1b88f). – edi Sep 05 '19 at 12:41