While do cast (Decimal to Char): follow me!!!

As a part of my task; I was making query something like below,

select cast (city_code as char(10)), city_name||’_’ ||city_type as CITY_INFO

From CITY

I wondered why my data  for City_Code look like this:

City_Code	CITY_INFO
10134.	COPENHAGEN_CYCKLE
101.	STOCKHOLM_TOG
3497.	DHAKA_RICKSH
2789.	LISBON_TOUR

City_code ended with ‘.’ Then looked carefully the original datatype for the column (CITY_CODE), it’s decimal (18, 0). That is the reason why it’s giving the problem.

Googled it and found some solution:

select cast(cast(city_code as integer) as char(10)), city_name||’_’ ||city_type as CITY_INFO

From CITY

Since the column is decimal, it’s first cast to integer and then cast to char. It works perfectly. But senior developer who reviewed the code asked not to two time casting rather use trim function to do this.

Fair enough!! Got motivation to do something else J

Then I have tried to implement the code like below:

select TRIM(TRAILING '.' FROM (cast(UNQ_ID_SRC_DEC as char(10))))

,city_name||’_’ ||city_type as CITY_INFO

 From CITY

I have trimmed with trailing ‘.’ But result did not change!!

City_Code	CITY_INFO
10134.	COPENHAGEN_CYCKLE
101.	STOCKHOLM_TOG
3497.	DHAKA_RICKSH
2789.	LISBON_TOUR

I was really curious to know where the problem is. After investigating a little more time on it, I made the query like below:

select TRIM(TRAILING '.' FROM (cast(UNQ_ID_SRC_DEC as varchar(10))))

,city_name||’_’ ||city_type as CITY_INFO

 From CITY

Yaahuuu!! It works as I expected. ‘.’ has been removed.

City_Code	CITY_INFO
10134	COPENHAGEN_CYCKLE
101	STOCKHOLM_TOG
3497	DHAKA_RICKSH
2789	LISBON_TOUR

I just changes cast data type from char to varchar and then it works fine!!

Hope my experience will  help some of you sometime!!!