Search code examples
mysqlcharactersubstrtrailing

SUBSTR twice in MySQL statement;

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.

Solution

  • 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);