0

I am trying to find all the name that starts with either A,B or C in Oracle DB and I wrote this syntax:

SELECT NUME FROM JUCATORI
WHERE NUME LIKE '[ABC]%';

but it doesn't not give me any name (neither error), even though I am sure I have in my DB names that starts with either A,B or C.

Filburt
  • 17,626
  • 12
  • 64
  • 115
Bianca
  • 3
  • 2

3 Answers3

2

LIKE doesn't work with character sets. (I think T-SQL extended LIKE to deal with such expressions, but Oracle's SQL doesn't.) You don't get an error, because LIKE '[ABC]%' looks for strings starting with an opening bracket, followed by an A, then a B, then a C, then a closing bracket

So either use OR:

SELECT nume FROM jucatori WHERE nume LIKE 'A%' OR nume LIKE 'B%' OR nume LIKE 'C%';

Or a regular expression:

SELECT nume FROM jucatori WHERE REGEXP_LIKE(nume, '^[ABC]');
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • 2
    Except it DOES work with wildcards - '%' is wildcard. It does not work with character sets, which '[ABC]' is. – piezol Jan 31 '20 at 12:42
  • @piezol: Yep, that's correct of course, after all this is what `LIKE` is all about :-) I'll update my answer accordingly. Thanks. – Thorsten Kettner Jan 31 '20 at 13:10
1

One option is

where substr(nume, 1, 1) in ('A', 'B', 'C')
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
1

You're confusing regular expressions with 'like' conditions.

Like checks for exactly the string you pass to it, with the only three exceptions being:

1. %    - any number of any characters
2. _    - any single character

3. ESCAPE clause that lets you define escape character 
so you can use %  as 'I'm looking for percent sign'

[ABC] meaning 'either A, B or C' is a regular expression syntax. You may want to use regexp_like instead, or for such simple query just 'OR' several likes:

with examples as (
   select 'Adam' ex from dual union all
   select 'Dorothy' ex from dual union all
   select '[ABC]something' ex from dual union all
   select '[ABC]' from dual
)
select e.ex,
       case when e.ex like '[ABC]%' then 'MATCHES' else null end as matches_like,
       case when regexp_like(e.ex, '^[ABC].?') then 'MATCHES' else null end as matches_regexp_like,
       case when e.ex like 'A%' or e.ex like 'B%' or e.ex like 'C%' then 'MATCHES' else null end as matches_complex_like
  from examples e
piezol
  • 915
  • 6
  • 24