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 'aa-001@test.me' to 'aa-0001@test.me'.
Advertisement
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 'aa-001@test.me' email UNION SELECT 'bb-0002@test.me')
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)) |
| :----------------------------------------------------- |
| aa-0001@test.me |
WITH t AS (SELECT 'aa-001@test.me' email UNION SELECT 'bb-0002@test.me')
SELECT REGEXP_REPLACE(email, '^(.{3})(.{11})$', CONCAT('$1', '0', '$2'))
FROM t
| REGEXP_REPLACE(email, '^(.{3})(.{11})$', CONCAT('$1', '0', '$2')) |
| :---------------------------------------------------------------- |
| aa-0001@test.me |
| bb-0002@test.me