Changing the character of the vehicle registration plate - Mercosur standard

Question

Changing the character of the vehicle registration plate - Mercosur standard

Asked 5 years, 6 months ago

Viewed 194 times

1

In the Oracle database, 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. I’d like to execute a command SQL to fix all 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, according to the standard table of Mercosul board, as below: Old plate Mercosur Plate 0 A 1 B 2 C 3 D 4 and 5 F 6 G 7 H 8 I 9 J

You can help me make the right command to resolve this situation?

Give a read on the functions Charindex() and Substring()

– Ronaldo Araújo Alves

2019/05/17 at 18:39
But after all, which is the database manager: mysql, oracle or sql server?

– José Diz

2019/05/17 at 18:42
I will use in oracle, but the suggestion can be in any one, if you prefer oracle

– Mauri

2019/05/17 at 18:44
It is important to clearly define the DB used, because the solution varies according to the engine. There you can use to post what you tried, in the form of [mcve], describing the difficulty. If you simply want to know how to insert a character within a string, you can simplify the question by removing all the superfluous information from the reason you are doing this, making possible solutions compatible with the website model (question about objective problem, useful answers for a wide range of users, which is compromised by the specificity of the plates).

– Bacco

2019/05/18 at 14:40
I edited the question with more details, thanks for the comment!

– Mauri

2019/05/29 at 14:46

1 answer

Browser other questions tagged oracle11g

You are not signed in. Login or sign up in order to post.

by José Diz • **6,942** points · Answer 1 · 2019-05-17T18:55:23+00:00

Here is a suggestion for SQL Server.

-- código #1 v2
SELECT Placa, 
       -- método 1
       substring (Placa, 1, 4) +
       char ((65 + cast (substring (Placa, 5, 1) as int))) +
       substring (Placa, 6, 2) as [Placa 1],
       -- método 2
       stuff (Placa, 5, 1, 
              char ((65 + cast (substring(Placa, 5, 1) as int)))) as [Placa 2]
  from tbPlaca;

The above code considers that the board is declared as text (char(7), for example) and that it is always 7 characters long.

For Oracle Database, I think the following is:

-- código #2
SELECT Placa, 
       -- método 1
       substr (Placa, 1, 4) ||
       chr ((65 + cast (substr (Placa, 5, 1) as integer))) ||
       substr (Placa, 6, 2) as [Placa 1],
       -- método 2
       stuff (Placa, 5, 1, 
              chr ((65 + cast (substr(Placa, 5, 1) as integer)))) as [Placa 2]
  from tbPlaca;