3

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

Bujji
  • 1,717
  • 10
  • 41
  • 66
  • 2
    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 Apr 25 '11 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. http://stackoverflow.com/questions/4380692/find-the-position-of-a-regex-substring-in-mysql – Compeek Apr 25 '11 at 20:59

2 Answers2

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.

Nicola Cossu
  • 54,599
  • 15
  • 92
  • 98
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.

Femi
  • 64,273
  • 8
  • 118
  • 148
  • 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. – Femi Apr 25 '11 at 21:10
  • Thanks Femi for your quick response . I am able to get the results as expected with nick rulez query .. Once again thanks – Bujji Apr 25 '11 at 22:01