在MySQL中仅从特定字符的左侧和右侧获取字符 [英] Taking only characters from the left and right of a specific character in mySQL

问题描述

我有一个列表表，其中的一个变量是Player，如果有一个玩家的名字，则有一个"_"，那里的姓氏是这样的:

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

我想从玩家变量中创建两个新变量.第一个变量是firstName，所以我希望所有字符都在""的左侧.第二个变量是lastName，它是""右边的所有字符.

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

我尝试使用LEFT(Player，LOCATE('_'，Player))，但是当我这样做时，新变量包含_.

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 _ ?

感谢您的帮助.

推荐答案

除了将LEFT()和RIGHT()与LOCATE()结合在一起，您还可以使用

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

这篇关于在MySQL中仅从特定字符的左侧和右侧获取字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！