TRIM leading " from varchar field

Client Joined: Oct 07, 2003 Posts: 735 Location: Ohio

I tried this code to help me trim a " from a Last_Name field. It is not working.

Code:




$stripname = $db->sql_query("UPDATE ".$prefix."$table_name SET Name_Last = TRIM(LEADING '\"' FROM 'Name_Last'");

I also tried a ltrim but could not get that working either.

The field looks like this.

Quote:

"ABRAHAM
"BAXTER
"COOPER

The Name_First looks like this

Quote:

COREY "
ROBYN A"
NICHOL W"

Yes you can imagine what the field looked like from the csv file

Quote:

"COOPER, ROBYN A"

I split it on the comma during the import

Code:




FIELDS TERMINATED BY ','

But now I can't get the " stripped off the name fields.

Any help is appreciated.

registered · Posted: Fri Dec 14, 2007 3:38 pm

Trim is designed to remove spaces. You'll need to use the function MID to return a valid substring of the entire string. http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

During import, can't you also set a "fields surrounded by" parameter? I thought there was something like that so it would automatically take out quotes

Posted: Fri Dec 14, 2007 4:50 pm

There is a fields enclosed by but the data looked like "Smith, Joe E" and I wanted to break the last name and first name apart.

registered · Posted: Fri Dec 14, 2007 7:01 pm

This should get you started....(warning not tested)

Code:




$line = '"Smith, Joe"';


$s = preg_replace('/"(\w+),\s+(\w+)"/', '$1,$2', $line);


list($last, $first) = explode(',', $s);

The preg_replace strips off the leading and trailing double quotes, leaving you with last name comma first name. Then you use an explode to split that into two parts based on the comma.

You'll have to beef up the regular expression if there is an optional middle initial or something.

References:

http://us.php.net/manual/en/function.preg-replace.php
http://us.php.net/manual/en/reference.pcre.pattern.syntax.php

Posted: Mon Dec 17, 2007 2:34 pm

I am trying to write these valued back to the table but can't seem to get it functioning correctly.

Code:

//Strip the " from imported students name.


$altertable = $db->sql_query("ALTER TABLE ".$prefix."$table_name ADD Name_First varchar(30) NULL AFTER ImportedName");


$altertable = $db->sql_query("ALTER TABLE ".$prefix."$table_name ADD Name_Last varchar(50) NULL AFTER Name_First");


$sql = $db->sql_query("SELECT * FROM ".$prefix."$table_name WHERE ImportedName IS NOT NULL");


while ($row = $db->sql_fetchrow($sql)) {            


      $Name = $row['ImportedName'];


$s = preg_replace('/"(\w+),\s+(\w+)"/', '$1,$2', $Name);


list($Fixed_Name_Last, $Fixed_Name_First) = explode(',', $s);


}


$fixname = $db->sql_query("UPDATE ".$prefix."$table_name SET Name_Last = '$Fixed_Name_Last', Name_First = '$Fixed_Name_First' WHERE Session_ID ='$Session_ID'");

...it is writing the last value in the query to all records.

For First_Name I have ELI
For Last_Name I have ZARKHIN

these are written for every record.

I figured if I had this

Code:

WHERE Session_ID ='$Session_ID'

it would write each corrosponding values.

Posted: Mon Dec 17, 2007 8:59 pm

Sorry it is late and I don't have the energy to simulate this. In that last query are you sure that '$Fixed_Name_Last' is not being interpreted literally and that you don't need to escape the ' mark with a \ ? I usually prefer the two step method of putting the SQL statement into a variable (e.g., $sql = ("UPDATE ".$prefix."$table_name SET Name_Last = '$Fixed_Name_Last', Name_First = '$Fixed_Name_First' WHERE Session_ID ='$Session_ID'") and then executing that separately. That way for diagnostic purposes I can echo out the SQL I've created and see exactly how it is being interpreted. I'd also put in a check that says if (!fixname) and then echo out any mysql errors.

Practice defensive programming from the outset and check for errors as you go, echo things to make sure they are coming out the way you expect and you will find that things go a lot smoother.