We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
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]
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]
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
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.
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.
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.
Putting it inside the brackets makes it match all characters EXCEPT the ones inside the bracket. WHAT ELSE REMAIN? putting it outside makes it match WITH city name STATRS WITH remaining characters i.e..,(consonants)
Certainly! The SQL query you provided is used to select distinct cities from the "station" table where the first letter of the city is not a vowel (i.e., not 'a', 'e', 'i', 'o', 'u'). Here's the query:
sql
Copy code
SELECT DISTINCT city
FROM station
WHERE LEFT(city, 1) NOT IN ('a', 'e', 'i', 'o', 'u');
This query will retrieve distinct city names from the "station" table where the first letter of each city does not belong to the specified set of vowels. Adjust the table and column names if they differ in your database schema.
thats because we still need to tell SQL to look for first character in the string.
The first ^ in '^[^aeiou]' points to the first character in the string.
the second ^ lets SQL know that we want to exclude these characters.
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.
The city name starts with the letter capital. To change upper case letters into lower case we use lower here because we are using lower case letters in 'not in' function in where clause.
or we can use the below code without using lower by putting upper case letters in 'not in'
SELECT DISTINCT CITY FROM STATION WHERE (SUBSTR(CITY,0,1)) NOT IN ('A','E','I','O','U');
Lower coverts what we get to the Lower Case.
substr is used to get some part of the complete string. In this case we will get the (0 to 1) i.e Start from 0 and end at 1 index.
Example:- Illinois -> Will give result as 'i' if we use lower(substr(city,0,1));
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.
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,.,*.
SUBSTR (String, start, length) -> CITY is String & when we start index starts with 1 basically the length it depends u can select upto how much length u wanted to.
for ex: SUBSTR("LeaRn N Practice",3,5) -> so start at index 3 i.e., 'a' & continue till length of 5 i.e., (aRn N) is the o/p.
If u want all the charcaters of o/p to be in lower case use LOWER.
SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '^[^aeiou]';
Can u please tell me whats the error in my query.
Its giving me a runtime error
Msg 4145, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'.
Weather Observation Station 9
You are viewing a single comment's thread. Return to all comments →
MySQL solution
From my HackerRank solutions.
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]
why did you include ^ outside the closed bracket..
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]
HackerRank solutions.
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
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.
HackerRank solutions.
could yout tell me where can I read about REGEXP as i m new to mysql
Intro to Regular Expressions
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.
thanks
Thank u!
Thanks a lot :)
Thanks !
https://www.hackerrank.com/domains/regex/re-introduction
I think this is a good explaination for regexp.
Oracle:https://www.techonthenet.com/oracle/regexp_like.php
Mysql:https://dev.mysql.com/doc/refman/8.0/en/regexp.html
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.
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.
HackerRank solutions.
check 'Mike Dame' sql tutors. I personally think he has the best sql tutorials.
Elegantly put. Glad to have you here helping us understand.
what the use of ^[aeiou]? it means negation means string not started with 'aeiou', right.
Correct.
is city names that *start with vowels"
is city names that start with anything that's not a vowel
HackerRank solutions.
^[^aeiou] it says words that starts other than a,e,i,o,u
Thanks @RodneyShag!
even with just [^aeiou] statement I am able to see this strings in my result: Alanson Auburn why is it so?
Thanks for the explaination.
try this
What the use of seperate 'i'
FOR case-insensitivity we use 'i'.
for ignoring the case sensityvity we have used "i"
This works for me.
i too have same doubt plzz... explain
Putting it inside the brackets makes it match all characters EXCEPT the ones inside the bracket. WHAT ELSE REMAIN? putting it outside makes it match WITH city name STATRS WITH remaining characters i.e..,(consonants)
Select distinct city from station where city not regexp '^[...aeiou]';
we should remove these ... inside the brackets and it's working fine! :)
YES THE CODE IS RUNNING ACCURATELY THANKS FOR PROVIDING THE BEST SOLUTION BRO
one of the code that i used:
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE "[^a|e|i|o|u]%";
NOT WORKING BRO
Works with SQL Server
GOOD EXPLANATION!
Thanks a lot for share this information
^ indicates the beginning of the string. $ indicates the end of the string.
selects those which dont contain AEIOU in the begining. ^[^AEIOU]
MYSQL
your solution does not work
I did exactly same but it shows wrong answer. Don't know why
select distinct city from station where left(city, 1) not in ('a', 'e', 'i', 'o', 'u') order by city;
Just replace the vowels present in lowercase with upper case vowels
SELECT DISTINCT CITY FROM STATION WHERE LEFT(CITY, 1) NOT IN ('A', 'E', 'I', 'O', 'U');
i try other query like as: select distinct city from station where lower(left(city, 1)) not in ('a', 'e', 'i', 'o', 'u');
Certainly! The SQL query you provided is used to select distinct cities from the "station" table where the first letter of the city is not a vowel (i.e., not 'a', 'e', 'i', 'o', 'u'). Here's the query:
sql Copy code SELECT DISTINCT city FROM station WHERE LEFT(city, 1) NOT IN ('a', 'e', 'i', 'o', 'u'); This query will retrieve distinct city names from the "station" table where the first letter of each city does not belong to the specified set of vowels. Adjust the table and column names if they differ in your database schema.
To include all the city name starts with(b,c,--,z etc.,) other than letters inside the bracket(vowels).As simple as that :) , cheers!
thats because we still need to tell SQL to look for first character in the string. The first ^ in '^[^aeiou]' points to the first character in the string. the second ^ lets SQL know that we want to exclude these characters.
As per your answer, the city will not contain vowels at all. They have just asked: not starting with vowels.
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.
HackerRank solutions.
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]';'For oracle .....
SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
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!
Because indexing starts from 0. And in negative side it starts from -1.
quando usa posição '0', ele considera como 1.
This also works in Oracle: Select distinct city from station where substr(city,1,1) not in ('A','E','I','O','U');
WHY WE USE LOWER HERE? Can you plz explain.
The city name starts with the letter capital. To change upper case letters into lower case we use lower here because we are using lower case letters in 'not in' function in where clause. or we can use the below code without using lower by putting upper case letters in 'not in' SELECT DISTINCT CITY FROM STATION WHERE (SUBSTR(CITY,0,1)) NOT IN ('A','E','I','O','U');
cud u plz explain lower(substr(city,0,1));
Lower coverts what we get to the Lower Case. substr is used to get some part of the complete string. In this case we will get the (0 to 1) i.e Start from 0 and end at 1 index.
Example:- Illinois -> Will give result as 'i' if we use lower(substr(city,0,1));
I dont understand why it would not be upper?? No city would start with lower so why would we want lower in our results???
It must be LOWER(SUBSTR(CITY,1,1)) there must be 1,1 not 0,1 :)
For MySQL :-
SELECT DISTINCT(CITY) FROM STATION WHERE LEFT(CITY,1) NOT IN ('A','E','I','O','U');
dope answer ! thx
best
Thanks..!
indeed a nice one !! left and right string functions are rarealy used but so helpful
Hello,
May I ask if there is any difference if we add .* into the expression:
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!
Yes, that is the same since .* matches 0 or more of any character.
HackerRank solutions.
would the same query will execute for oracle?
Sorry I've never coded any Oracle.
SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,1,1)) NOT IN ('a','e','i','o','u');
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,.,*.
thanks! then how about the following? is it also valid for this case?
I think it is valid, but it's redundant to add .*
I see. Thanks!
or you could use where city not regexp '^[aeiou]'. This is less confusing
Agreed, yours is clearer. I'm going to leave my solution as the fancy way since it's good Regex operator practice.
IT IS NOT WORKING ON ORACLE
This solution works on MySQL only. Oracle requires different syntax.
HackerRank solutions.
For Oracle .....
SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
Oracle : SELECT distinct CITY FROM STATION WHERE NOT REGEXP_LIKE(city ,'^(A|E|I|O|U)');
Select distinct CITY from STATION where not CITY like '[aeiou]%';
MS SQL SERVER That wrkd... Thanks.
why like is not working ?
For Oracle .... SELECT DISTINCT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,0,1)) NOT IN ('a','e','i','o','u');
What does LOWER(SUBSTR(CITY,0,1)) mean? plz answer im new
SUBSTR (String, start, length) -> CITY is String & when we start index starts with 1 basically the length it depends u can select upto how much length u wanted to.
for ex: SUBSTR("LeaRn N Practice",3,5) -> so start at index 3 i.e., 'a' & continue till length of 5 i.e., (aRn N) is the o/p.
If u want all the charcaters of o/p to be in lower case use LOWER.
what's the use of ^
I answered this in an earlier comment
HackerRank solutions.
It is showing invalid relational operator
Hi. Make sure you are using MySQL and the code will work properly.
HackerRank solutions.
I DIDN'T UNDERSTAND WHAT YOU ARE EXPLAINING?? WHY WE ARE USING ^ SYMBOL TWICE?
This comment may answer your question
HackerRank solutions.
You can also use the NOT keyword.
Like so:
what is the difference between regexp '^[aeiou]' , regexp '[^aeiou]' and regexp '^[^aeiou]' ?
Hi. I updated my original comment to answer your question.
HackerRank solutions.
Thank you very much
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou]';
Can u please tell me whats wrong with my query
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[^aeiou]';
Can u please tell me whats the error in my query. Its giving me a runtime error
Msg 4145, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 9 An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'.
Hi, are you using MySQL? Your query is the same as the working solution.
HackerRank solutions.
what about this SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[aeiou]';
does the computational cost same as yours ?
Yes. Nice job.
HackerRank solutions.
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");
this is running in mysql
We can also do that by logical NOT ;)
It's working like you tell us, but is working fine with NOT like this way:
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[aeiou]';
select distinct city from station where city not regexp'^[aeiou]';
error: ORA-00920: invalid relational operator
select distinct city from station where substr(city,1,1) NOT IN('a','e','i','o','u');
That's cool.
I tried it this way SELECT DISTINCT CITY FROM STATION WHERE NOT REGEXP_LIKE(LOWER(CITY), '^[aeiou]');
Thanks, this reduces the code.
How About this :-
SELECT DISTINCT CITY FROM STATION WHERE !(REGEXP_LIKE(City, '^[aeiou]'));Great job!!! That works fine.
but why does this thing shows error on hackerrank even if it is right
what does it mean regexp
Not Working for MySQL
why u usre CITY REGEXP WHY NOT CITY LIKE
> THANK YOU
SELECT DISTINCT CITY FROM STATION WHERE LEFT(CITY,1) NOT IN ('A','I','E','O','U')
This is the same solution that I used