Sam Grant Sam Grant - 1 year ago 36
MySQL Question

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?

Answer Source

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

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

(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 ;

Example1:

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

Outputs:

CATCH

Example2:

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

Outputs:

oeAaaae