How to insert space after each character in a string MySQL

Question

Hi,

I have a character string and for reporting/alignment purpose I need to insert a space after each character. Unfortunately I will have to do it in a SQL or I can write format function.

e.g. "abcd123" to be converted it to "a b c d 1 2 3 ".

Since it's for a considerable number of rows I was wondering how optimized it will be to parse each character and build a new string each time?

Any help is appreciated.

Thanks

Answer 1

I would not recommend storing unnecessary spaces in SQL. This will only waste space.

How about writing a custom retrieval method that will return all strings from your DB with spaces after each character?

Answer 2

Here's a quick & dirty mysql function which solves your problem:

delimiter ||
DROP FUNCTION IF EXISTS concat_whitespace||
CREATE FUNCTION concat_whitespace( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA 
BEGIN
DECLARE len INT UNSIGNED;
DECLARE erg LONGTEXT;
SET len = LENGTH(x);
REPEAT
SET erg = CONCAT_WS(' ',SUBSTRING(x,len,1),erg);
SET len = len - 1;
UNTIL len < 1 END REPEAT;
return erg;
END;
||

Now try this:

Select concat_whitespace("abcd123")

which returns "a b c d 1 2 3"

This function basically loops for each character and concats a space. Direction is from end to beginning, which saves a counter variable.

I havn't done any performance test, fur sure you somehow could optimize this..