Reversing string in SQL

Question

I got an sql question. I have a table containing a column named title that store a string like this in all raws.

"Prenom - Nom (85)".

I would like to know if there is a way in sql to change that string like this :

"Nom - Prenom"

It means, I would like to reverse it and then delete the " (85).

Thank you in advance.

you want to delete the last four charachters? and also "-" would always be there? — Rohit Tiwari
– Rohit Tiwari, Commented Jan 31, 2014 at 11:38
delete it and then reverse the first string si as i get nom - prenom — Mamadou
– Mamadou, Commented Feb 28, 2014 at 16:51

Gordon Linoff · Accepted Answer · 2014-01-31 11:43:40Z

2

Yes:

select concat(substring_index(left(col, length(col) - instr(reverse(col), ' ')), ' - ', -1),
              ' - ',
              substring_index(col, ' - ', 1)
             )

This assume that you want something a bit more general than getting rid of the '(85)'; it removes the final word.

answered Jan 31, 2014 at 11:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Tom Mac · Accepted Answer · 2014-01-31 11:41:27Z

0

Ugly as hell and almost certainly won't work for all of your cases. But this works for the example you have posted:

select concat(
substring_index(substring_index("Prenom - Nom (85)"," (",1)," - ",-1),
" - ",
substring_index(substring_index("Prenom - Nom (85)"," (",1)," - ",1)
);

answered Jan 31, 2014 at 11:41

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

1 Comment

Mamadou Over a year ago

Am goint to test this and give feedback.

Collectives™ on Stack Overflow

Reversing string in SQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related