Statement for converting varchar to int4 in PostgreSQL

Today is depressing.

In the SHP file of National Basic Geographic Data, the provincial data adcodd99 is int type. I imported it into PostgreSQL and it became int4 type.

However, the adcodd99 of the district-level data is portable and imported into the database as varchar. After querying the PostgreSQL documentation, no function can be directly converted into a number of strings. Some research found that it can be converted using a combination of a bunch of string functions provided by it. The data table is geo_country_poly. I have created a new int4 adcodd99_int column. Run the following SQL statement to convert the data:

Update geo_country_poly set adcodd99_int =
100000 * (ASCII (substring (adcode99 from 1 for 1)-48) +
10000 * (ASCII (substring (adcode99 from 2 for 1)-48) +
1000 * (ASCII (substring (adcode99 from 3 for 1)-48) +
100 * (ASCII (substring (adcode99 from 4 for 1)-48) +
10 * (ASCII (substring (adcode99 from 5 for 1)-48) +
1 * (ASCII (substring (adcode99 from 6 for 1)-48)

Note:

function	return type	description	example	result
ASCII ( text )	integer	ASCII code of the first character of the parameter	ASCII ('x')	120
substring (string [from integer] [for integer])	text	extract substring	substring ('Thomas 'from 2 for 3)	trim