CODEWITHSUNDEEP
mysqlstringselectreplacesubstring
user3368883
user3368883

Reputation:

Replace first character in a string in MySQL

On my database MySQL I have this string :

19-003950

From this string I need extract only 

003950

And transform to :

103950

replacing first character '0' to '1' in a string.

I have tried withous success this SQL query:

mysql> SELECT
    REPLACE (
        SUBSTRING_INDEX('19-003950', '-' ,- 1),
        SUBSTRING(
            SUBSTRING_INDEX('19-003950', '-' ,- 1),
            1,
            1
        ),
        '1'
    ) AS NEWSTRING;
+-----------+
| NEWSTRING |
+-----------+
| 113951    |
+-----------+
1 row in set

Please can you help me ?

Upvotes: 1

Views: 3299

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13026

Here's your query.

select concat('1', right(reverse(substring_index(reverse('19-003950'),'-', 1)), 
             length(substring_index(reverse('19-003950'),'-', 1) - 1)))

Upvotes: 0

GMB
GMB

Reputation: 222722

Consider:

select 
    concat(
        '1',
        substring(str, locate('-', str) + 1)
    ) new_string
from (select '19-003950' str) t

locate('-', str) gives you the position of the dash in the string. You can add 2 to that and take everything from that position until the end of string. Finally, concatenate '1' at the beginning of the string.

Demo on DB Fiddlde:

| new_string |
| :--------- |
| 103950     |

Upvotes: 1

Related Questions