I have a column with text separated by a hyphen, for example, hello-welcome-to-tutorials, I want to split the text and pick the first letters of each word.
So words are [hello, welcome, to, tutorials]
The first letters are hwtt. I want to come up with a select query in MySQL for this.
This is my query:
select GROUP_CONCAT(LEFT(SUBSTRING_INDEX(mycolumn, '-', -1), 1) SEPARATOR '') from mytable
By I am getting only the first letter for the last word that is t.
How to solve this?
MySQL version is 8.0.33
>Solution :
You can do this with REGEXP_REPLACE:
SELECT
REGEXP_REPLACE(CONCAT('-', mycolumn), '-([^-])[^-]*', '$1')
FROM mytable
This uses a regular expression matching:
-match a hyphen character (introduces a word([^-])match any non-hyphen character, and capture (the first letter)[^-]+match zero or more non-hyphen characters (the rest of the word)
The replacement uses $1 to refer to the captured character
Example:
+---------------------------------------------------------------------------------+
| REGEXP_REPLACE(CONCAT('-', 'hello-welcome-to-tutorials'), '-([^-])[^-]*', '$1') |
+---------------------------------------------------------------------------------+
| hwtt |
+---------------------------------------------------------------------------------+