pooja_cse2308 SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,LENGTH(CITY),1)) IN ('a','e','i','o','u');
shubhi_D thanks I was looking for a compact solution
dhanyasj01 You can use negative indexing in Oracle. select distinct city from station where upper(substr(city,-1)) in ('A','E','I','O','U');
rupendraprataps1 thanks
anantsaini10 Why do we need to add upper or lower function. why can't we get values just by SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(CITY,LENGTH(CITY),1) IN ('a','i','o','e','u');
rupendraprataps1 Because name of city can be in upper or lower case so change according to what u writing in 'in' if u writing in in lower change to lower if u writing in upper change to upper
Deepak511 because oracle is case sensitive.the value can be in lower or upper case in city column. suppose city value is Mumbai,here upper function convert the value to MUMBAI and it exactly match with the value you give in where clause like MUMBAI=MUMBAI.hope it helps!
Nishanth15 because last character can be uppercase , so that if we use lower last character will be converted to lower case and then we can check with lower case vowels
vcsshyam1998 it is not working
Ratnesh88 For Oracle
SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(CITY,LENGTH(CITY),LENGTH(CITY)) IN ('a','i','e','o','u');
For MySql
SELECT DISTINCT CITY FROM STATION WHERE RIGHT(CITY,1) IN ('a','i','e','o','u');
rachanabharathi1 can you please explain this logic
nileshsavale9049 First LENGHT is to get position of last character.what is use of second LENGTH in SUBSTR for oracle
palash_rawat [deleted]palash_rawat SUBSTR(string, start, length)
or
SUBSTR(string FROM start FOR length)
So with 1st Length(city) it will start from the end of the city name.
With 2nd Length(city) it will take the complete city name(length of it, starting from the last or back).
palash_rawat Extract a substring from a string (start from the end, at position -5, extract 5 characters):
SELECT SUBSTR("SQL Tutorial", -5, 5) AS ExtractString;
palash_rawat SUBSTR(string, start, length)
or
SUBSTR(string FROM start FOR length)
So with 1st Length(city) it will start from the end of the city name.
With 2nd Length(city) it will take the complete city name(length of it, starting from the last or back).
palash_rawat Extract a substring from a string (start from the end, at position -5, extract 5 characters):
SELECT SUBSTR("SQL Tutorial", -5, 5) AS ExtractString;
palash_rawat Can you please explain the first query for Oracle. Specially the part- SUBSTR(CITY,LENGTH(CITY),LENGTH(CITY)).
I am not able to understand what LENGTH(CITY) does as it is written 2 times.
vinodkumarmuppe1 here, what is the use of RIGHT pls explain me.
cute_ramesh01 SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(CITY,LENGTH(CITY),1) IN ('a','e','i','o','u');This is working perfectly
provatsikdar select distinct CITY from STATION where SUBSTR(CITY,LENGTH(CITY),1) IN ('a','i','e','o','u');
favianioel I made it a bit more simple:
SELECT DISTINCT CITY FROM STATION WHERE RIGHT(CITY,1) IN ('a','e','i','o','u');
tsp170501997 code not working
kostyapride U GOD DAMN RIGHT
ediarto i try like that, but got wrong answer :D
toheebbellob01 Perfect code. U good
srinivasanguna30 can u please explain this code?
kostyapride How does work this part? SUBSTR(CITY,LENGTH(CITY),1) It will take 1 word from the end of length? and if it equals 'aeiou' so it's what we are looking for?
arajapandi city REGEXP '[aeiou]{1}$'
sliu203 [deleted]SPEC7RE Negative indexing works in MySQL too - WHERE LOWER(SUBSTR(city,-1)) IN ('a','e','i','o','u');
rohitnk MySQL:
where City regexp '[aeiou]$'
ShkKiit Hey bro it should end with a|e|i|o|u so how does this regex work? also shouldn't this regex try to search for all of the letter of aeiou? Please give a little explanation
songkoko The $ symbol marks the end of the string. So it matches strings that end with a,e,i,o, or u.
sachin_autage361 But also not Working of this Symob($)......
eedayley Square brackets match any of the characters inside.
MobilityWins where regexp_like(lower(city),'[aeiou]$') this is better and works using ORACLE
truptigore17 Hello,
I am a newbie here. I got it without using lower, wonder why would you use lower?Does that not increase data manipulation?
arajapandi city REGEXP '[aeiou]{1}$'
RodneyShag MySQL solution
From my HackerRank solutions.
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '[aeiou]$';
$ matches End of string
[...] matches any character listed between the square bracketsUseful Regular Expression Reference
Let me know if you have any questions.
kodaivasanth its not working.. invalid relational operator...
RodneyShag I just tried it for you and it works. Make sure you use MySQL.
billal3938 Your code is too good
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 :)
gargdeepa what should be done if we want to sort acc to last character...
AvmnuSngHackerRank Admin Why you want to do this? Just sort the cities that end with vowel
gargdeepa select 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; what is the error in this?
kasiraman Missing Upper cases
Angsuman25 Q) sort acc to last character. A) SELECT CITY FROM STATION ORDER BY SUBSTR(CITY, LENGTH(CITY), 1);
Sojanya You can also use right keyword.
SELECT DISTINCT City FROM Station WHERE RIGHT(City, 1) IN ('a', 'e', 'i', 'o', 'u')
malik_masis why we have to write 'right' ? i tried in ('%a',%'e' ..) but did't run. after i changed with like '%a' or like '%e'
gemdolly Thanks, This is simple and easily understandable.
Sahasransu you will get duplicate data with that query. Try distinct or unique keyword for Oracle.
Ank05 SELECT Distinct CITY FROM STATION WHERE (CITY LIKE '%[aeiou]') order by city
This should also work as logocally it is just the same query as in previous statement, please advise!
Sahasransu Yes your logic is correct. But you need to use the right oracle functions to achieve that. The query will be : SELECT Distinct CITY FROM STATION WHERE REGEXP_LIKE (CITY,'[aeiou]') order by city
bhargavkumar47 can u please explain me what's wrong in this
select distinct city from station where city like '%[aeiou]'; in mysql? Thank you
Sahasransu When you use like its just a Simple string pattern match . So for ur input of %[aeiou] it will look for a string having any Characters followed by [aeiou] which includes the Square brackets also in the String . Using REGEXP_LIKE Method you will search for a regex patten instaed of the simple String.
weih_chan Not all DBMSs support sets with Like [abcd....]. Microsoft Access and Microsoft SQL Server support sets with Like, not Oracle and MySQL
vidhigoel4 why have you used order by?
so9835s you were generally on the right track, but the challenge was to find cities ending in vowel letters. so the correct query would be SELECT Distinct CITY FROM STATION WHERE REGEXP_LIKE (CITY,'[aeiou]$') order by city;
sns45 This is how I did it in oracle.
select distinct city from ( (select city from station where city like '%a') union all (select city from station where city like '%e') union all (select city from station where city like '%i') union all (select city from station where city like '%o') union all (select city from station where city like '%u'));
gemdolly It works but has duplicate values
rabbitfighter All you have to do is know what ^[x] and [x]$ mean in RegEx. It's very simple to just say this:
SELECT DISTINCT city FROM station WHERE city REGEXP '[aeiou]$';
sanjay_dev what is difference between '^[aeiou]' and '[^aeiou]'
harrypotter0 ^[aeiou] means select anything which starts with a or e or i or o .
[^aeiou] means select anything which does not contains a|e|i|o|u . Hope that helps :)
sj_barua2014 using distinct keyword 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'order by city;
rsv1193 You need to select the distinct rows by using distinct(city),it will work fine
dpshpoudel101 no need order by city you can just do select distinct city
Vasanth_Kumar12 nothing much just give a "distinct" keyword to it
mahiv77 add distinc
1241997yvn THEY ASKED THE CITY NAMES WITH OUT DUPLICATES SO YOU HAVE TO USE DISTINCT KEY WORD WHILE PROJECTING THE COLUMN_NAME
naveensurya98765 add distinct after the select
sourabhT ADD DISTINCT
mktriv use select distinct and rest same
prasannakumarko1 we get error if we use double quotes i.e "%a"
saikadiyala201 You have to write distinct before the first city
souvikmodak 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') order by city;
piyusman MYSQL : 1. SELECT DISTINCT(CITY) FROM STATION WHERE CITY REGEXP '[A,E,I,O,U]$';
Also
SELECT DISTINCT(CITY) from STATION WHERE substr(CITY,LENGTH(CITY),1) in ('A', 'E', 'I', 'O', 'U');
Also
SELECT DISTINCT CITY FROM STATION WHERE RIGHT (CITY, 1) IN ('A','E', 'I', 'O', 'U');
gouravsingh510 Not Working
chpreetham322 Why did you order by city? I mean they didnt ask it right,the question was to avoid duplicates.
AllisonP You do not need to sort by last character, only filter out cities not ending in vowels. In addition to the wildcard-vowel comparisons mentioned above, you could also use a case-insensitive ('i') regular expression ([aeiou]) to find cities that end ($) in vowels:
Where REGEXP_LIKE (City, '[aeiou]$', 'i')Do some reading on regular expressions. http://www.techonthenet.com/oracle/regexp_like.php
aashil I get the following error while using REGEXP_LIKE in MYSQL:
Query:
select distinct city from station Where REGEXP_LIKE (City, '[aeiou]$', 'i');
Error:
ERROR 1305 (42000) at line 4: FUNCTION oiqzktlmtdsyl4sw.REGEXP_LIKE does not exist
chinmay10 REGEXP_LIKEis for Oracle, for MYSQL use following :WHERE CITY REGEXP '[aeiou]$' ;
wiicontatos 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';
nmcastro1 I tried exactly what you listed here, except condensed the LIKE op into one:
WHERE city LIKE '[aeiou]%' But it didn't work. So I instead went with what you wrote. I don't get why it didn't. I've tried it elsewhere and the command runs fine.
brunoalsantos Solution in DB2:
select distinct city from station where RIGHT(city,1) in ('a','e','i','o','u');
rysmakAsked to answer That's the way I've done!^^
gaikwadn099 Can you please elaborate this?
kodaivasanth in hackerrank i get "invalid identifier" as an error
indianadi2008 SELECT DISTINCT(CITY) FROM STATION WHERE SUBSTRING(CITY,LENGTH(CITY),1) IN ('a','e','i','o','u');
abhinavjaiswarAsked to answer Can you also brief the question statement in the comments ?
jenrademacher Why isn't 'y' as a last letter counted as a vowel? I tried including it, but got an incorrect answer.
apurbasinghAsked to answer Yes you can. select DISTINCT CITY from STATION where CITY LIKE '%[a|e|i|o|u|y]'
hemakutti21 SELECT DISTINCT(CITY) from STATION WHERE substr(CITY,LENGTH(CITY),1) in ('A', 'E', 'I', 'O', 'U');
nadimintikrish I like this approach
songkoko Because that's not what the problem asked for?
Sort 435 Discussions, By:
Please Login in order to post a comment