Burton Guster Burton Guster -4 years ago 77
MySQL Question

Taking only characters from the left and right of a specific character in mySQL

I have a list table with where one of the variables is Player and if has a players first name then an "_" and there last name like this:

Mike_Gonzalez


I would like to create two new variables from the player variables. The first variable would be firstName, so I would want all the characters to the left of the "". The second variable would be lastName, and it would be all the characters to the right of the "".

I've tried using LEFT(Player, LOCATE('_', Player)), but when I do, the new variable includes the _ .

How can I run the code where I would be able to jus get the first and last names without the _ ?

Thanks for any help.

Answer Source

Besides combining LEFT() and RIGHT() with LOCATE(), you can also use SUBSTRING_INDEX():

SELECT
    SUBSTRING_INDEX(Player, '_', 1) AS FirstName
  , SUBSTRING_INDEX(Player, '_', -1) AS LastName
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download