I want to select records in my table when it matches a row that ends with a particular value.
eg.
if 'oop' is found at the end of a particular record it select the record
Pls how can i go about it
thanks
2
-
the search string is not fixed, can be anythingOgugua Belonwu– Ogugua Belonwu2010-10-13 21:20:10 +00:00Commented Oct 13, 2010 at 21:20
-
i came up with this: select from table where substring('columnname', -(length('oop')))='oop'. will it do the same job? if it will which will u advise. thanksOgugua Belonwu– Ogugua Belonwu2010-10-13 21:55:30 +00:00Commented Oct 13, 2010 at 21:55
Add a comment
|
3 Answers
You can use LIKE:
SELECT *
FROM your_table
WHERE your_column LIKE '%oop'
Note that this query will result in a full scan so it might be slow if you have many rows.
1 Comment
Ogugua Belonwu
before i saw ur post, i came up with this: select from table where substring('columnname', -(length('oop')))='oop'. will it do the same job? if it will which will u advise. thanks
select * from yourtable where somevalue like '%oop'
3 Comments
Ogugua Belonwu
before i saw ur post, i came up with this: select from table where substring('columnname', -(length('oop')))='oop'. will it do the same job? if it will which will u advise. thanks
Alex
If you are OK with implementing your own solution in favor of a simple, concise answer suggested by several people in this thread, as well as taking the performance hit of multiple function calls, then yes, your answer is perfect.
Ogugua Belonwu
thanks alex, actually what prompted my statement is this line: 'Note that this query will result in a full scan so it might be slow if you have many rows.' from Mark Byers. What am looking at is the performance. Hope u will understand my point of view, thanks once again
SELECT *
FROM your_table
WHERE your_column REGEXP 'oop'
Regular Expression Queries can open up some pretty cool extra features that like can't touch.
