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.
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));
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)
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.
SELECT STUFF(name, 5, 0, ' ') FROM Customers
'Name'->Name).