Menu
I have a table with a text field that contains one or more words, separated by spaces. I want to isolate the last word in that text field. For example if the table contained:
|col1 |
+-----+
|a |
|b c |
|d e f|
I want a query that will return:
|result|
+------+
|a |
|c |
|f |
Thanks in advance!
Barry
P.S. I am running MySQL 5.1
204
Late answer, posted only as guidance. The simple fix is to add a "fail-safe" in the charindex() by adding a space to the string, thus ensuring a hit.
SQL Server CHARINDEX() Function The CHARINDEX() function searches for a substring in a string, and returns the position. If the substring is not found, this function returns 0. Note: This function performs a case-insensitive search.
We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table. And then we can select the entry which we want to retrieve.
SELECT SUBSTRING_INDEX(yourColumnName,' ',1) as anyVariableName from yourTableName; In the above query, if you use -1 in place of 1 then you will get the last word.
SELECT SUBSTRING_INDEX(TRIM(col1), ' ', -1) FROM table;
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
148
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
