Re: How can I convert a string containing a few names into its initials?

[Felix Geerinckx]

Gert van de Pol wrote:

> But isn't there a solution when using mysql functions only?
> When transferring data from one table to another,
> at the same time I would like to convert the full names into the initials.
> Is there a way to do this?

USE test;
DROP TABLE IF EXISTS names;
CREATE TABLE names (
name CHAR(50) NOT NULL
);

INSERT INTO names VALUES
("James Henry William"), ("John Paul"), ("Gert");

DROP TABLE IF EXISTS ints;
CREATE TEMPORARY TABLE ints (
id INT NOT NULL PRIMARY KEY
);
INSERT INTO ints VALUES (1), (2), (3), (4), (5);


SELECT
names.name,
CONCAT(
GROUP_CONCAT(
SUBSTRING(
REVERSE(
SUBSTRING_INDEX(
SUBSTRING_INDEX(
REVERSE(names.name), ' ', ints.id
), ' ', -1
)
), 1,1
) ORDER BY ints.id DESC SEPARATOR '.'
), '.'
) AS Initials
FROM names, ints
WHERE
ints.id <= CAST((LENGTH(names.name) - LENGTH(REPLACE(names.name, ' ', ''))) / LENGTH(' ') AS UNSIGNED) + 1
GROUP BY names.name;

--
felix
Please use BBCode to format your messages in this forum.