Select Concat Substring and Replace in MySQL

Question

I have a .sql file that needs all the extra characters removed and just the text left. So anything inside the " ". An example of the TEXT field in the column is

a:1:{i:0;s:9:"test word here";}

a:1:{i:0;s:11:"test words here too";}

So I would want the words. test word here. and test words here too. To be all left in the text field.

I originally went with something like this.

  UPDATE `questions`
  SET answer = REPLACE(REPLACE(REPLACE(REPLACE(answer, 'a:1', ''), 's:4', ''), 'i:0', ''), ',', '')

But then quickly realized the s:4 has s:5, s:16 etc. So that wouldn't work. My next attempt was to use concat and just remove a certain amount of characters starting with a:1. I was able to do a working SELECT. But not able to get a REPLACE to work. Below you can see the working SELECT.

SELECT CONCAT('tt',
          SUBSTRING(`answer`, -LOCATE('a:1', `answer`)+15)
   ) from `questions`;

Here is my attempt at getting a REPLACE to work with it. But I'm stuck. I'm open to any suggestions, in case I'm going in the complete wrong direction here anyways.

SELECT CONCAT(REPLACE('tt',
          SUBSTRING(`answer`, -LOCATE('a:1', `answer`)+15))
   ) from `questions`;

Gordon Linoff · Accepted Answer · 2015-06-13 23:34:58Z

2

How about substring_index()?

UPDATE `questions`
    SET answer = substring_index(substring_index(answer, '"', 2), '"', -1)

answered Jun 13, 2015 at 23:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

f00d Over a year ago

interesting. I never saw substring_index. That's fantastic. Thank you!

slaakso · Accepted Answer · 2015-06-13 21:52:23Z

1

You could do something like this:

select substr(with_end, 1, locate('"', with_end )-1) as 'str'
from (
  select substr(answer, locate('"', answer )+1) 'with_end'
  from questions
) as q

answered Jun 13, 2015 at 21:52

slaakso

9,1452 gold badges19 silver badges29 bronze badges

1 Comment

f00d Over a year ago

holy cow! That's awesome!. But now how would you do a replace for that to make it permanent? Or are you suggesting a SET and UPDATE questions?

Collectives™ on Stack Overflow

Select Concat Substring and Replace in MySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related