How to add a character to specific index in string

I have a db with email-addresses which are 15 characters in total.

Some are 14 characters long and they have the same in common, they miss a 0 in their name at the 3th index.

I had a what similar problem with the id numbers but that was easy to fix because i had to push 0 to the first index.

My expected results should be changing '[e-mail]' to '[e-mail]'.

Answer

To add a 0 as 4th character in emails having 14 characters, you may simply use SUBSTR :

SELECT CONCAT(SUBSTR(email, 1, 3), '0', SUBSTR(email, 4))
FROM mytable
WHERE LENGTH(email) = 14

Another solution is to use REGEXP_REPLACE, with a regular expression that matches only on emails with 14 characters ; this avoids the need for a WHERE clause, as emails that do not match the pattern will be returned untouched :

SELECT REGEXP_REPLACE(email, '^(.{3})(.{11})$', CONCAT('$1', '0', '$2'))
FROM t

db<>fiddle here :

 WITH t AS (SELECT '[e-mail]' email UNION SELECT '[e-mail]')
 SELECT CONCAT(SUBSTR(email, 1, 3), '0', SUBSTR(email, 4))
 FROM t
 WHERE LENGTH(email) = 14

| CONCAT(SUBSTR(email, 1, 3), '0', SUBSTR(email, 4)) |
| :----------------------------------------------------- |
| [e-mail]                                        |


 WITH t AS (SELECT '[e-mail]' email UNION SELECT '[e-mail]')
 SELECT REGEXP_REPLACE(email, '^(.{3})(.{11})$', CONCAT('$1', '0', '$2'))
 FROM t

| REGEXP_REPLACE(email, '^(.{3})(.{11})$', CONCAT('$1', '0', '$2')) |
| :---------------------------------------------------------------- |
| [e-mail]                                                   |
| [e-mail]