I have a table called cities, with a column for timezones ( Timezone VARCHAR(10) NOT NULL that has timezones stored like: +00:00 or -02:00). - I am trying to select all cities that are two hours behind a city belonging to the +02:00 timezone. I want to select the third character of the string and convert it to int. This

Can i use SUBSTR() inside of CAST() method?

I have a table called cities, with a column for timezones ( Timezone VARCHAR(10) NOT NULL that has timezones stored like: +00:00 or -02:00). – I am trying to select all cities that are two hours behind a city belonging to the +02:00 timezone.
I want to select the third character of the string and convert it to int. This is my approach:

SELECT CAST(SUBSTR(Timezone, 3, 1) AS INT) FROM Cities;

My query returns error 1064. I cannot figure out why the syntax of my code is not correct. How can i solve this? Thank you!

SUBSTR(Timezone, 3, 1) returns the character that i want to modify. I do not want to replace it with 0, even if it would be easier, because having the posibility to do arithmetic operation with that number can help me reuse the code in future queries.

Answer

You need UNSIGNED (not INT) for CAST a numeric string as INTEGER

set @timezone ='+00:00';
SELECT CAST(SUBSTR(@Timezone, 3, 1) AS UNSIGNED)

and

SELECT CAST(SUBSTR(Timezone, 3, 1) AS UNSIGNED)
FROM cities

Advertisement

Answer