CODEWITHSUNDEEP
mysqlsqlsql-update
JIJOMON K.A
JIJOMON K.A

Reputation: 1280

mysql replace a character with another

I have a table with some values like below,

Slno
---------
IFAAA1121
IFAAA1122
IMBBB1121
IMBBB11223

My goal is to reformat the SlNo in to the below format,

Slno
---------
IF-AAA-1121
IF-AAA-1122
IM-BBB-1121
IM-BBB-11223

How is it possible ?

My query is:

UPDATE `certificate_log_uae` 
SET `DeviceSerialNumberTemp` = REPLACE(LEFT(DeviceSerialNumberTemp,2),
                                       LEFT(DeviceSerialNumberTemp,2).'-')

Upvotes: 1

Views: 509

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37500

Try simple insert function:

select Slno, insert(insert(slno, 3, 0, '-'), 7, 0, '-') from tbl

Demo

To update values try:

update certificate_log_uae set
DeviceSerialNumberTemp = insert(insert(DeviceSerialNumberTemp , 3, 0, '-'), 7, 0, '-')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

One approach would be to just build the final string you want using concatenation:

UPDATE certificate_log_uae
SET DeviceSerialNumberTemp = CONCAT(LEFT(DeviceSerialNumberTemp, 2),
                                    '-',
                                    SUBSTRING(DeviceSerialNumberTemp, 3, 3),
                                    '-',
                                    SUBSTRING(DeviceSerialNumberTemp, 6));

enter image description here

Demo

If you are using MySQL 8+ or later, then there is a very simple regex based solution using REGEXP_REPLACE:

SELECT
    DeviceSerialNumberTemp,
    REGEXP_REPLACE(DeviceSerialNumberTemp, '(.{2})(.{3})(.*)', '$1-$2-$3') AS output
FROM certificate_log_uae;

Demo

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

  • You can use the Substr() function to get substrings out from your input string, at various positions and lengths.
  • Since the length of the last substring is not fixed; we can simply specify the start position to slice the substring, and leave specifying the length parameter. It will consider the substring till the end of the overall string.
  • Now, just concatenate this substrings back using - appropriately.

Try the following:

UPDATE `certificate_log_uae` 
SET `DeviceSerialNumberTemp` = CONCAT(SUBSTR(`DeviceSerialNumberTemp`, 1, 2), 
                                      '-', 
                                      SUBSTR(`DeviceSerialNumberTemp`, 3, 3), 
                                      '-', 
                                      SUBSTR(`DeviceSerialNumberTemp`, 6)
                                     )

Upvotes: 1

Related Questions