SUBSTR twice in MySQL statement;

Question

So my responses are of the pattern.. "Congratulations! You have won a Panasonic Sound System in the *. Our call centre will contact you on ***." What I need is to select only the prize (in this case Panasonic Sound System" ) as the output. The characters for the different prizes differ. Others have many characters and only 10 characters. I need to run a select statement that removes both the leading "Congratulations! You have won a " and the trailing "in the **. Our call centre will contact you on ****." and therefore returns the prize; Lets call my table entries, my field which has this text we call it response; I've run SELECT SUBSTR(response,32) from entries; and I remove the leading characters before the prize.

when i run this i get "Panasonic Sound System in the *. Our call centre will contact you on ****.";

The character length of LEADING is 32 and TRAILING is 94. The prize is not constant.

I'm not sure I understand your problem. – Nir Alfasi Aug 23 '12 at 09:30 — Nir Alfasi, Aug 23 '12 at 09:30

score 0 · Accepted Answer · answered Aug 23 '12 at 09:32

0

SUBSTRING_INDEX might be useful here:

select 
substring_index(
substring_index(
"Congratulations! You have won a Panasonic Sound System in the *. Our call centre will contact you on ***."," a ",-1)," in the ",1);

answered Aug 23 '12 at 09:32

Tom Mac

9,693
3
25
35

Thanks alot both of you for your help. Tried Jon's suggestion but had errors so i decided to try Tom Mac's. Worked at first try. Muchas Gracias Senor!! – Aug 23 '12 at 10:59

jon · Answer 2 · 2012-09-01T15:48:25.827

If you are confident that your trailing and leading character counts are always going to be constant, you make use of substr's usage case: SUBSTR(str,pos,len).

len can be determined by subtracting the leading unwanted chars and trailing unwanted chars from the length of the original string.

set @test_string = "Congratulations! You have won a Panasonic Sound System in the ************************. Our call centre will contact you on *************************";
set @leading = 32;
set @trailing = 94;
select substr(@test_string, @leading, length(@test_string) - @leading - @trailing);

Eg:

mysql> select substr(@test_string, @trailing_chars, length(@test_string) - @leading_chars - @trailing_chars);
+------------------------------------------------------------------------------------------------+
| substr(@test_string, @trailing_chars, length(@test_string) - @leading_chars - @trailing_chars) |
+------------------------------------------------------------------------------------------------+
|  Panasonic Sound System                                                                        |
+------------------------------------------------------------------------------------------------+

SUBSTR twice in MySQL statement;

2 Answers2