Sql-server – use Try_Cast to give me a query result column with different datatypes

sql serversql-server-2012t-sql

I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need).

I am trying to make a view for this table that allows the value to display as XML (with the nice link feature that comes with SSMS) when the data is valid XML, but still shows the string when it is invalid XML.

Like this:

SELECT TRY_CAST([RunLog] AS xml) as [Log], -- works
       RunLog,                             -- Also Works

       -- This is the one I want, but it tries to parse all of them 
       -- (even the ones that fail the cast)
       Case 
           When Try_Cast(RunLog as xml) is NULL 
                Then NettinRunLoggLog else CAST(RunLog AS xml)
       end as CombinedLog

FROM   LoggingTable

I think that this is a lost cause, because SQL Server will not allow a result to have more than one datatype. But I thought I would ask the experts just to be sure.

Best Answer

All output of the case expression must be of the same datatype.

If maintaining the XML datatype for the valid XML data is worth a little manipulation of the invalid data, then one workaround could be to wrap the invalid data up like a declaration before casting it to XML:

cast('<?invalid ' + RunLog + ' ?>' as xml)

Also, you shouldn't need to use both try_cast and cast in the same expression.

coalesce(try_cast(RunLog as xml),cast('<?invalid ' + RunLog + ' ?>' as xml))

Related Solutions

SQL Server Float Datatype – How to Use with 2 Digits After Decimal Point

This can be achieved with the decimal datatype.

See below for an example:

declare @num as float;
set @num=5.20;

select convert(decimal(10, 2), @num);

The output here will be 5.20.

After re-reading your question:

But this is not the solution i am expecting. Is there any method like setting datatype or changing datatype so that i can achieve the same?

Is there a reason why you are specifically using float? Oftentimes people tend to default to that datatype when decimal is more than sufficient.

SQL Server – Using LIKE with Variables

This applies the same matching logic as Aaron Bertrand to a Windowed Aggregate Function:

;WITH dt AS 
(
  SELECT tab.*,
    COUNT(*) 
    OVER (PARTITION BY [TS #] + CASE WHEN [TS #] NOT LIKE '% -W' THEN ' -W' ELSE '' END) AS cnt
  FROM tab
)
SELECT *
FROM dt 
WHERE cnt > 1;

Fiddle

Edit:

As [TS #] is not unique, the above will not work and DISTINCT is not allowed in Windowed Aggregate Functions...

So this uses a modified logic: If MIN and MAX are different, both versions exist:

;WITH dt AS 
(
  SELECT tab.*,
    MIN([TS #]) 
    OVER (PARTITION BY [TS #] + CASE WHEN [TS #] NOT LIKE '% -W' THEN ' -W' ELSE '' END) AS minTS,
    MAX([TS #]) 
    OVER (PARTITION BY [TS #] + CASE WHEN [TS #] NOT LIKE '% -W' THEN ' -W' ELSE '' END) AS maxTS
  FROM tab
)
SELECT *
FROM dt 
WHERE minTS <> maxTS;

Fiddle

Best Answer

Related Solutions

SQL Server Float Datatype – How to Use with 2 Digits After Decimal Point

SQL Server – Using LIKE with Variables

Related Question