MySQL Script Help

[statik213]

dang, MySQL indexes from 1 instead of 0 on strings....
ok, this works... look at the transcripts

update s2j2_sync_exception
set username=
CONCAT(
TRIM(SUBSTRING(lastnamefirst,LOCATE(',' , lastnamefirst) + 1, LENGTH(lastnamefirst) - LOCATE(',' , lastnamefirst) )),
"_",
TRIM(SUBSTRING(lastnamefirst,1, LOCATE(',' , lastnamefirst) - 1))

);

mysql> select username, lastnamefirst from s2j2_sync_exception ;
+-------------------+--------------------+
| username | lastnamefirst |
+-------------------+--------------------+
| Jon_Andreas | Andreas, Jon |
| James_Arquitola | Arquitola, James |
| Scott_Brosious | Brosious, Scott |
| Kris_Burlingame | Burlingame, Kris |
| Joseph_Dahl | Dahl, Joseph |
| Bruce_Dichter | Dichter, Bruce |
| Judy_Henry | Henry, Judy |
| Jessie_Laraux | Laraux, Jessie |
| Cathlina_Martinez | Martinez, Cathlina |
| Ernest_Medeiros | Medeiros, Ernest |
+-------------------+--------------------+
10 rows in set (0.00 sec)

 

[Shuxclams]

Originally posted by: statik213
dang, MySQL indexes from 1 instead of 0 on strings....
ok, this works... look at the transcripts

update s2j2_sync_exception
set username=
CONCAT(
TRIM(SUBSTRING(lastnamefirst,LOCATE(',' , lastnamefirst) + 1, LENGTH(lastnamefirst) - LOCATE(',' , lastnamefirst) )),
"_",
TRIM(SUBSTRING(lastnamefirst,1, LOCATE(',' , lastnamefirst) - 1))

);

mysql> select username, lastnamefirst from s2j2_sync_exception ;
+-------------------+--------------------+
| username | lastnamefirst |
+-------------------+--------------------+
| Jon_Andreas | Andreas, Jon |
| James_Arquitola | Arquitola, James |
| Scott_Brosious | Brosious, Scott |
| Kris_Burlingame | Burlingame, Kris |
| Joseph_Dahl | Dahl, Joseph |
| Bruce_Dichter | Dichter, Bruce |
| Judy_Henry | Henry, Judy |
| Jessie_Laraux | Laraux, Jessie |
| Cathlina_Martinez | Martinez, Cathlina |
| Ernest_Medeiros | Medeiros, Ernest |
+-------------------+--------------------+
10 rows in set (0.00 sec)

Excellent help! That is exactly what I was looking for.....


Thanks, now I am of to passing a gigantic array through PEAR.... yay!







SHUX

 

[Jeraden]

Originally posted by: Shuxclams

Originally posted by: Jeraden
assuming its always in the lastname, first name and the column is col1


set col1 = ltrim(substr(col1,instr(col1,',')+1)) || '_' || substr(col1,1,instr(col1,',')-1)

thats oracle syntax, so use the appropriate mysql equivalents for those functions.

See attached code for the equiv.


The only thing I don't understand about your script is the OR ( || ), is that different in Oracle?




SHUX

|| is to concatenate strings in oracle