eternal simple solution for MySQL
SELECT DISTINCT city FROM station WHERE city REGEXP "^[aeiou].*";
aasthaa It is not working!
eternal can u paste the error?
shivam652 [deleted]adityakumar19951 ORA-00920: invalid relational operator 00920. 00000 - "invalid relational operator" *Cause:
*Action: Error at Line: 28 Column: 45gruszecki_adam if U had Error ORA-... U used Oracle database
jagginadivya45 invalid relatioanal operator
jeremy_kim Check if you are using mySQL
malik_masis [deleted]
vijaymanda SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(LOWER(CITY), '^[aeiou]') ;
Use this query it is working fine.
reshantio30 what is the purpose of 'regexp_like'?
laxus123 Regular expressions...!
dushyantsharma71 can't we do it with the help of Like statement and wildcards
jmichaelmedina Yes that works too. Regular expressions are very powerful and worth the effort to practice with and learn. Have fun and enjoy!
shivamckaushik No its not!
vvidhya it worked! thanks!
visitmanish1994 WHATS THE USE OF LOWER
jcarroll1 It converts the string to lowercase. So "AaaAhhAHH" becomes "aaaahhahh"
Sonalim6 I am sorry this is unrelated, but your answer though!! Good one :D
abhaychaudhary91 perfect
Ekta3012 THIS IS NOT WORKING IN MYSQL.
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[AEIOU]' ; THIS IS WORKING.
NitishKumar_bit Nice ur code is working
devbhandari Select distinct city from station where substring(city,1,1) in ('a','e','i','o','u');
santoshimn1 Intelligent solution!
ghanwatrr456 not working
xtakamiya112 [deleted]
xtakamiya112 hi! what is the (city,1,1) for? thanks!
arun_kushvaha This works for me: select distinct city from station where lower(substr(city,1,1)) in ('a','e','i','o','u');
maheshpeddi1234 worked!Thanks
K_Srivatsan y you using city 1,1
dyaramatreya can you pls explain the use of city,1,1
md_salman15 syntax : SUBSTRING(string, start_pos, number_of_chars)
So (CITY,1,1) will consider only first letter of city names to be be compared with vowels.
chevalierAdam2 Ah damn, I tried this but didn't know SQL used 1-based indexing. Still kind of a newb :p
amit_shah0206 [deleted]saifali9680 CAN YOU EXPLAIN YOUR CODE "REGEX '^[AEIOU]';
alberty001 you could replace the LOWER function with the ignore case flag 'i'
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^[aeiou]','i');
akhilamalla1999 [deleted]nupurkumari4965 CAN YOU PLESE EXPLAIN (CITY,'^[aeiou]','i') WHAT i IS FOR
06_01_1998 'i' is for case insensitive refer the link https://docs.oracle.com/cd/B13789_01/server.101/b10759/conditions018.htm
nehakarmakar your code is not working
basilcherat select distinct(city) from station where REGEXP_LIKE (city,'^[AEIOU]');
truptigore17 Also works without lower keyword
guptaamit60 select distinct city from station where city like "a%" or city like "e%" or city like "i%" or city like "o%" or city like "u%";
shubh911 I HAVE WRITTEN THE SAME QUERY BUT OUTPUT IS NOT RESPONDING .SO, SIR CAN YOU PLEASE SUGGEST ME SOME DIFFERENT METHOD TO SOLVE THIS PROBLEM.
chandu_sai89 SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'I%' OR CITY LIKE 'O%' OR CITY LIKE 'U%'
DIS CODE IS WORKING IN SQL
poorvi26g THANKS , THIS CODE IS WORKING FINLY... ITS SIMPLE CODE FOR THIS QUERY
iamguowen I worte the same in SQL server, but it showed wrong answer.
bharathyandrapu1 WHAT IS PURPOSE OF % IN THE ABOVE CODE
jvinniec The '%' is interpreted as a wild card. So
- 'A%' = match anything beginning with the letter A ('Anne', 'Allison', 'Ann Arbor', etc ...)
- '%A' = match anything ending with the letter A ('AurorA', 'BannanA', 'CaliforniA', etc...)
Using the OR clause the answer above is able to chain together a match for each of the five vowls. Because the LIKE clause is case-sensitive the query will fail if any of the cities are not capitalized.
Kunal_M select distinct(city) from station where substr(city,1,1) in ('A','E','I','O','U');
vanshreebapat hi. what is the role of the word 'in' the query? excuse me if the doubt is naive.
akhilsharma2275 https://www.w3schools.com/sql/sql_in.asp take a look of this
rohit1996srivas1 [deleted]
gee_kiss_sexy yeap, same here!
ankursaxenanakul also add A.EI,O,U capital vowels cases , it will work
nupoor_1013 sir why the charlist does not work here? I mean, the code like select city from station where city like '[aeiou]%';
this gives no response.....what is wrong in this sir, please do tell...
kartik_chauhan You do realize that 'aeiou' means to search for names having 'aeiou' as part of them?
shikhar_rawat31 [deleted]csmahori No, it doesn't mean that. In fact, letters inside open brackets are used whenever such problems(of OR opertion) are asked. so select CITY from STATION where CITY like '[aeiou]%'; should be a correct query, but i don't know why it's showing it a wrong answer.
bhupendra1 Agree!
mohitbajpai21 It is correct when you change the mode to My sql server!
cibaIT It's correct, but it works only for MS Access and SQL Server.
yosefvb Make sure to use uppercase in each expression "A%", "E%".., or else it will not work. It is case sensitive and the cities in the database begin with a capital letter.
sivaandsivabe Yes you are correct :)
kaushikmn1994 SELECT CITY FROM STATION WHERE SUBSTR(CITY,1,1) IN ('A','E','I','O','U'); This is easier.... using functions
urvashi_bansal [deleted]akhilsharma2275 1st 1 means place of the character from where to start the sub string and the another 1 is for how many character you want in that sub string.... (1,1)its like (position, length).
DClayton12 Leveraging UNION would be faster and yeild the same result. UNION leads to more SEEKS than SCANS. OR will perform SCANS and therefore more expense cost wise.
select distinct city from station where city like "a%" union select distinct city from station where city like "e%" union select distinct city from station where city like "i%" union select distinct city from station where city like "o%" union select distinct city from station where city like "u%";
emirtartar35 Yes, you right. Thank you for your answer.
szamoscs select distinct (city) from station where 1=0 or city like('a%') or city like('e%') or city like('i%') or city like('o%') or city like('u%') ; --MSSQL
mehedi2003 Start with capital letters:
select distinct city from station where city like 'A%' or city like 'E%' or city like 'I%' or city like 'O%' or city like 'U%';
sbansal796 this code is not working
2digantkaushik [deleted]
ashimagarg This worked for me!
esquare /* Enter your query here. */ SELECT distinct CITY FROM STATION WHERE CITY LIKE 'a%' or CITY LIKE 'e%' or CITY LIKE 'i%' or CITY LIKE 'o%' or CITY LIKE 'u%';
vishalnagpure Try this
select CITY from STATION where CITY REGEXP '^[AEIOU]';
zexianxu so this means that the ".*" at the end of the regexp query is optional in this case right?
thanks!
devbhandari In MSSQL Select distinct city from station where substring(city,1,1) in ('a','e','i','o','u');
soniabhi1 for mysql select city from station where city like 'a%' city like 'e%' city like 'i%' city like 'o%' city like 'u%';
ghanwatrr456 [deleted]ghanwatrr456 it show the error
rgupta123 It is working in MySQL.
khiryz92 what is the REGEXP portion?
EugeneWen It stands for regular expression. I think we can just use LIKE operator, but the charlist wildcard seems not working in MySQL.
cassandrathomas yes, very annoying
will96 so this SHOULD work if the charlist wildcard was working? SELECT DISTINCT city FROM station WHERE city LIKE "[aeiou]%";
lerec7 yeah its not working
coeus Can you tell me how this querry would be modified if the question is IF the cities name END with 'aeiou'.
a3leong I believe your regex would have to be modified to be "*.[aeiou]$" if you want to test for ending with a vowel.
7anner .*[aeiou]$
saikishore_namb1 what is the use of . before []?please tell me
ian_lee_0408 '.'means match any character, thus,
'.*'means match any sequence of character Some more details here: https://dev.mysql.com/doc/refman/5.7/en/regexp.html#operator_regexp
saurabhkrt [deleted]saurabhkrt SELECT DISTINCT(city) FROM station WHERE city RLIKE '[aeiou]$';
deccancharger72 INVALID RELATIONAL OPERATOR
ccuong_ph I had the same idea:
SELECT DISTINCT city FROM station
WHERE left(city,1) REGEXP '[aeiou]'
sunghawksi hi, I used nearly the same code with you, mine is SELECT DISTINCT CITY WHERE LEFT(CITY,1) REGEXP '[aeiou]' FROM STATION;
but it always returns to me that ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE LEFT(CITY,1) REGEXP '[aeiou]' FROM STATION' at line 2
Can you tell me what the difference between yours and mine? btw, is there the 1 in the left(city,1) necessary?
neelusharma THANKS..IT'S WORKING...
dattatreya065 tanq bro its workig
unrealneural Awesome!
kamalnayan Its advised to avoid regular expressions in queries. You can use substr for same:
select distinct city from station where substr(city, 1, 1) in ('a', 'e', 'i', 'o', 'u');
leonardo_dcbv or left(city,1)
zagciba Yes. Whenever there's a need for "IN" , it should be prefered.
MSSQL : substring
select distinct city from station where substring(city, 1, 1) in ('a', 'e', 'i', 'o', 'u');
jcarroll1 What? No. In MSSQL, using
LIKE '[aeiou]%'
will allow the query optimizer to take advantage of any indexes on the City column, while using SUBSTRING will prevent it.
56_aditi what is regular expression where i can read about it
karbhariajay explain REGEXP "^[aeiou].*" and REGEXP"[aeiou]$"
kartik_chauhan select distinct city from station where city REGEXP '^a|^e|^i|^o|^u';
chirag0321 what is ^ for?
dfkrasilov ^ Matches the starting position within the string. In line-based tools, it matches the starting position of any line.
coder1996 try this query in ORACLE
SELECT DISTINCT city FROM station WHERE city LIKE 'A%' or city LIKE 'E%' or city LIKE 'I%' or city LIKE 'O%' or city LIKE 'U%';
ryandonga this is the most simple code that every one can understand, very easy and straight forward
select distinct city from station where Lower(city) LiKE 'a%' or lower(city) LIKE 'e%' or lower(city) LIKE 'i%' or lower (city) LIKE 'o%' or lower (city) LIKE 'u%';
knightofcode oh I did it the hard way using or and checking it for each and every vowel. i learnt a new thing
chilledblitz use
select distinct city from station where city rlike '^(a|e|i|o|u).*' order by city;
aditisati_cherr1 Thanks for this code.It worked well.But can you explain me the function of regexp.
kuridzabranko8 Can you please explain why you put * at the end?
sanathkumar5664 Why doesnt it work for Oracle?
shashankkurakula what is REGEXP ?
kumarinidhiweb This is working on mysql....but not sqlserver.....can u plz do it on sqlserver.....as I am using sqlserver plateform...
rohilla_sanjeev1 Thanks it works fine . Can anybody help me in figuring out the error in
select city from station where city like '^[aeiou]';
sanjay_pallur select city from station where substring(city,1,1) in ('a','e','i','o','u');
Mysql
shraddha_p use RLIKE '^[aeiouAEIOU].*' insted of REGEXP
mathanolwin SELECT DISTINCT city FROM station WHERE city REGEXP "^[aeiou]";
This is enough. Why .*
marm0t [MySQL] SELECT DISTINCT city FROM station WHERE city REGEXP '^[aeiou]';
agentvenom regexp '^[aeiou]'; ^ stands for 1st alphabet.
kpe01 I think that becomes a greedy search. So probably this is sufficient:
SELECT DISTINCT city FROM STATION WHERE city RLIKE '^[aeiou]';adityagesh SELECT CITY FROM STATION WHERE CITY REGEXP '^[aeiouAEIOU].*';
CorgiDev How would this differ if using MS SQL Server?
nchitturi ORACLE:
SELECT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,1,1)) in ('a','e','i','o','u');
taylormano I added a distinct()
select distinct(city) from station where lower(substr(city,1,1)) in ('a','e','i','o','u');
renuabi15 CAN U EXPLAIN THIS CODE IN DETAIL WHY U USE SUBSTRING 1,1
taylormano For Oracle: substr(string, start_position [, length ] ) Works like: substr('abcdef', 1) would return 'abcdef' , substr('abcdef', 1, 1) would return 'a', substr('abcdef', 2, 3) would return 'bcd'. For this specific problem you only want the first character in the string hence substr(city,1,1). You can go to https://www.techonthenet.com/oracle/functions/substr.php for more info.
ishu_mishra97 purpose of lower(substr(city,1,1))
gayuhoney003 WHY U USING LOWER HERE....WHAT IS THE PURPOSE OF IT..CAN U PLZ RLY...
er_shreyansh1991 if you don not use lower then
if City Name starts from 'A' which is not in the set of ('a', 'e', 'i', 'o', 'u'). then it will give wrong result. So either use ('A', 'a', 'E', 'e', 'I', 'i', 'O', 'o', 'U', 'u') as a set to test or use lower method. i think lower is much better to use.
HarishTati why we use lower in code
hafizusmanaftab yes its work greate!!!
kamalnayan Its a SQL query in ANSI syntax, so it will work in all DBMS that follows ANSI, no matter being Oracle or mysql. I have compiled with mysql version as:
select distinct city from station where substr(city, 1, 1) in ('a', 'e', 'i', 'o', 'u');
harshitha2922 y is it "substr(city,1,1)"?? can u please explain y did u write that?
melodyhe For SQL server, I did it as below:
SELECT DISTINCT city from station where city LIKE '[a, e, i, o, u]%'ORDER BY city;
Aissag why u use ORDER BY city;??
maredla SELECT DISTINCT city from station where city LIKE '[a, e, i, o, u]%';
sakshihanda this is not working for me
chrome Did you select MySQL as search engine? Default is Oracle.
shawon10 Here is an easy solution for mysql that is working
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE 'a%' OR CITY LIKE 'e%' OR CITY LIKE 'i%' OR CITY LIKE 'o%' OR CITY LIKE 'u%';
archygupta22 why it is not working in oracle? can you tell me the solution for oracle?
syedahmed085 This solution definetly works in oracle but result set will not return any row because the names of city start with capital letters and not with small letters, so use like this
select distinct city from station where city like 'A%' or city like 'E%' or city like 'I%' or city like 'O%' or city like 'U%';
kamalnayan What if you are given consonants instead of vowel? You will write this
lik21 times? Better to use substr, Check my other comments to get exact query.
mellisabai SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[a,e,i,o,u]'
This one works in mysql
monika_singla261 why can't i use like instead of regexp? i wrote the same query but used like and its not working
amit_kumar09679 i used this code .but it is showing error but it is working at w3school
santhoshkumar5 this not working for me
souvikmodak SELECT UNIQUE CITY FROM STATION WHERE (CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'I%' OR CITY LIKE 'O%' OR CITY LIKE 'U%') ORDER BY CITY;
MaheshPandey21 ----MY SQL SERVER---
select CITY from STATION where CITY LIKE '[aeiouAEIOU]%' group by CITY;
prlpzb It sems that LIKE '[AEIOU]%' just works for MS SQL Server, but it doesn't work for MySQL and the other environments I tried - I solved the issue there using "OR". Is there a reason that prevents the brackets from working in MySQL?
rahul_suman It's working but there is no need of group by over here
jo_ravar Your query is not giving any output.
lsopromadze SELECT CITY FROM STATION WHERE LEFT(CITY,1) IN ('a','e','i','o','u') GROUP BY CITY
ManasaPedavalli Your query is not working.
d_srikanth1129 select distinct city from station where left(city,1) IN ('A','E','I','O','U');
Try this. It worked for me.
Gauri94 Heyy thanks shrikant....
Yash_44 use REGEXP '^[aeiou]' it will select the citynames those which are starting with vowels regexp means it is similar to like operator and defined as regular expresion "^" - this symbol indicates starting. check select distinct city from station where city REGEXP '^[aeiou]' :)
srirampknss its not working !! ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEXP '^[aeiou]'' at line 2
Gauri94 wrong answer
devendra_iips Using MYSQL
SELECT DISTINCT city FROM station WHERE SUBSTR(CITY,1,1) IN ('A','E','I','O','U');
nanhe Why the same query does not work in oracle?
MattCarr I really wish postgres was an option for these SQL challenges.
Siddgupta_home Agreed, I was trying to do this in postgres...
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(City, '^[AEIOU]');
Feel free to ask if you have any questions :)
rohit167822 Sir, 1.) What does REGEXP_LIKE do?
2.) And why can't we use -->
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE 'A%' OR CITY LIKE 'E%' OR CITY LIKE 'I%' OR CITY LIKE 'O%' OR CITY LIKE 'U%';
marinskiy Hey)
REGEXP_LIKE allows to use regular expressions and write quiries with really complex conditions. For example:
^[AEIOU]
This condition means that the first letter is eather A, E, I, O or U.
Check out this lik to documentation: Using Regular Expressions in Database Applications
Using LIKE is pefectly correct here. However, I believe REGEXP_LIKE solution is less wordy. And also, it's useful to learn REGEXP_LIKE for next problems in this section.
rohit167822 Thank u sir for the info :)
Selvaa_EEE what for "^" this symbol is used and how does it works??
damonporch It indicates to match with the front of the string.
^a matches apple/ant but wouldn't match dad or _apple.
It also works with more characters for example, ^def will match definitely.
Oracle docs talks about it https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_regexp.htm#i690823 if you scroll down to ^.
marinskiy "^" - means the first symbol. So ^[AEIOU] means first symbol is eather A, E, I, O or U.
Check out this lik to documentation: Using Regular Expressions in Database Applications
Aris006280 Really Work
Select City From Station WHERE LEFT(City,1) IN ('A','E','I','O','U')
aakash101agarwal You can also do by using substring method
SELECT CITY FROM STATION WHERE SUBSTRING(CITY,1,1) IN ('A','E','I','O','U')
Nierrrrrrr I love regex. Oracle sql query:
SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(UPPER(CITY), '^[AEIOU]');
HarishTati hello.please explain your code.what's the use of regexp and why you wrote upper keyword in that.please
Nierrrrrrr REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE.
source: Oracle Docs
The UPPER keyword just to make sure no matter the first character of city is upper case or lower case, it always convert it to upper case, so we can only check for 5 charaters (AEIOU) instead of 10 characters (AEIOUaeiou).
kelly_freet13 Didn't see a clear MSSQL, so here is mine.
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[AEIOU]%';
The [] specifies any character within the brackets, and the % specifies any string of zero or more characters after the first letter within [].
ashuwp This works for MySQL Server
pooja_cse2308 SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) IN ('a','e','i','o','u');
xpioneer2 select distinct city from station where lower(substr(city,1,1)) in ('a','e','i','o','u') works
hhuang2088 This is my solution:
SELECT DISTINCT CITY FROM STATION WHERE CITY RLIKE "^[aeiou]";
paliwalgaurav can you please exlpain this?
hhuang2088 This particular query only works in MySQL.
What this query does is it prints out the names of cities who's names matches the regex passed through RLIKE.
Sort 658 Discussions, By:
Please Login in order to post a comment