0

I need to print names that do not start and end with vowel. I tried it like this: 

SELECT DISTINCT name FROM people WHERE
lower(name) NOT LIKE IN ('a','e','i','o','u')%('a','e','i','o','u');

I got error.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
nishitha.m
  • 5
  • 1
  • 3
  • Have a look at http://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql – CollinD Mar 19 '17 at 06:55

2 Answers2

1

You can use regexp_like with match parameter i for case insensitive matching:

select distinct name from people
where not regexp_like(name, '^[aeiou]($|.*[aeiou]$)', 'i');

Pattern details:

  • ^[aeiou] - starts with a vowel
  • ($|.*[aeiou]$) - either there is only one character (matched in the first step) or ends with a vowel.
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
1

You may want to try avoid using REGEXP from performance reasons in case of large data sets.

In such case is TRANSLATE your friend.

1) translate all vowels to one representative

2) perform normal LIKE predicate with the selected vowel

select txt from tab1
where translate(lower(txt),'aeiou','aaaaa') not like 'a%a';

REGEXPs are mighty, but should not be used on non-trivial data sets in case that they could be avoided. (My 8M rows test data gives 7 seconds elapsed using TRANSLATE vs. 2+ minutes with REGEXP).

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53