Which SQL command to use to change a character of the vehicle plate - Mercosur Standard

Question

I use a system that works with the Oracle 11g database and I have the table (Tab_plavei) with the string field (Plavei), format UU9999. I need to adjust this field to the new standard of Mercosur boards, format UU9U99.

Which command SQL should use to fix all the cards that have already been registered in the format UU9999 changing to the new format UU9U99, that is, change the 2nd numeric character or 5º of the string of the board, according to the standard table of Mercosul board: string 0 -> A; 1 -> B; 2 -> C; 3 -> D; 4 -> E; 5 -> F; 6 -> G; 7 -> H; 8 -> I and 9 -> J.

Thanks for the help!

Answer 1

Hello,

What you want to do is a little more complicated than it looks. Come on: to update the table, use the command UPDATE; to do what you want and modify a certain character in the string, the command STUFF() should work perfectly; already to replace the whole with the letter of the corresponding alphabet, try the command SUBSTRING().

Example of the STUFF function:

STUFF(ABC, @n, 1, 'X')

Take the value of the ABC field, start at the position @n, deletes 1 character and inserts character 'X' in position.

Example of the SUBSTRING function:

SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', ABC, 1)

Replaces integer 1 by A, 2 by B, 3 by C and so on.

Final code:

UPDATE tableName
SET PlaVei = CONCAT( -- Concatena tudo para formar a nova placa
    SUBSTRING(PlaVei, 1, 4), -- Caracteres antes da letra alterada
    SUBSTRING("ABCDEFGHIJKLMNIOPQRSTUVWXYZ", SUBSTRING(PlaVei, 5, 1), 1), -- Altera o inteiro pela letra
    SUBSTRING(PlaVei, 6, 7) -- Caracteres depois da letra alterada
)

I hope I helped, hug!