0

I have a table with lot of rows. how can i extract a string from all rows that between specific strings. for example i have this text: "stackoverflow". the strings "stack" and "flow" is common in all rows; how can i extract string between in this tow String and copy to a new column? what i try is:

UPDATE word set m = (SELECT m FROM word WHERE m BETWEEN 'stack' and 'flow') ;

Update: consider this text:"I like stackoverflow services." the words "stack" and "flow" is common in all rows and I want string between them.

Improve this question
2
  • You can ONLY use nested queries when they return a single value, hence this won't work. You should instead either use w3schools.com/sql/sql_insert_into_select.asp or JOIN Commented Nov 23, 2017 at 8:09
  • You can use substr(x,y) to get the word in between, where m like 'stack%' and m like '%flow' Commented Nov 23, 2017 at 8:17

3 Answers 3

Reset to default
1

you can try this

//tested string 'here is something like that'
update word set m=(select substr(m,instr(m,'is'),instr(m,'like')-1) from word);
//ouput will be 'is something like'

this is pure mathematics using string function of mysql

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

Comments

0

Use substr to get the word start from 6th character (stack) and then how many character you want to take. The length - 9 is mean total word length deduct length of 'stack' and 'flow'

UPDATE word set m = (substr(m, 6, length(m)-9)) where m like 'stack%' and m like '%flow'

Improve this answer

4 Comments

thanks u but the length of string in rows is different.
@HadiAhmadi uh then what kind of string you expected?
for example my text in row is "I like stackoverflow !.....". the words "stack" and "flow" is common in all rows and i want string between them.
@HadiAhmadi sorry for late reply, but i think answer from AK47 is much more easier
0

You can also use 

UPDATE word 
set m = replace(replace(m,'stack',''),'flow','')
where m like 'stack%' and m like '%flow';
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.