CODEWITHSUNDEEP
mysql
saamii
saamii

Reputation: 93

SQL add space in column string

I have an sql table named Customers. In Customers I have the column Name.

I want to add in the column Name an space after 5 characters.

Example:

From the column "Name" with the content "name1234" i want add a space at the position 4 so that the result is "name 1234".

I have try it so but it dont work.

SELECT INSERT('Name',4,0, ' ')

FROM
  Customers

How would you do it, please help.

Upvotes: 4

Views: 43910

Answers (4)

Karl the Hack Ackerman
Karl the Hack Ackerman

Reputation: 1

WITH  My_string(some_text) AS (VALUES ('This is some text') )

SELECT

   some_text,

   CAST ( (WITH RECURSIVE Counter(x) AS ( VALUES ( ( 1 ) ) UNION ALL SELECT x+1 FROM Counter WHERE x < length(some_text) )

SELECT GROUP_CONCAT(substr(some_text, x, 1),' ') FROM counter)
       AS TEXT) Some_text_With_Spaces

FROM My_string

You are getting the length of the string and creating a temp table with a count from 1 to the length then using GROUP_CONCAT with SUBSTR to pull each character out and use the specified delimiter. In the above example a space.

Upvotes: 0

Jean Amnotte
Jean Amnotte

Reputation: 1

SELECT STUFF(name, 5, 0, ' ') FROM Customers

Upvotes: -1

g_tec
g_tec

Reputation: 641

What you want is to update your column values with a concatenation of the first part of the name followed by the space ' ', followed by the rest of the name.

UPDATE Customers
 SET name = CONCAT(SUBSTRING(name, 1, 5), 
     ' ', 
     SUBSTRING(name, 6));

Upvotes: 4

malyy
malyy

Reputation: 874

Check this: http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_insert

SELECT INSERT('Customers', 6, 0, ' ');

To update all rows:

UPDATE YOURTABLE
SET YOURCOL = INSERT(Customers, 6, 0, ' ');

UPDATE

Here is an example:

mysql> select * from CUSTOMERS;
+----+---------+-----+---------+--------+
| ID | NAME    | AGE | ADDRESS | SALARY |
+----+---------+-----+---------+--------+
|  1 | Name456 |   0 |         |   0.00 |
+----+---------+-----+---------+--------+
1 row in set (0,02 sec)

mysql> UPDATE CUSTOMERS SET Name = INSERT(Name, 6, 0, ' ');
Query OK, 1 row affected (0,05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from CUSTOMERS;
+----+----------+-----+---------+--------+
| ID | NAME     | AGE | ADDRESS | SALARY |
+----+----------+-----+---------+--------+
|  1 | Name4 56 |   0 |         |   0.00 |
+----+----------+-----+---------+--------+
1 row in set (0,00 sec)

Upvotes: 0

Related Questions