Inserting substring in string at specific index counting from end. How to in mysql?

I have problem with mysql insert().

I have strings of variable length (15-20 chars) in a table, and I need to add 4 characters to every string. The catch is that I need to add them exactly 4 chars before the end of the string.

For example, given input string 12345开发者_JAVA百科67890-123 I need to add 'ABCD' before '-', so that the string becomes 1234567890ABCD-123.

• I tried with insert() function but it can't do it.
• Is there solution for this or do I need to write an application to do it?
• I use MySQL 4.1.

UPDATE yourTable 
SET yourString=CONCAT(LEFT(yourString,(CHAR_LENGTH(yourString)-4)), "ABCD", RIGHT(yourString,4))

As always with such queries, test it first on a small sample of the data before running it.

CONCAT( SUBSTR( str, 0, LENGTH(str) - 4 ), new_characters, SUBSTR( str, -4 ) )

REPLACE provide a solution:

UPDATE `table` SET `column` = REPLACE(`column`, '-', 'ABCD-');

If you want to do this on the database side you can take advantage of Stored Procedures in MySQL.

Do this:

select insert('1234567890-123', locate('-', '1234567890-123'), 0, 'ABCD');

I have used the literal '1234567890-123', but for a column, it would look like:

select insert(col, locate('-', col), 0, 'ABCD');

Note that this inserts the string before the hyphen -. Alternatively, you could insert it at a particular position, or at a position back from the end: length(col) - 4