MySQL:- Cast Integer to String

Background

Using MySQL and needing to quickly concatenate numbers and strings.

Considered using the cast or the convert operator to “prep” the numbers.

 

Lab

Operator – Cast

Operator – Casting Number To Character

Syntax

Using the cast operator goes something like this:-


cast(<variable> as <datatype>)

Casting to the character datatype looks like:-


cast(<variable> as char)

Operator – Cast To Character

SQL

set @id1 =1;
set @id2 =400000;


set @id1AsCHAR1 = cast(@id1 as char);
set @id2AsCHAR2 = cast(@id2 as char(10));

select 
      @id1 as id1
    , @id1AsCHAR1 as id1AsCHAR1
    , length(@idAsCHAR1) as char1Length    
;

select
      @id2 as id2
    , @id2AsCHAR2 as id2AsCHAR2    
    , length(@id2AsCHAR2) as char2Length    
;
    
Output
Output – Grid -01

Output – Grid -02

Explanation
  1. The cast operator works perfectly
    • We were able to cast an int to string using char
    • We were also able to cast an int to string using char(10)
      • The leading spaces were stripped and so no need for trim to remove the padded spaces

Operator – Cast To Varchar

SQL

set @id =1;
set @idAsVarchar1 = cast(@id as varchar)
set @idAsVarchar2 = cast(@id as varchar(4))

;

select 
      @id as id
    , @idAsVarchar1 as idAsVarchar1 
    , @idAsVarchar2 as idAsVarchar2

;
    
Output
Output – Image

Output – Text

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)
set @idAsVarchar2 = cast(@id as varchar(4))' at line 1

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)
set @idAsVarchar2 = cast(@id as varchar(4))' at line 1


Explanation
  1. Attempting to cast to varchar failed
  2. The varchar datatype can not be the recipient of the cast operator

 

Referenced Work

  1. Stack Overflow
    • int to string in MySQL
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s