RodneyShag MySQL solution
From my HackerRank solutions.
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou].*[aeiou]$';
Let me know if you have any questions.
JackySatpal why can't we do like the below? CITY REGEXP '^[aeiou]$'
RodneyShag [deleted]
Ippo343 Nope, it's because the regex is wrong.
^ // string's beginning [aeiou] // a SINGLE vowel $ // end of string
Therefore this would match the vowels and only the vowels. Even a string like "aaa" would not match, because it's more than 1 char long.
The correct one is the one you used at the beginning because of the ".*" bit:
^ // start of string [aeiou] // a single vowel . // any characted... * // ...repeated any number of times [aeiou] // another vowel $ // end of string
fjhahn This was helpful... Thanks for the in depth explanation
youssefaabdo Thanks
ssrini21 Why does not below condition work?
regexp_like (column_name, '^[aeiou]*[aeiou]$','i')
yadavricky because you have . missing before . select distinct city from station where REGEXP_LIKE (lower(city),'^[aeiou].[aeiou]$');
TurboPascal Wow, thank you :)
TomYumNoodle hello,
So why doesn't this work?
select distinct city from station where city regrex '^[aeiou]|[aeiou]$';
I followed the example used here: https://www.tutorialspoint.com/mysql/mysql-regexps.htm
Thank you!
psatishpatro88 if u write ''^[aeiou]|[aeiou]$', it will check only start letter vowel or end letter vowel like abcdfsd, efdsf, osdf(staring one vowel), dsfa, dsde, fghfgo(ending one vowel)
if u remove the pipeline, '^[aeiou][aeiou]$' it will check only start letter vowel end letter vowel and nothing between them
if u write '^[aeiou].[aeiou]$'; adding one dot means start letter vowel or end letter vowel and any one char between them
if u write '^[aeiou].*[aeiou]$'; adding one * means start letter vowel or end letter vowel and any one character repeated 0 or more than 0
Hope, u get it
TomYumNoodle Thank you so much!
psatishpatro88 You are welcome
Nvaishnav awsome explanation
gauravHD Thank you for the explanation!
thefriction That was very informative was thinking to skip this one becuase of regex but then your comment popped up.Thanks
gaurisankarj1 You're asked for the one's which start and end in vowels, you've only accounted for the one's starting, it would either be an error or the $ will not function like you'd like it to.
suraj97a it will be a compilation error as the syntax is wrong
michelle__K can we use + instead of *??
RodneyShag - passes the testcase but it's technically not correct since it would match strings of a longer length. * is 0 or more of a character. + is 1 or more of a character. As long as you know the difference between the 2 symbols you should be fine.
vyomeshaudi [deleted]vyomeshaudi [deleted]vyomeshaudi can you plz tell what does .* is used to do in your code
RodneyShag Hi. According to this reference, The "." matches "any single character". The "*", matches "Zero or more instances of preceding element". Together, they match 0 or more of any character.
divine_soul It works without .* also.Is it right?
RodneyShag I tried it without the .* as you suggested and it does not work.
divine_soul SELECT DISTINCT city FROM station WHERE city REGEXP '^[aeiou]' AND city REGEXP '[aeiou]$';
Actually i did it this way,i forgot to mention.
RodneyShag Nice solution
skarthikamca Why my query is not working ?
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE 'a%a' OR CITY LIKE 'e%e' OR CITY LIKE 'i%i' OR CITY LIKE 'o%o' OR CITY LIKE 'u%u'
RodneyShag Because a city name can start with "a" and end with "e", which your code does not account for. The first and last letters do not necessarily have to be the same.
skarthikamca thanks
basantdwivedi most welcome.
dhrubodeepbasum1 Because you did not check all the permutation of vowels, there are more cases like aa,ae,ai,ao....etc.
basantdwivedi select distinct(city) from station where substr(city,1,1) in('A','E','I','O','U') and substr(city,length(city),1) in('A','E','I','O','U');
what about this one? This one is also not working..while i have checked in oracle 10g .It's working there but not here.
swathivenkat0191 You need to change the case of the end character : select distinct(city) from station where substr(city,1,1) in('A','E','I','O','U') and substr(city,length(city),1) in ('a','e','i','o','u');
dev_joebar I like this sol'n and discovered there are these functions to create the substring a bit easier:
and
right(string, number_of_chars)mysql, sql server, and postgres all appear to have
leftandrightsupport.
isolsgrouptesti1 its not working i was implement same logic
akshayguptapuram because ur code works for starting and ending with same vowel but actual query is it may start with one and end with other vowel
basantdwivedi question is saying that STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. It means that first and last letter must start and end with vowel any one i.e (a,e,i,o,u,A,E,I,O,U) .
In my query i have used ^[AEIOUaeiou] two times, one for checking the vowels at starting and other for checking vowels at ending.
divyesh_shetty17 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%') AND (CITY LIKE '%A' OR CITY LIKE '%E' OR CITY LIKE '%I' OR CITY LIKE '%O' OR CITY LIKE '%U')
This should work
hasan_abdulhasi1 EXCELLENT
mprksh1047 Can you please explain this piece of code in little more detail? Thanks
RodneyShag https://www.hackerrank.com/challenges/weather-observation-station-8/forum/comments/399775 - this user comment explains it better than I could explain it.
nitishkumar8195 SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '[aeiou]$.*^[aeiou]'; what is wrong in this ?
sivameda1 can you explain your query briefly...
RodneyShag https://www.hackerrank.com/challenges/weather-observation-station-8/forum/comments/399775 - this user comment explains it better than I could explain it.
basantdwivedi select distinct(city) from station where substr(city,1,1) in('A','E','I','O','U') and substr(city,length(city),1) in('A','E','I','O','U');
why this one is not working?
vtarawade Because it doesn't cover all test cases. If you add the lower case characters as well, it should work. In an oracle session, you can fine tune this behaviour by setting the NLS_COMP and NLS_SORT session parameters in NLS_SESSION_PARAMETERS
rex_liu_y select distinct city from station where left(city,1) in ('a','e','i','o','u') and right(city, 1) in ('a','e','i','o','u')
ajayverma1244 SMART
deepsammanna select DISTINCT CITY from station where city regexp '[aeiou]$' and city in (SELECT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]');
deepsammanna will this have a performance issue?
evil_eyeguy simplified by taking out the nested SELECT and just replacing it with another REGEXP.
So we have for mySQL:
SELECT DISTINCT city FROM station WHERE city RLIKE '^[aeiou]' AND city RLIKE '[aeiou]$'
RLIKE is just a synonym for REGEXP for those who don't know.
Also, simplified even more by (credit: rshaghoulian):
SELECT DISTINCT city FROM station WHERE city RLIKE '^[aeiou.*aeiou]$'
rohit167822 this doesn't work for oracle, is it for some other database server? If yes, then of which?
phaberest I wrote the same but it doesn't work. Weird, I just ran it in my local database on a real table and there it just works.
It is not as clean as the regex version mentioned in the discussion, but well...
hwang20 u r a god
kezzetz ooh waw! thanks it works
hacknik246 This is in Oracle SQL without REGEX using only basic SELECT statements. It's easy to read. Hope it helps.
SELECT 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%' ) AND ( LOWER(city) LIKE '%a' OR LOWER(city) like '%e' OR LOWER(city) LIKE '%i' OR LOWER(city) LIKE '%o' OR LOWER(city) LIKE '%u' );
Tmtorres82 [deleted]
stacyjay2008 select DISTINCT (city) from STATION where REGEXP_LIKE(city, '^[aeiou]','i') and REGEXP_LIKE(city, '[aeiou]$','i');
using regular expression is easier
avinasp [deleted]avinasp @hacknik246 Terrfic, after many trials, your code worked! Thanks!
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 :)
misbahnazneen perfect and easy
158W1A1247 It's Working.. SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,1,1)) in ('a','e','i','o','u') and LOWER(SUBSTR(CITY,LENGTH(CITY),1)) in ('a','e','i','o','u');
158w1a1202 This is also working... thk u fr ur code ...this helped me a lott..
select distinct CITY from STATION where substr(UPPER(CITY),1,1) in ('A','E','I','O','U') and substr(UPPER(CITY),-1,1) in ('A','E','I','O','U');
Carlosdlr check this one works with the oracle compiler version using by this site
SELECT DISTINCT(city) FROM station WHERE REGEXP_LIKE (lower(city), '^[aeiou]') and REGEXP_LIKE (lower(city), '[aeiou]$');
gokhansavas90 SELECT distinct City FROM STATION WHERE City LIKE '[A,E,I,O,U]%[A,E,I,O,U]'
AKSHAY97 Not working in mysql
rohit167822 Not working in ORACLE
ICPhi This can be simplied I believe but here is my =soultion for MySQL Luckily left doesnt get greedy in these type of situations.
select distinct city from station where right(city, 1) in ('a','e','i','o','u') and left(city, 1) in ('a','e','i','o','u');
shruti_sri In oracle;-
Try this.
select distinct city from station where REGEXP_Like(City, '^[AaEeIiOoUu]') and REGEXP_Like(City, '[AaEeIiOoUu]$') order by city;
ashi_sri_18 Thanks shruti
Sort 563 Discussions, By:
Please Login in order to post a comment