Add the prefix 00 and navicatmysql to a field in Mysql in Navicat.

Add the prefix 00 and navicatmysql to a field in Mysql in Navicat.

For the first time, I hope you will pay more attention to it.

In my case, if the content of the varchar field in a table in Navicat is less than 5 characters long, add '0' before the content. If the field content is 101, I will change it to 00101;

There are two difficulties: 1. determine the length of the field content (I do not know enough) 2. Add a prefix to the field content;

The previous error SQL is:

Update base_sf set diy_code = '00' + diy_code

Where pk in (select pk from base_sf where LENGTH (diy_code) = 3)

In this way, diy_code in Navicat is regarded as a numerical value, so it has never been successful.

The correct SQL statement is:

Update base_sf set diy_code = concat ('0', diy_code)

Where pk in (select * from (select pk from base_sf where LENGTH (diy_code) = 4) tmp)