CODEWITHSUNDEEP
mysqlacronym
Sam Grant
Sam Grant

Reputation: 442

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?

Upvotes: 2

Views: 13421

Answers (8)

chris
chris

Reputation: 3193

Here is a REGEX solution (likely not possible when this question was originally asked):

SELECT REGEXP_REPLACE(
    REGEXP_REPLACE( 'My Big Fat String', '\\b(\\w)[^\\s]+', '\\1' ), 
    '\\s', 
    ''
); // MBFS
  • \\b matches a word boundary
  • (\\w) captures the first character
  • [^\\s]+ consumes the rest of the non-whitespace characters in the word
  • \\1 keeps only the captured character from each word

Then the outer REGEXP_REPLACE removes the whitespace from the result.

Upvotes: 0

user3601725
user3601725

Reputation: 515

This works for 3-letter terms:

Select concat(left(Term,1),left(substring_index(Term,' ',-2),1),left(substring_index(Term,' ',-1),1)) from Table

Here is the one for 4 word terms:

Select concat(left(Term,1),left(substring_index(Term,' ',-3),1),left(substring_index(Term,' ',-2),1),left(substring_index(Term,' ',-1),1)) from Table

Ok ok here is 5 words ;)

Select concat(left(Term,1),left(substring_index(Term,' ',-4),1),left(substring_index(Term,' ',-3),1),left(substring_index(Term,' ',-2),1),left(substring_index(Term,' ',-1),1)) from Table

Upvotes: 0

Bart Van Eynde
Bart Van Eynde

Reputation: 1

SELECT REGEXP_REPLACE( ' Bart Van Eynde', ' (.)[^ ]+', '\\1' ); -- 'BVE'
  • Before starting add a space before your string
  • ' (.)[^ ]+' = Start looking for space + something (and save it) + ignore the rest if it is not a space
  • '\\1' only write the 'something' back

in a query:

SELECT UPPER( REGEXP_REPLACE( CONCAT(' ', col1), ' (.)[^ ]+', '\\1' ) ) from table1;

Upvotes: 0

dsimer
dsimer

Reputation: 115

I know this is a little late to the game, but I wanted to offer up a non-function way of doing this for those of you creating a view or a .sql file for periodic use:

SELECT
    @spaces:= length(fi.FacilityName) - length(replace(fi.FacilityName,' ','')) as spaces,
    concat(left(fi.FacilityName,1),
        if(@spaces > 0, substring(fi.FacilityName,@pos:=locate(' ',fi.FacilityName)+1,1),''),
        if(@spaces > 1, substring(fi.FacilityName,@pos:=locate(' ',fi.FacilityName, @pos)+1,1),''),
        if(@spaces > 2, substring(fi.FacilityName,@pos:=locate(' ',fi.FacilityName,@pos)+1,1),''),
        if(@spaces > 3, substring(fi.FacilityName,@pos:=locate(' ',fi.FacilityName,@pos)+1,1),''),
        if(@spaces > 4, substring(fi.FacilityName,@pos:=locate(' ',fi.FacilityName,@pos)+1,1),'')) as initials
from facilityInfo fi

It's two steps, and you have to include a conditional substring() line for every word you anticipate being in the string, but that is just a copy, paste, and increment of the comparison value for @spaces. My requirements for doing this may be a little looser than some, however. Regardless, it works and causes no noticeable speed issues.

Upvotes: 1

Pascal Havelange
Pascal Havelange

Reputation: 96

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

Upvotes: 8

Lion
Lion

Reputation: 19027

String manipulation of this kind is not what SQL is designed for, unless you want to write a stored procedure or UDF for it.

SQL isn't really suited for string manipulation of this sort. You may do it somehow but why would you when better tools are available elsewhere? I went on a long search on Google to find such a query statement but I couldn't. Just use the following function to achieve what you want.

drop function if exists initials;
delimiter ||
create function initials(str text) returns text
begin
    declare result text default '';
    declare i int default 1;

    if(str is null) then
        return null;
    end if;

    set result = upper(substr(str, 1, 1));

    while(i <= length(str)) do
        if (substring(str, i, 1) = ' ')
        then
            set result = concat(result, upper(substr(str, i+1, 1)));
        end if;
       set i = i + 1;
    end while;

    return ucase(result);
end;
delimiter ;

Upvotes: 1

vadchen
vadchen

Reputation: 1462

Do you mean LEFT and UPPER?

Upvotes: 0

Ted Hopp
Ted Hopp

Reputation: 234807

This should get all the first letters into a result set:

SELECT UPPER(SUBSTR(name, 0, 1)) FROM the_table

Concatenating them all into a single acronym would, I think, require a procedure of some kind. I don't think it can be done in a statement.

Upvotes: 0

Related Questions