andrewstuart Is it really necessary to have yet another variation on this question?
reckoner Hahaha! More like a regex exercise :P
VishiGulati BUT I THINK ITS TO MUCH NOW
supercoder1 select distinct city from station where NOT ((city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u') or (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%'));
What is wrong with this query?Im getting the correct answer. But it is saying it is wrong.
Gopal_Singh07 we can't use like inside any other clause
BansheeGhoul Oracle solution ... 1) select DISTINCT city from (SELECT T1.city FROM station T1 WHERE lower(SUBSTR(T1.city, -1)) not IN ('a', 'e', 'i', 'o', 'u') union all SELECT T1.city FROM station T1 WHERE lower(SUBSTR(T1.city, 0, 1)) not IN ('a', 'e', 'i', 'o', 'u'));
2) SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,Length(CITY),1)) NOT IN ('a','e','i','o','u')and LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
surabhighosal select distinct CITY from STATION where substr(UPPER(CITY),1,1)NOT in ('A','E','I','O','U') and substr(UPPER(CITY),-1,1) NOT in ('A','E','I','O','U');
whats wrong wid the above query
arnovsmit There are 2 things needs to be changed: 1) you have to use OR instead of AND 2) In the 2nd part of the query use substr(UPPER(CITY),-1,LENGTH(CITY)) NOT in ('A','E','I','O','U'); Instead of substr(UPPER(CITY),-1,1) NOT in ('A','E','I','O','U');
mounikarao309 No it has to be substr(UPPER(CITY),-1,1) NOT in ('A','E','I','O','U');
toheebbellob01 select distinct city from station where RIGHT(CITY,1) NOT IN ('a','e','i','o','u') OR LEFT(CITY,1) NOT IN ('a','e','i','o','u')
benayapaul try this one select city from station where left(city,1) not regexp 'a|e|i|o|u' or right(city,1) not regexp 'a|e|i|o|u' group by city
shweta_shegokar1 Can you please explain what left and right stands for?
thiaagoo911 Right(columnName, integer)
It takes the "integer times characters" from the right to the left, like if you do Right(City, 3) it takes the last 3 characters of the city name.
poojapathak select distinct(city) from station where left(city,1) not regexp 'a|e|i|o|u' and right(city,1) not regexp 'a|e|i|o|u' ---------- I got the correct output
rambruster In Oracle
select DISTINCT city from (SELECT T1.city FROM station T1 WHERE lower(SUBSTR(T1.city, -1)) not IN ('a', 'e', 'i', 'o', 'u') union all SELECT T1.city FROM station T1 WHERE lower(SUBSTR(T1.city, 0, 1)) not IN ('a', 'e', 'i', 'o', 'u'));
indraone01 In MSSQL and it's work well for me,
select city from station where city not in (select city from station where left(city,1) in ('a','i','u','e','o') and right(city,1) in ('a','i','u','e','o')) group by city
indranil2501 In my exprience IN is a very slow operator. So, i would recomend to use below code:
SELECT DISTINCT S.CITY FROM STATION S WHERE S.CITY NOT LIKE '%[AEIOU]' OR S.CITY NOT LIKE '[AEIOU]%'
avishek_nagi excellent this code works on MS SQL server
SELECT DISTINCT CITY FROM STATION WHERE city NOT LIKE '%[AEIOU]' OR city NOT LIKE'[AEIOU]%';
JonFulleruk This should of worked as well right?
with AND instead of or?
Select distinct city from station where city not like '%[aeiou]' AND city not like '[aeiou]%'
rishaba This is also not working
kannankarunahar1 SELECT DISTINCT CITY FROM STATION WHERE ID in (SELECT DISTINCT ID FROM STATION WHERE CITY REGEXP '^[^aeiou]' OR CITY REGEXP '[^aeiou]$'); --> MySQL
caseybcutler This worked, but can change to the following due to the ID's should be unique/the primary key anways. SELECT DISTINCT CITY FROM STATION WHERE ID in (SELECT ID FROM STATION WHERE CITY REGEXP '^[^aeiou]' OR CITY REGEXP '[^aeiou]$');
suphiya select distinct city from station where REGEXP_LIKE (city, '(^[^aeiou])|([^aeiou]$)', 'i');
barsa_tripathy_1 for ms sql one more way to do SELECT distinct CITY FROM STATION WHERE CITY NOT LIKE '%[A,E,I,O,U]' and CITY NOT LIKE '[A,E,I,O,U]%';
rahulmu4our Oky Thanks
bhumikachawla_01 It can also be done without using subquery. select distinct city from station where city not regexp '^[a,e,i,o,u]' or city not regexp '[a,e,i,o,u]$';
LinVenus regexp, what is this? I can not find resources in SQL server for it.
blurryface30 [deleted]
mimsy_ru For MS SQl Server you can use LIKE with[^] OR as alternative NOT LIKE []: SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[^AEIOU]%' OR CITY LIKE '%[^AEIOU]'ORDER BY CITY; alternative with NOT LIKE: SELECT DISTINCT CITY FROM STATION WHERE CITY NOT LIKE '[AEIOU]%' OR CITY NOT LIKE '%[AEIOU]'ORDER BY CITY;
amrit4job instead of OR in between is should be and
city not regexp '^[a,e,i,o,u]' AND city not regexp '[a,e,i,o,u]$';
chandu_sai89 SELECT DISTINCT CITY FROM STATION WHERE CITY NOT LIKE '[AEIOU]%' OR CITY NOT LIKE '%[AEIOU]'
DIS CODE IS WORKING IN MS SQL SERVER
Mandril31 but this is not working on mysql.can you help
rahulmu4our This code work for me.
SUMA_KR [deleted]prateek_ashtika1 This doesn't works fine in mySQL...Needs correction?
rpravinkumar25 Try this simple way
select distinct city from station where city not like "[aeiou]%%[aeiou]";
bhumikachawla_01 Did not work
NilEeSHGaIkWaD select distinct city from station where NOT ((city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u') AND (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%'));
i will definetly work;
viraj1604 Try using lower(city) in each case and it will work.
kumudamvasudevan SELECT DISTINCT CITY FROM STATION WHERE CITY NOT RLIKE '^[aeiouAEIOU].*.[aeiouAEIOU]$';
hope this one works for Mysql
NilEeSHGaIkWaD select distinct city from station where NOT ((city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u') AND (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%'));
u only replace OR BY AND in between two conditions
NilEeSHGaIkWaD select distinct city from station where NOT ((city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u') AND (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%'));
i will work
smaita Why isn't the query executing when I use CITY?
can_code observe that the city names begin with uppercase letters
manikantareddy_2 SELECT distinct CITY FROM STATION WHERE SUBSTR(CITY,1,1) NOT IN ('A','E','I','O','U') or SUBSTR(CITY,-1,1) NOT IN ('a','e','i','o','u') order by city;
cricketcrazyray1 the query is short in MySQL using regex, But those who use Oracle, the above one is the best.
Krishnegowdakv Why this is not working in Oracle?
tarunama I don't understand your query, because complex WHERE condition. Maybe better use REGEXP in MySQL.
hvp8466 SELECT DISTINCT CITY FROM STATION WHERE (NOT (CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'I%' OR CITY LIKE 'O%' OR CITY LIKE 'U%')) AND (NOT (CITY LIKE '%a' OR CITY LIKE '%e' OR CITY LIKE '%i' OR CITY LIKE '%o' OR CITY LIKE '%u')); //IT CAN WORK PERFECTLY
vanshreebapat use AND instead of OR
vigneshwaran_ch1 try using and instead of or .
neerajsewanisrt8 use REGEXP_LIKE instead
hackos95 @supercoder1 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%');
Sdiva007 i didnt get the answer for trying that query
cruiser_mishra INSTEAD OF OR USE AND SELECT DISTINCT CITY FROM STATION WHERE NOT ((CITY LIKE "A%" OR CITY LIKE "E%" OR CITY LIKE "I%" OR CITY LIKE "O%" OR CITY LIKE "U%") AND (CITY LIKE "%a" OR CITY LIKE "%e" OR CITY LIKE "%i" OR CITY LIKE "%o" OR CITY LIKE "%u")) ORDER BY CITY;
avid_vats ur answer is case specific ... do add uppercase too
shubhamkumbhar11 select distinct city from station where NOT ((city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u') AND (city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%'));
Put and
raajdeep SELECT DISTINCT CITY FROM STATION WHERE NOT ((CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'I%' OR CITY LIKE 'O%' OR CITY LIKE 'U%') AND (CITY LIKE '%a' OR CITY LIKE '%e' OR CITY LIKE '%i' OR CITY LIKE '%o' OR CITY LIKE '%u'));
piyusman MYSQL:
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[AEIOU]' OR CITY NOT REGEXP '[AEIOU]$';
ALSO
SELECT DISTINCT CITY FROM STATION WHERE RIGHT (CITY, 1) NOT IN ('A','E', 'I', 'O', 'U') OR LEFT (CITY,1) IN ('A','E', 'I', 'O', 'U');
and also you can do with substring one as already there in comments.
IT IS WAY TOO MUCHHHHHHHHH
chadwalt I would prefer using substring. But I think using regexp is more efficient
madhu131313 why was this down voted ?
vikashagrawal96 yap yap
Vyom31 [deleted]
harryapotter SELECT DISTINCT CITY FROM STATION WHERE RIGHT (CITY, 1) NOT IN ('A','E', 'I', 'O', 'U') OR LEFT (CITY,1) NOT IN ('A','E', 'I', 'O', 'U'); Now it is true :)) :))
paulxchoi yeah... i feel like im working more on my regex than my sql at this point.
SELECT DISTINCT city FROM station WHERE city RLIKE "^[^aieou]|.*[^aeiou]$"
urvashi_bansal please explain this regex expression why u have used ^ before [^aeiou]
geek_diary_su Its a negation sign,
sonukumar5dec select distinct city from station where regexp_like(city,'^[^AEIOU].*[^aeiou]$');
is it wrong answer???please help
jayarajanv25 poda fucker
chadwalt SELECT DISTINCT CITY FROM STATION WHERE LEFT(CITY, 1) NOT IN ('A', 'E', 'I', 'O', 'U') OR RIGHT(CITY, 1) NOT IN ('a', 'e', 'i', 'o', 'u') THIS WORKED FOR ME.
chamax yes nice it works for me thanks
pradeepradha it's working
souvikmodak select distinct city from station where city not in (select city from station where (city like 'A%' or city like 'E%' or city like 'I%' or city like 'O%' or city like 'U%') AND (city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u'));
piyusman [deleted]darrenbkl MySQL
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou]|[^aeiou]$'
kaustubhn36 TRY THIS QUERY - select DISTINCT city from station where city REGEXP '^[^aeiou].*';
lfv89 Completly agree, this is ridiculous.
sayanarijit select distinct city from station where city regexp '[^aeiou]$' or city regexp '^[^aeiou]'
alekwmiller I for one am happy to work through these step-by-step. I like understanding what every, tiny variation does.
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT DISTINCT City FROM Station WHERE REGEXP_LIKE(City, '^[^AEIOU]|[^aeiou]$');
Feel free to ask if you have any questions :)
rshaghoulian MySQL solution
From my HackerRank solutions.
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou]|[^aeiou]$';
Let me know if you have any questions.
kanishk251296 HOW U USE EITHER OR IN THIS CODE
rshaghoulian The symbol | is basically the "OR" you are referring to.
kanishk251296 but what is the meaning of ^ b/w aeiou brackets EX -[^aeiou]
rshaghoulian The ^ inside the brackets says that we can use any letter EXCEPT aeiou. If there was no ^ then it would be the 5 letters aeiou, which is not what we want.
anuj_pancholi_1 From what i can gather the regexp you've used translates to "starts with a vowel OR ends with a vowel". Why, then, when i implement a similar logic in Oracle, does it say that it's wrong?
rshaghoulian That is not the same as the problem statement of: "do not start with vowels or do not end with vowels"
For example, the word: railway. This does not start with a vowel, and does not end with a vowel. But your translation would provide a different result.
TomYumNoodle [deleted]TomYumNoodle Hello,
Can I use OR here if I use Regex?
For example:
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou] OR CITY REGEXP [^aeiou]$';
I know this code doesn't work, but just want to get more clarity :) Thanks!
neuron001 select distinct city from station where not(city like '%a' or city like '%e' or city like '%i' or city like '%o' or city like '%u' or city like 'a%' or city like 'e%' or city like 'i%' or city like 'o%' or city like 'u%') order by city;
why this fails, any idea?
hrbukharii Solution for MySQL
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[aeiou].*[aeiou]$';
pavima1965 THIS DOESN'T GIVE THE CORRECT ANSWER
unicore it should be '^[AEIOU].*[aeiou]$'
RouyiDing It is my answer: select distinct CITY from STATION where CITY like '[^aeiou]%[^aeiou]'; But the system said wrong, anyone can explain why?
heilman_ivAsked to answer I'm not sure about your regex so you might have capitalization problems or it can't EITHER start OR end with vowels. So Aberdeen would be valid.
math_frechette It is wrong because the question say that the city name cannot begin with OR end with a vowel. In your answer the city name won't begin AND end with a vowel. You could have wrote SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[^AEIOU]%' OR CITY LIKE '%[^AEIOU]';
sargam142 thanks for explaining
xayuzaii my solution in oracle:
SELECT DISTINCT(CITY) FROM STATION WHERE REGEXP_LIKE (City, '^[^aeiou]', 'i') OR REGEXP_LIKE (City, '[^aeiou]$', 'i');
gaurav1292 hey, why you added 'i' in the argument?
47un_ 'i' ensures case insensitive matching https://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions018.htm
mohamedTaman or you can:
SELECT distinct city FROM station where REGEXP_LIKE(UPPER(city),'^[^AEIOU]') OR REGEXP_LIKE(UPPER(city),'[^AEIOU]$');
tanya20_1995 not working :(
mpalanichelvam Please tell me , what is wrong in my query.
1)SELECT distinct name FROM emp WHERE REGEXP_LIKE(name, '^[^AaEeIiOoUu].*[^AaEeIiOoUu]$');
2)SELECT DISTINCT name FROM emp WHERE LOWER(SUBSTR(name,1,1)) not IN ('a','e','i','o','u') AND LOWER(SUBSTR(name,-1,1)) not IN ('a','e','i','o','u');
Both are showing correct output in my local.But,it showed wrong answer as here. Anyone please explain to me.
Note : Here ,
CITY = NAME STATION = EMP.
SanthoshSasi Use OR instead of AND it will work.
abhijit_a_ghatge could you explain this query
BansheeGhoul Oracle other solution ...
SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,Length(CITY),1)) NOT IN ('a','e','i','o','u')and LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
Sanjit_Prasad 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%' AND CITY NOT LIKE 'E%' 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');
franck_busin select distinct city from station where city not like '[aeiou]%[aeiou]'
rgupta_raghav7 SELECT DISTINCT city FROM station WHERE LEFT(city,1) NOT IN ('A','E','I','O','U') OR RIGHT (city,1) NOT IN ('A','E','I','O','U');
Sort 511 Discussions, By:
Please Login in order to post a comment