从Mysql中的一列中删除子字符串

Question

I want to delete a part of the substring from my table's column

For example, I have a structure like the following:

id | Col1                                      
____________________________________________________________
1  | The string I want and the string I don't   

And I need to update my table such that:

id | Col1                                       
____________________________________________________________
1  | The string I want 

I tried using the following code:

$var = "UPDATE mytable
  SET col1 = replace(col1,col1(SUBSTRING(col1,19),'')";

But this won't work!

Answer 1

Try this query to separate address:

UPDATE mytable as a 
INNER JOIN
    (SELECT 
        id,
        Address1, 
        LEFT(Address1, 40 -1) as newCol1,
        substring(Address1, 40) as newCol2
    FROM mytable 
    where length(Address1) > 40) b 
on a.id=b.id 
set Address1=newCol1,Address2=newCol2;

if there is any concern about above query please let me know.