CODEWITHSUNDEEP
mysqlsql
MOR_SNOW
MOR_SNOW

Reputation: 831

Get a list strings not starting with, or ending on a vowel

I want to print all the cities that do not start with a vowel, and they also must not end on a vowel. I am aware that there is a lot of tutorial about how to do it when it must not start with a specific letter. But I am not able to find it, expressing several letters.

This is what I tried:

select distinct city
from station
where city not like '%[aeiou]%';

So if I say 

where city not like '%a'

I will get the cities that do not end with the letter a.

Doing:

where city not like 'a%'

I will get the cities that do not start with the letter a

So my attempt with: where city not like '%[aeiou]%'; is to combine the two. Id does not throw an error, it does give me a list, but just not the correct result. Actually the list is so big, that I don't know what the expression I wrote above gives me. I tried several other attempts, but this is probably the most qualified one.

How can I get a list of all cities that do not start with, or end with a vowel?

Upvotes: 1

Views: 2064

Answers (4)

rama krishna
rama krishna

Reputation: 1

select distinct(city) from station WHERE (CITY not LIKE 'A%' and CITY not LIKE 'E%' and CITY not LIKE 'I%' and CITY not LIKE 'O%' and CITY not LIKE 'U%') or (CITY not LIKE '%A' and CITY not LIKE '%E' and CITY not LIKE '%I' and CITY not LIKE '%O' and CITY not LIKE '%U');

Upvotes: 0

fancyPants
fancyPants

Reputation: 51888

You can simply use the SUBSTRING() function:

WHERE LOWER(SUBSTRING(city, 1, 1)) NOT IN ('a', 'e', 'i', 'o', 'u')
AND LOWER(SUBSTRING(city, -1, 1)) NOT IN ('a', 'e', 'i', 'o', 'u')

I also use the LOWER() function here, so that you don't have to repeat the same for upper case letters.

Upvotes: 7

Todd Peek
Todd Peek

Reputation: 41

I think this is what you're trying to do:

    declare @Station table (city varchar(100))
    insert into @station select 'Houston'
    insert into @station select 'Astoria'
    insert into @station select 'enigma'


    select distinct 
         city
    from
         @Station
    where
         city not like '[aeiou]%[aeiou]'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269963

In MySQL, you would use regular expression. This pattern:

where city not like '%[aeiou]%';

will return all cities, because no city -- to my knowledge -- has a name with the sequence of characters '[aeiou]' in it. The above would do something more meaningful in SQL Server or Sybase, where those patterns are supported by like.

The regular expression looks like:

where city regexp '^[^aeiouAEIOU].*[^aeiouAEIOU]$'

Upvotes: 3

Related Questions