CODEWITHSUNDEEP
sqlsqlite
user5943823
user5943823

Reputation:

SQLite: how to find a subString between specific strings in all rows?

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.

Upvotes: 0

Views: 1400

Answers (3)

rudra
rudra

Reputation: 200

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

Upvotes: 1

AK47
AK47

Reputation: 58

You can also use 

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

Upvotes: 0

Lee
Lee

Reputation: 848

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'

Upvotes: 0

Related Questions