I have a problem with the replace function in mysql for example
set @str = "Category, CategoryName, CategoryID";
SELECT REPLACE (@str, "Category", "125");
I would replace only the first word "Category" and here's the result:
125,125Name, 125ID
how to fix the problem
thank you in advance
If you realy want to use MySQL REPLACE function, the only option I see is this:
set @str = "Category, CategoryName, CategoryID";
SELECT REPLACE (@str, "Category,", "125,");
If you like to replace only the first characters (if you define the first characters has to be equal to the string "Category") you can give a try:
SELECT
fieldname,
REPLACE(fieldname, 'Category', '125'),
LEFT(fieldname, 8),
RIGHT(fieldname, (LENGTH(fieldname)-8)),
CONCAT('125', RIGHT(fieldname, (LENGTH(fieldname)-8)))
FROM tablename
WHERE LOWER(LEFT(fieldname, 8)) = 'category';
Your result look like this:
CategoryNameCategorySomethingElse | 125Name125SomethingElse |
Category | NameCategorySomethingElse | 125NameCategorySomethingElse
Now, you can prepare your update statement:
UPDATE tablename
SET fieldname = CONCAT('123', RIGHT(fieldname, (LENGTH(fieldname)-8)))
WHERE LOWER(LEFT(fieldname, 8)) = 'category';
