Mysql SQL query to get Last word in text

Question

I have a text similar to the below in my database

this is my car
how are you,doing
how is your)health
this is not(working

when I query database I should get the last words from this text. In this example

1st row should return "car"
2nd row should return "doing"
3rd row should return "health"
4th row should return "working"

any idea on how to get this?

Thanks for your help

Regards

Kiran

There is substring_index() function but in your case it doesn't work cause you have always different chars (spaces,commas, parentheses and so on). I think you need some regexp or you have to use a lot of nested replace to remove those chars before using that function. — Nicola Cossu
– Nicola Cossu, Commented Apr 25, 2011 at 20:56
I think you'd be better off just fetching each whole row and using RegEx (or whatever) to get the last word in your code. stackoverflow.com/questions/4380692/… — Compeek
– Compeek, Commented Apr 25, 2011 at 20:59

Nicola Cossu · Accepted Answer · 2011-04-25 21:05:37Z

5

This is an example:

create table lastword (
id int not null auto_increment primary key,
mytext varchar(250)
) engine = myisam;

insert into lastword (mytext) values 
('this is my car'),
('how are you,doing'),
('how is your)health'),
('this is not(working');

select *,
substring_index(replace(replace(replace(mytext,',',' '),')',' '),'(',' '),' ',-1) as last 
from lastword

+----+---------------------+---------+
| id | mytext              | last    |
+----+---------------------+---------+
|  1 | this is my car      | car     |
|  2 | how are you,doing   | doing   |
|  3 | how is your)health  | health  |
|  4 | this is not(working | working |
+----+---------------------+---------+
4 rows in set (0.00 sec)

As you can see you would have to use a lot of nested replaces to accomplish your task.

answered Apr 25, 2011 at 21:05

Nicola Cossu

56.7k15 gold badges96 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Femi · Accepted Answer · 2011-04-25 21:01:59Z

3

If the table is text, with columns ID and VALUE

select ID, RIGHT(VALUE, LOCATE(' ', REVERSE(VALUE))) FROM text;

The performance will probably suck if you end up with a couple of large text fields. Might want to rethink that approach.

answered Apr 25, 2011 at 21:01

Femi

64.8k8 gold badges120 silver badges150 bronze badges

2 Comments

Femi Over a year ago

Ah, didn't notice you had multiple delimiters: my query will only work if you normalize that. Either replace all delimiters with a fixed value or (what's probably the best option) add a column to store the last value and put it in there when you insert the row.

Bujji Over a year ago

Thanks Femi for your quick response . I am able to get the results as expected with nick rulez query .. Once again thanks

Collectives™ on Stack Overflow

Mysql SQL query to get Last word in text

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related