[ACCEPTED]-tsql : best way to cast variables to string type?-tsql

Accepted answer

Score: 28

All-caps is literal text, lower-case is 9 something into which you should interpolate 8 a value:

CAST(expression AS CHAR)
-- or:
CAST(expression AS VARCHAR)

You can optionally specify a length.

Unfortunately, it's 7 a bit harder with datetimes, if you want 6 to format it in any way that's different 5 from the default representation.

My info 4 came from the MSDN site. I think you'll have to read 3 that site, and others, carefully and play 2 around with it a bit, but hopefully the 1 CAST function will be a good start.

Good luck!

Score: 1

you need to convert/cast it each time. I 5 made a function to use:

CREATE FUNCTION QuoteNull
(
     @InputStr      varchar(8000)  --value to force to string
)
RETURNS
varchar(8000)
AS

BEGIN
    RETURN COALESCE(''''+@InputStr+'''','null')
END

it puts single quotes 4 around the value or just the word null if 3 it is null, but you can customize it as 2 necessary.

here is a version that handles 1 formatting dates automatically:

CREATE FUNCTION QuoteNull
(
     @InputStr      sql_variant   --value to force to string
)
RETURNS
varchar(8000)
AS

BEGIN
    DECLARE @String  varchar(8000)
    SET @String=COALESCE(''''+  CASE SQL_VARIANT_PROPERTY(@InputStr,'BaseType')
                                    WHEN 'datetime' THEN CONVERT(varchar(23),@InputStr,121)
                                    ELSE CONVERT(varchar(8000),@InputStr)
                                END
                             +'''','null')
    RETURN @String
END

Score: 0

I do not think there is--and if there were, I 5 wouldn't trust it, as there'd probably be 4 very little control over the overall formatting. Build 3 your own, and it'll look exactly as it needs 2 to look. Factors to contemplate:

Date formatting
Numbers, leading zeros, decimal places, +/- signs
Line length, word wrap
Leading spaces, trailing spaces, spaces between strings that don't have leading or trailing spaces

...it goes 1 on and on.

Source: stackoverflow.com

[ACCEPTED]-tsql : best way to cast variables to string type?-tsql

IF/ELSE Stored Procedure

Conditional Operator in SQL Where Clause

automate a SQL query to run every month

SQL Server 2005 Get First and Last date for any Month in any Year

Why does putting a WHERE clause outside view have terrible performance

How do I properly SELECT INTO @TempTable Multiple times to return a single resultset?

best way to convert and validate a date string

How to create sequential number column index on table with data

Aggregate function checking 'if contains'

TSQL - ISNULL over multiple columns