MS-SQL cast command

MS-SQL CAST command

MS-SQL has several different data types for characters, numbers, and so on.  There are times, however, that one data type needs to be treated as a different data type.

For example, let us assume that we have a table has an integer field STORE_NUMBER, and a varchar field STORE_NAME.  There may be times that we want to combine the store number, and name, for reporting purposes.  Say that we have store 100, named "Main store", and want to generate output of "100-Main store".

To concatenate fields, the plus sign (+) is used.  However, if we try to select STORE_NUMBER+’-‘+STORE_NAME, there is a problem.  MS-SQL will try to treat the plus sign as an arithmetic operator, instead of a concatenate operator.  This is because one of the fields, in this case STORE_NUMBER, is a numeric type field (integer), and not a character type field.  MS-SQL then tries to take the value "100", and add the dash character to it, then add the string "Main store" to that.  That just does not work out.

What needs to be done, is to treat the STORE_NUMBER as a character based field.  Then everything is character based, and MS-SQL will perform the concatenate operation.  This is done using the CAST command.

The CAST command will take an argument, and treat it as the data type that you specify.  In this case, treating the STORE_NUMBER as a type VARCHAR will solve the problem.

So, if we select CAST(STORE_NUMBER as VARCHAR)+’-‘+STORE_NAME, we get the desired result of "100-Main store".

Casting a number into a VARCHAR is where I use the cast command most often.  I have used several of the other data types at times also, such as a decimal number into an integer, or a string into a date.

Dave.