Geek Answers Handbook

Separate columns with conditions

I have a table with some problem data. For example, a table looks like this:

ID   NAME                            JOB
---  ---------------------------     ---------------
1    Peter                           Teacher
2    John                            Programmer
3    Tom**He is a Teacher  
4    Alan**He is a Accountant

The problem is that some data was inserted correctly and some were not. Now I want to execute SQL so that the table looks like this:

ID   NAME                            JOB
---  ---------------------------     ---------------
1    Peter                           Teacher
2    John                            Programmer
3    Tom                             Teacher  
4    Alan                            Accountant

I am not familiar with SQL Statement, so I can just think about using the following PHP Script to fix this problem.

$sql1 = "SELECT NAME FROM MY_TABLE WHERE JOB = '' AND NAME LIKE '%He is a %'";
$res1 = mysql_query($sql1);
while($row1 = mysql_fetch_array($res1)){
    $new_data = explode("**He is a ", $row1["NAME"]);
    $sql2 = "UPDATE MY_TABLE SET NAME = '".$data[0]."', JOB = '".$data[1]."' WHERE ID = '".$data["ID"]."'";
    mysql_query($sql2);
}

Can someone suggest me a better way to fix this problem with one or more SQL statements? Thanks

+4
source share
2 answers

substring_index update:

UPDATE my_table
SET    JOB = SUBSTRING_INDEX (name, '**He is a ', -1),
       name = SUBSTRING_INDEX (name, '**He is a ', 1),
WHERE  name LIKE '%**He is a %' AND
       (job IS NULL OR job = '') -- Just to be on the safe side
+1
UPDATE 
SET    NAME     = SUBSTRING_INDEX(SUBSTRING_INDEX(NAME, '**He is a ', 1), ' ', -1),
       job      = SUBSTRING_INDEX(SUBSTRING_INDEX(NAME, '**He is a ', 2), ' ', -1)
WHERE  NAME LIKE '%**He is a %'
+2

More articles:


All Articles