Search code examples
sqlsql-variant

Try_casting a Sql_variant column truncating the value after a comma

I have a column 'Value' of sql_variant type. It has the value 

'Device Warning: Bus Transient, Warning Code: 164, Timestamp: 2015-12-23 13:02:48.750'

If I try to select 

TRY_CAST([Value] AS nvarchar) as varvalue

I get the value 

Device Warning: Bus Transient,

How can we cast a sql_variant type without losing some data? I needed to convert it into a nvarchar because I need to use a SUBSTING function to extract parts of the value.

Any help would be greatly appreciated!!!

Solution

  • nchar and nvarchar (Transact-SQL)

    Remarks
    When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

    1> select len('Device Warning: Bus Transient,')
2> go

-----------
         30

    Specify a length.

    QED