我有一个表格字段membername
,其中包含用户的名字和姓氏。是否可以将它们拆分成两个字段memberfirst
和memberlast
?
所有记录都具有此格式“名字 姓氏”(不带引号且中间有一个空格)。
当数据全部放在名字字段中时,我使用的方法是将first_name拆分为first_name和last_name。这将只在last name字段中放置最后一个单词,因此"john phillips sousa"将成为"john phillips"的名字和"sousa"的姓氏。它还避免覆盖已经修复过的任何记录。
set last_name=trim(SUBSTRING_INDEX(first_name, ' ', -1)), first_name=trim(SUBSTRING(first_name,1,length(first_name) - length(SUBSTRING_INDEX(first_name, ' ', -1)))) where list_id='$List_ID' and length(first_name)>0 and length(trim(last_name))=0
如果有人需要遍历表并拆分字段:
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_str`($str VARCHAR(800), $delimiter VARCHAR(12), $position INT) RETURNS varchar(800) CHARSET utf8
DETERMINISTIC
BEGIN
RETURN REPLACE(
SUBSTRING(
SUBSTRING_INDEX($str, $delimiter, $position),
LENGTH(
SUBSTRING_INDEX($str, $delimiter, $position -1)
) + 1
),
$delimiter, '');
END
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_str_to_rows`($id INT, $str VARCHAR(800), $delimiter VARCHAR(12), $empty_table BIT) RETURNS int(11)
BEGIN
DECLARE position INT;
DECLARE val VARCHAR(800);
SET position = 1;
IF $empty_table THEN
DROP TEMPORARY TABLE IF EXISTS tmp_rows;
END IF;
SET val = fn_split_str($str, ',', position);
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_rows AS (SELECT $id as id, val as val where 1 = 2);
WHILE (val IS NOT NULL and val != '') DO
INSERT INTO tmp_rows
SELECT $id, val;
SET position = position + 1;
SET val = fn_split_str($str, ',', position);
END WHILE;
RETURN position - 1;
END
DROP TEMPORARY TABLE IF EXISTS tmp_rows;
SELECT SUM(fn_split_str_to_rows(ID, FieldToSplit, ',', 0))
FROM MyTable;
SELECT * FROM tmp_rows;
您可以使用ID与其他表进行连接。
如果您只拆分一个值,可以像这样使用它
SELECT fn_split_str_to_rows(null, 'AAA,BBB,CCC,DDD,EEE,FFF,GGG', ',', 1);
SELECT * FROM tmp_rows;
UPDATE `salary_generation_tbl` SET
`modified_by` = IF(
LOCATE('$', `other_salary_string`) > 0,
SUBSTRING(`other_salary_string`, 1, LOCATE('$', `other_salary_string`) - 1),
`other_salary_string`
),
`other_salary` = IF(
LOCATE('$', `other_salary_string`) > 0,
SUBSTRING(`other_salary_string`, LOCATE('$', `other_salary_string`) + 1),
NULL
);
MySQL 5.4 提供了本地的分割函数:
SPLIT_STR(<column>, '<delimiter>', <index>)