combining non string datatype

COMBINING NON STRING DATATYPE WITH STRING

When we are combining string we have CONCAT function in SQL 2012 but in earlier version we are using operator (+).

Now in case we have to contact Date, Numeric values with string how we will do this? If we use operator (+) then this will give error.

Conversion failed when converting the varchar value 'dada' to data type int.

What we can do here, we have to make data compatible, for concatenating date or numeric values we need to convert their type to string then we can CONCAT both.

How we will do this?

We have another method (CAST, CONVERT), both can be used to convert the data type.

To concatenate non string values to strings, the non-string value must be converted to a string. If the string value can be implicitly converted to a number, the values will be added together instead.

Example:-

select 1+'1'

If we are getting result as 11 instead of 2, then numeric value must be converted to a string.

This can be achieved using CAST or CONVERT function.

If you attempt to concatenate a non-numeric string and a number without converting, you will receive an error message. Run this example to see the error:

Example 1

SELECT 1 + 'abc';

You will get this error.

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value 'abc' to data type int.

Example 2

select 'Todays date : '+GETDATE();

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Function CAST or CONVERT can be used to convert a numeric or temporal value to a string.

Syntax :

CAST(<value> AS <new data type>)

CONVERT(<new data type>,<value>)

Using convert function we can use as:-

select 'Todays date : '+convert(varchar(20),GETDATE());

Result: - Todays date : Jul 24 2015  1:46PM

select 'Shyam age is : ' + CAST(32 as varchar(10))+' Years';

Result:-

Shyam age is : 32 Years