iam new to sql and i would like to know how can I find the letter or symbol at the end of value in column called Name?
E.g if i would find something i will write select * from 'table' where 'Name' like '%es%' but it will find me all rows contains es
Lets say - lesl, pespe, mess... but how to write select which will select just values with 'es' At the end of word? ... using regex i will use es\Z..... thanks in advance!
-
Since you are new, you might want to learn some basics before you start doing real work. To that end, I've heard good things about the book, Teach Yourself SQL in Ten Minutes.Dan Bracuk– Dan Bracuk2013-03-10 15:47:51 +00:00Commented Mar 10, 2013 at 15:47
-
Thanks for your idea. i will find this book and will be better and better :)DRastislav– DRastislav2013-03-10 15:54:02 +00:00Commented Mar 10, 2013 at 15:54
Add a comment
|
6 Answers
You have to remove the last %, so it will only select words ending with es.
select * from table where Name like '%es'
2 Comments
DRastislav
Thanky for your answer. It was helpful :)
Melissa Barnett
Can you use the like keyword with more than one condition ?
You're currently matching on: ..where 'Name' like '%es%'.
Which equates to anything, then 'es' then anything else.
Removing the last % changes the where to
anything then 'es'.
in short.. you need ..where 'Name' like '%es'
Comments
The query ..where 'Name' like '%es' will find columns where name ends with "ES". But if we need to find column where name ends with either "E" or "S", the query would be
.. where 'Name' LIKE '%[ES]'
Comments
You can also use REGEX
select distinct city from station where city REGEXP '[aeiou]$';
Resources: To Know more about REGEXP
Comments
- if you want to find name start with something like 'test' use => select name from table where name like 'test%'.
- if you want to find name end with something like 'test' use => select name from table where name like '%test'.
- if you want to find name start with s and end with h use => select name from table where name like 's%'and name like '%h' or simply select name from table where name like 's%h'.
Comments
Try pattern [a-z]ed($) with regexp operator/function.
Pattern explanation:
[a-z] - match any letter
es - match es literally
($) - end of string (so make sure it's not followed by any letters)
Full query would be:
select * from test
where ingr regexp '[a-z]es($)'
