CODEWITHSUNDEEP
mysql
BT.
BT.

Reputation: 13

How to insert space after each character in a string MySQL

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

Upvotes: 1

Views: 7361

Answers (3)

Abhinand
Abhinand

Reputation: 11

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

Upvotes: 0

RngTng
RngTng

Reputation: 1239

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..

Upvotes: 3

tinkertime
tinkertime

Reputation: 3042

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?

Upvotes: 2

Related Questions