Manipulate string

Manipulate string

Hi all.

This is my initial email string in MySQL:
chevymark.sunderland@9651.com

I try this query:


mysql> SELECT
	REPLACE (
		SUBSTRING_INDEX(
			'chevymark.sunderland@9651.com',
			'@',
			1
		),
		'.',
		' '
	) AS output;
+----------------------+
| output               |
+----------------------+
| chevymark sunderland |
+----------------------+
1 row in set

Now I need this other output:


+---------+
| output  |
+---------+
| chv snd |
+---------+

would be to say the first three consonants of the name and first three consonants of the surname.
alternatively three random characters from the name and surname…

I try this query but the output is wrong:


mysql> SELECT
	CONCAT(
		LEFT (
			REPLACE (
				SUBSTRING_INDEX(
					'chevymark.sunderland@9651.com',
					'@',
					1
				),
				'.',
				' '
			),
			3
		),
		' ',
		(
			LEFT (
				REPLACE (
					SUBSTRING_INDEX(
						SUBSTRING_INDEX(
							'chevymark.sunderland@9651.com',
							'@',
							1
						),
						'.',
						' '
					),
					' ',
					- 1
				),
				3
			)
		)
	) AS output;
+--------+
| output |
+--------+
| che    |
+--------+
1 row in set

how can i do it?
any help?
thank you.

What happens if the user doesn’t even have 3 characters in one of their names?

I don’t have any of these cases

I believe your going to have to use a server side function in order to accomplish this (PHP / ASP).

Using PHP, I would use str_split() and iterate through until you have your result.

thank you for help.
My language servers side is ASP NET and this the solution (Linq to filter):


        string vowels = "aeiouy";
        string name = "chevymark.sunderland@9651.com";
        name = new string(name.Where(c => !vowels.Contains(c)).ToArray());
        string[] strresult = name.Split('.');
        string stroutput = strresult[0].Substring(0,3) + " " + strresult[1].Substring(0,3);