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
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..
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?
update tablename set comment= trim(concat(substr(comment, 1,1),space(1),substr(comment, 2,1),space(1),substr(comment, 3,1),space(1),substr(comment, 4,1),space(1),substr(comment, 5,1),space(1),substr(comment, 6,1),substr(comment, 7)));
here comment is column name
