MySQL Script Help

[Shuxclams]

I have a column I want to convert to firstname_lastname

Jones, John shoudl be John_Jones

What do I want to use.








Shux

 

[kamper]

alter table foo add firstname_lastname varchar(30)

update foo set firstname_lastname = firstname + '_' + lastname

I'm sure you can combine that into one statement somehow but I'm not going to wager a guess how to do it with mysql. I question why you want to do this though. Throwing away data is rarely a good thing and you can easily make the firstname_lastname a calculated field (select firstname + '_' + lastname as firstname_lastname from foo) either in each of your selects or with a view.

 

[Shuxclams]

Its a single column, I wish it were two....





IS that still right?






SHUX

 

[Barnaby W. Füi]

Your question is vague. What is the layout of this table and the data in it? What *exactly* do you want to do?

 

[Armitage]

Step back a bit and take a closer look at how your table is organized.
I suggest that first name & last name should be separate columns, and any concatenation or other string manipulation should be done on the application side.

 

[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.

 

[statik213]

Originally posted by: Shuxclams
I have a column I want to convert to firstname_lastname

Jones, John shoudl be John_Jones

What do I want to use.


Shux

Do this:

alter table foo add firstname varchar(30);
alter table foo add lastname varchar(30);

update foo
set firstname = SUBSTRING(firstname_lastname,0,LOCATE(firstname_lastname ' ') + 1,
set lastname = SUBSTRING(firstname_lastname,LOCATE(firstname_lastname ' '), LENGTH(firstname_lastname) - LOCATE(firstname_lastname ' ') - 1)
;


Try this, look at the updated data and if it looks fine drop the firstname_lastname column. My offsets above (+/-1) maybe off.... also, if you're name's are seperated w/ more than one space this won't work....
actually it should just wrap a trim(...) to both SUBSTRING(....) above....

 

[statik213]

also, always have fnames and lnames in seperate columns....
and when u query do SELECT CONCAT(fname, ' ', lname) or CONCAT(lname, ', ' fname)....

 

[kamper]

Oh. I totally read your post the other way around

If the data in your current column is very uniform (always "Fname, Lname" with no funky characters) then the suggestions above will be fine. If it gets any trickier I would move the string splitting into application space where you have more powerful string manipulation. Just do a select on all the names and loop through and do updates.

Off topic, there's another reason why postgres is cooler than mysql: half decent regex support so you could easily do this using sql.

 

[Armitage]

Originally posted by: kamper
Oh. I totally read your post the other way around

If the data in your current column is very uniform (always "Fname, Lname" with no funky characters) then the suggestions above will be fine. If it gets any trickier I would move the string splitting into application space where you have more powerful string manipulation. Just do a select on all the names and loop through and do updates.

Off topic, there's another reason why postgres is cooler than mysql: half decent regex support so you could easily do this using sql.

:roll: What's wrong with the regex support in mysql?

 

[statik213]

You can use regex to match strings but not to update them.... there's no regex replace....

i think....

 

[kamper]

Originally posted by: Armitage

Originally posted by: kamper
Oh. I totally read your post the other way around

If the data in your current column is very uniform (always "Fname, Lname" with no funky characters) then the suggestions above will be fine. If it gets any trickier I would move the string splitting into application space where you have more powerful string manipulation. Just do a select on all the names and loop through and do updates.

Off topic, there's another reason why postgres is cooler than mysql: half decent regex support so you could easily do this using sql.

:roll: What's wrong with the regex support in mysql?

Uh oh. I might have just been caught talking out of my ass

When I read about the postgres regex support I was pretty impressed and I had never seen anything like that at the database level before (much better than standart sql like syntax). I admit that, although I haven't seen such support from mysql, I also don't know that there isn't any (I was assuming...).

Please educate me to the contrary if I am mistaken and we can hopefullly find a nice solution for shux at the same time.

 

[kamper]

Would you look at that? Kamper was wrong (it's been known to happen before... )

And from postgres: http://www.postgresql.org/docs/8.0/interactive/functions-matching.html For the record, I still think postgres' is better (what with the posix compliance and such) but I'm still learning both so...

What I don't see from mysql, which is key here, as statik mentioned, is the notion of capturing groups. For shux's thing we need to be able to put brackets around the first and last names and have the matching pattern returned for insertion into the correct columns.

 

[statik213]

Yup can't do that in MySQL -- only in the WHERE clause.

expr REGEXP pat
expr RLIKE pat
Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. See section G MySQL Regular Expressions. Returns 1 if expr matches pat, otherwise returns 0. RLIKE is a synonym for REGEXP, provided for mSQL compatibility. Note: Because MySQL uses the C escape syntax in strings (for example, `\n'), you must double any `\' that you use in your REGEXP strings. As of MySQL Version 3.23.4, REGEXP is case-insensitive for normal (not binary) strings:

mysql> SELECT 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> SELECT 'Monty!' REGEXP '.*';
-> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
mysql> SELECT "a" REGEXP "A", "a" REGEXP BINARY "A";
-> 1 0
mysql> SELECT "a" REGEXP "^[a-d]";
-> 1

But you can't really use it in updates....

Maybe do something to check if there are rows that are not in `fname lname` format and edit them selectively? But if it's a huge table that would be a pain in the neck....

But even if you did have sophisticated regex support how would you disambiguate
Mary Kate Olsena and Rebecca Romjin Stamos? (If you consider Mary Kate to be one name).....

 

[kamper]

A rough stab at this in postgres (no idea if it'd work, but it's the idea):

update foo
set fname = substring(fullname, '([^,]+).*')

Of course that only handles the simple "lname, fname" format. For something like "Mary Kate Olsen" you pretty much have to shoot in the dark. Maybe assume that the last token is the only thing for the last name. For complex data you inevitably need some human intervention. I'm hoping he's got nicely structured data within that field

 

[statik213]

doesn't that seem like overkill?
you can just as easily handle it w/ subtring() and locate() AKA find() like I posted above.,...

yeah running either method on a large table is going to get mess up some rows....

 

[statik213]

OK i got the format wrong so my substring stuff would not work as above... i was thinking 'fname lname', but this would work:
update foo
set firstname = SUBSTRING(name,LOCATE(name ','), LENGTH(name) - LOCATE(name,',')) ,
set lastname = SUBSTRING(name,0,LOCATE(name ','))
;

 

[kamper]

You are correct, I hadn't fully read your above post. If your solution matches all the funky cases he has then it's perfect. I just like that you can do it all using regexes in postgres, as opposed to inlining all these string manipulation functions.

 

[Shuxclams]

I would gladly redo the data ---- only I dont have control how the date comes into our system as we grab it offsite and import it. I am looking into a VB Script and a Excel macro to format the date before upload, but it 'seems' that it would be a much easier transform as a SQL script.


I am going to test a couple of these and see what works...

Thanks







SHUX

 

[Shuxclams]

Here is the table that I have. The column is 'firstnamelast' and that is the one I want to explode and reassemble in 'username'.











SHUX

 

[statik213]

Yeah there probably isn't much you do w/ a vb macro or excel in terms of string manipulation that you can't do w/ mysql.

but the .net framework has some good regex support.... so you might want to see if you can get at any of those libraries from u vb macro.

 

[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

 

[Shuxclams]

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

Well this just turned firstnamelast into '0'....

Going to try a different route..





SHUX

 

[Shuxclams]

Grrr ---


See attached script...

I get;

1193 - Unknown system variable 'lastnamefirst'




SHUX

 

[statik213]

don't u mean explode lastnamefirst to username?


update s2j2_sync_exception
set username=
CONCAT(

SUBSTRING(lastnamefirst,LOCATE(lastnamefirst, ','), LENGTH(lastnamefirst) - LOCATE(lastnamefirst, ',') - 1),
',',
SUBSTRING(lastnamefirst,0,LOCATE(lastnamefirst, ',')

);