substr with REPLACE

[SFDonovan]

I have a field called StudentID varchar(9) which is a University ID (UID) used on campus. The students enter this on the scantron when they take a test. It is highly likely they will enter the wrong number or maybe only the last 4 of their UID. The scantron is fed into a machine that records the test and spits out a grade.

The format of the csv that I import looks like this.

1,000077193 ,"ABOUSOUD, OMAR ",9,90,35,9,90
1,000390918 ,"ABRAHAM, HEIDI ",9,90,35,9,90
1,000385334 ,"ADAMSHARRISON, ANGELA ",8,80,14,8,80
1,000545516 ,"AGIN, ANNIE ",8,80,14,8,80
1,000388419 ,"AMPONSAH, EUGEN ",10,100,75,10,100

The 9 character number is the StudentID or UID. Sometimes I will see a StudentID in this file as

xxxx75481

The lazy student didn't bother putting his whole UID on the scantron and the machine they feed this into places xxx in place of what I assume to be leading zeros.

The accurate UID's stored in my students table looks like this

U00025856
U00020784
U00518132
etc

*Note all of them have a leading capital U followed by 8 characters.

In the imported table from the scantron I need to trim leading x's from the StudentID and replace with zeros, and replace the first character with a U.

Is the following accurate, or am I even in the ballpark?

$alteruid = $db->sql_query("SELECT StudentID FROM ".$prefix."$table_name");
       while ($check_UID = $db->sql_fetchrow($alteruid)) {
       $new_UID = substr(REPLACE(x '0'($check_UID['StudentID'], -8)));

I still have to append with a capital U.

I didn't think cleansing these scantron records would be so difficult. :glare:

[SFDonovan]

Or could I separate the two.

$replacexuid = $db->sql_query("UPDATE ".$prefix."$table_name SET StudentID = replace([StudentID],'[x]','[0]')");


$alteruid = $db->sql_query("SELECT StudentID FROM ".$prefix."$table_name");
       while ($check_UID = $db->sql_fetchrow($alteruid)) {
       $new_UID = substr($check_UID['StudentID'], -8);

I just tried and it still is not replacing the x values, but the substr is working.

[SFDonovan]

Still need help with this.

I'm trying to update these records to cleanse them from a scantron. I needed to remove any leading x from the StudentID which I did with this.

$replacexuid = $db->sql_query("UPDATE ".$prefix."$table_name SET StudentID = REPLACE(StudentID,'x','0')"); 

Then I need to strip off the first character in a 9 character field and replace with a U

$i = "U";
$alteruid = $db->sql_query("SELECT StudentID FROM ".$prefix."$table_name");
while ($row = $db->sql_fetchrow($alteruid)) { 
$clean_UID = substr($row['StudentID'], -8);
	   $new_UID = $i.$clean_UID;
$db->sql_query("UPDATE ".$prefix."$table_name SET StudentID = '$new_UID'");
  }

The code is currently updating the very last record out of 104 from the query and updating all student grades with that UID. This is incorrect as each student has their own StudentID.

I missing something that seems pretty easy.

[bradgrafelman]

A query like this might work:

UPDATE myTable
SET StudentID = CONCAT('U', REPLACE(SUBSTRING(StudentID, 2, 9), 'x', '0'))
WHERE StudentID LIKE '%x%'

That should find all StudentID's that have an 'x' in them and replace it with U + (2nd through 9th characters, with x's replaced with 0's).