問題描述

我有一個(gè)表字段 membername，其中包含用戶的姓氏和名字.是否可以將它們分成 2 個(gè)字段 memberfirst、memberlast?

I've got a table field membername which contains both the last name and the first name of users. Is it possible to split those into 2 fields memberfirst, memberlast?

所有記錄的格式都是Firstname Lastname"(沒有引號(hào)，中間有空格).

All the records have this format "Firstname Lastname" (without quotes and a space in between).

推薦答案

遺憾的是 MySQL 沒有拆分字符串功能.但是，您可以為此創(chuàng)建一個(gè)用戶定義的函數(shù)，如下文所述:

Unfortunately MySQL does not feature a split string function. However you can create a user defined function for this, such as the one described in the following article:

• MySQL 拆分字符串函數(shù) by Federico Cargnelutti

• MySQL Split String Function by Federico Cargnelutti

使用該功能:

DELIMITER $$

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$

DELIMITER ;

您可以按如下方式構(gòu)建查詢:

you would be able to build your query as follows:

SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
       SPLIT_STR(membername, ' ', 2) as memberlast
FROM   users;

如果您不想使用用戶定義的函數(shù)并且不介意查詢更冗長，您還可以執(zhí)行以下操作:

If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM   users;

這篇關(guān)于將值從一個(gè)字段拆分為兩個(gè)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！