MySQL提取特定列中每个单词的第一个字母 [英] Mysql extract first letter of each word in a specific column

问题描述

我想在表中创建一个首字母缩写列.我想从名称"列中抓取每个单词的第一个字母，将其大写，然后将其全部串联到首字母缩写"列中.

I want to create an acronym column in a table. I want to be grab the first letter of each word from a 'name' column, capitalize it, then concatenate all into an 'acronym' column.

有什么简单的方法来获取首字母?

Any easy way to grab first letters?

推荐答案

这是一个改进的"功能，由于使用了正则表达式，因此仅可过滤所需的字符.

Here is an "improved" function, allowing to filter only wanted characters thanks to a regular expression.

• 函数initials完成实际工作，您必须指定正则表达式
• 功能acronym仅保留字母数字字符

• function initials does the actual job, you have to specify the regular expression
• function acronym does the job keeping Alpha-numeric characters only

(如有必要，在输出上使用upper，lower或ucase函数)

(Use upper, lower or ucase functions on the output if necessary) .

delimiter $$
drop function if exists `initials`$$
CREATE FUNCTION `initials`(str text, expr text) RETURNS text CHARSET utf8
begin
    declare result text default '';
    declare buffer text default '';
    declare i int default 1;
    if(str is null) then
        return null;
    end if;
    set buffer = trim(str);
    while i <= length(buffer) do
        if substr(buffer, i, 1) regexp expr then
            set result = concat( result, substr( buffer, i, 1 ));
            set i = i + 1;
            while i <= length( buffer ) and substr(buffer, i, 1) regexp expr do
                set i = i + 1;
            end while;
            while i <= length( buffer ) and substr(buffer, i, 1) not regexp expr do
                set i = i + 1;
            end while;
        else
            set i = i + 1;
        end if;
    end while;
    return result;
end$$

drop function if exists `acronym`$$
CREATE FUNCTION `acronym`(str text) RETURNS text CHARSET utf8
begin
    declare result text default '';
    set result = initials( str, '[[:alnum:]]' );
    return result;
end$$
delimiter ;

示例1:

select acronym('Come Again? That Cant Help!');

输出:

CATCH

CATCH

示例2:

select initials('Come Again? That Cant Help!', '[aeiou]');

输出:

oeAaaae

oeAaaae

这篇关于MySQL提取特定列中每个单词的第一个字母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！