rshaghoulian MySQL solution
From my HackerRank solutions.
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou]';
Putting a ^ inside the closed bracket means something completely different than putting it outside the brackets. Putting it inside the brackets makes it match all characters EXCEPT the ones inside the bracket. So instead of writing [bcdfghjklmnpqrstvwxyz], we can write [^aeiou]
dungen_coder why did you include ^ outside the closed bracket..
rshaghoulian Putting a ^ inside the closed bracket means something completely different than putting it outside the brackets. Putting it inside the brackets makes it match all characters EXCEPT the ones inside the bracket. So instead of writing [bcdfghjklmnpqrstvwxyz], we can write [^aeiou]
dungen_coder yeah as i did write [^aeiou] this but the query failed .. it only worked after i included ^[^aeiou]. so this means query the names of all the city which are not starting from vowel s we could have just used [^aeiou] this .. but it didnt worked explain please
rshaghoulian The first ^ is to make it match the beginning of the String. The second ^ is negation. You need both to work.
If you just did [^aeiou] it would be strings without aeiou. You need the extra ^ in ^[^aeiou] to make sure to match strings that dont BEGIN with aeiou.
sailormonkey could yout tell me where can I read about REGEXP as i m new to mysql
hvogue This link is a nice, brief, intro. It says for google analytics, but it covers the basic symbols, and is MUCH more readable than lots of other resources.
sailormonkey thanks
h597679894 Thank u!
sahuldeo1 Thanks a lot :)
kishan201263 thanks sir, It helped me a lot to understand this concept...... Can u pls suggest me from where i should learn sql to strengthen my concept.... I m a beginner so still struggling with new concepts..Thanks in advance.
rshaghoulian Hi. I personally learned the basics of MySQL from CodeAcademy. It took a few hours. Next I did this HackerRank SQL track which was also helpful.
markpuchalaii Elegantly put. Glad to have you here helping us understand.
adityakumar19951 what the use of ^[aeiou]? it means negation means string not started with 'aeiou', right.
rshaghoulian Correct.
^[aeiou]
is city names that *start with vowels"
^[^aeiou]
is city names that start with anything that's not a vowel
somiya Very nice explanation!! Thank you sir-:)
rgulshan Select distinct city from station where city not regexp '^[...aeiou]';
harishshet222 ^ indicates the beginning of the string. $ indicates the end of the string.
selects those which dont contain AEIOU in the begining. ^[^AEIOU]
jayatip7 As per your answer, the city will not contain vowels at all. They have just asked: not starting with vowels.
rshaghoulian The additional ^ that's the first character in the regular expression makes [^aeiou] apply to just the 1st character of the regular expression. So, the reg. exp. can contain vowels.
m_lewis_bsba My solution was similar, but may be easier to read considering the questions below:
`mysql SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[aeiou]';'BansheeGhoul For oracle .....
SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
kumar_aishwarya1 This also works:
select distinct CITY from STATION where lower(substr(CITY,1,1)) not in ('a','e','i','o','u');
Can you please tell me the difference in the substring function of the two solutions?
Sorry I am pretty new to this!
zexianxu Hello,
May I ask if there is any difference if we add .* into the expression:
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou.*]'; SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou].*';
Edit: Based on the comments below, the 1st expression above is not correct because it means the string doesn't start with a,e,i,o,u and also doesn't start with . or *.
But how about the 2nd expression? I'm wondering whether it is correct or not.
Thanks!
rshaghoulian Yes, that is the same since .* matches 0 or more of any character.
joshiabhi205 would the same query will execute for oracle?
rshaghoulian Sorry I've never coded any Oracle.
bonniegu0429 In the square brackets, the characters have no special meaning. So if you include .* in the square bracket, it means the string doesn't start with a,e,i,o,u,.,*.
zexianxu thanks! then how about the following? is it also valid for this case?
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou].*';
bonniegu0429 I think it is valid, but it's redundant to add .*
zexianxu I see. Thanks!
greenhand1 or you could use where city not regexp '^[aeiou]'. This is less confusing
rshaghoulian Agreed, yours is clearer. I'm going to leave my solution as the fancy way since it's good Regex operator practice.
kumaramitdav1996 IT IS NOT WORKING ON ORACLE
rshaghoulian This solution works on MySQL only. Oracle requires different syntax.
BansheeGhoul For Oracle .....
SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
danivilela Oracle : SELECT distinct CITY FROM STATION WHERE NOT REGEXP_LIKE(city ,'^(A|E|I|O|U)');
venkytillui49 Select distinct CITY from STATION where not CITY like '[aeiou]%';
khannaneha1 why like is not working ?
BansheeGhoul For Oracle .... SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
sripavuluri77 what's the use of ^
rshaghoulian I answered this in an earlier comment
nainularab999 It is showing invalid relational operator
rshaghoulian Hi. Make sure you are using MySQL and the code will work properly.
abhitomar9992 I DIDN'T UNDERSTAND WHAT YOU ARE EXPLAINING?? WHY WE ARE USING ^ SYMBOL TWICE?
rshaghoulian This comment may answer your question
evil_eyeguy You can also use the NOT keyword.
Like so:
SELECT DISTINCT city FROM station WHERE city NOT RLIKE '^[aeiou]'
_ManasH_ what is the difference between regexp '^[aeiou]' , regexp '[^aeiou]' and regexp '^[^aeiou]' ?
rshaghoulian Hi. I updated my original comment to answer your question.
_ManasH_ Thank you very much
jabongg select DISTINCT city FROM station WHERE substr(city, 1, 1) NOT IN ('a','e','i','o','u');
ryankawall01 I did this exact thing and it did not work..still wondering why
ryankawall01 Nevermind , I figured it out. Needed to add LOWER
BansheeGhoul For Oracle ...
SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
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 :)
architg0409 select distinct city from station where substr(city,1,1) not in ('A','E','I','O','U');
Sowmiya12 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%') ;
ankilshah SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou]';
POORVI29 Can you plz explain ...
MobeenAftab HI, he is using regular expressions to case match a set of string tokens against the DB results. If i was to translate this regexp it would go something like this: select all(^) words beginning with tokens within this set [^] - that do not match. So by defining a set of tokens, he has set a query to ignore any word beginning with any character within the set [^]. I wrote a small intro into regexps on my blog you can view here: http://mobeenaftab.com/2017-02-25-Regular-Expressions/ its not finished but you should get the basic jist of regexp syntax.
elevenal MS SQL Server
SELECT DISTINCT CITY FROM STATION WHERE CITY not LIKE '[aeiou]%';
kostyapride Thanks! I forgot about 'not'
alexanderallane [deleted]
paria It works for me .
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%');
Forbidden_Devil Yeah... It helped a lot...Thanks...
Anand_atul select distinct city from station where left(city,1) not in ('a','e','i','o','u');
select distinct city from station where city not regexp '^[aeiou]';
viralshah1494 SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE (CITY, '^[^AEIOU]','i');
For oracle
shwetajain676 select distinct city from station where substr(city,0,1) not in ('A','E','I','O','U');
NilEeSHGaIkWaD u just replace substr(city,1,1) it will work properly and in substr() first parameter represent string on which operation perform and second one parameter represend length of character and third one parameter represent index of character in string
newhoopsdynasty Dumb question, but wouldn't it be (city,1,0)? I just thought usually the index(of a string, or anything) starts at 0
Sort 268 Discussions, By:
Please Login in order to post a comment