T-SQL Tutorial

SQL Conversion functions


Conversion functions perform conversions of expression using cast, convert and parse. The SQL Server Conversion functions are: CAST, CONVERT, PARSE, TRY_CAST, TRY_CONVERT, TRY_PARSE.

CAST

SQL Server CAST convert an expression of one data type to another.

USE model;
GO
DECLARE @string AS varchar(100);
DECLARE @number AS int;
SET @string = '5500';
SET @number = CAST(@string AS INT);
PRINT @number;
GO

CONVERT

SQL Server CONVERT function is the same like the cast function, converts an expression, only the syntax is different.

USE model;
GO
DECLARE @string AS varchar(100);
DECLARE @number AS int;
SET @string = '40300';
SET @number = CONVERT(INT, @string);
PRINT @number;
GO

PARSE

SQL Server PARSE function translate an expression value to the requested data type.

USE model;
GO
DECLARE @string AS varchar(100);
DECLARE @string2 AS varchar(100);
DECLARE @number AS int;
DECLARE @date AS date;
SET @string = '33500';
SET @string2 = 'Sunday, 03 May 2020';
SET @number = PARSE(@string AS INT);
SET @date = PARSE(@string2 AS date);
PRINT @number;
PRINT @date;
GO





TRY_CAST

SQL Server TRY_CAST function perform an converting from string to date/time and number types. Returns null if the cast fails.

USE model;
GO
DECLARE @string AS varchar(100);
DECLARE @string2 AS varchar(100);
SET @string = 'abcde';
SET @string2 = '12345';
SELECT
CASE WHEN TRY_CAST(@string AS int) IS NULL
THEN 'Ok'
ELSE 'Not Ok'
END AS Result;
SELECT
CASE WHEN TRY_CAST(@string2 AS int) IS NULL
THEN 'Ok'
ELSE 'Not Ok'
END AS Result2;
GO

TRY_CONVERT

SQL Server TRY_CONVERT function perform a value cast conversion to the specified data type if the cast succeeds, else if cast is not succeeds returns null.

USE model;
GO
DECLARE @string AS varchar(100);
DECLARE @string2 AS varchar(100);
SET @string = 'xyzijk';
SET @string2 = '97102';
SELECT
CASE WHEN TRY_CONVERT(int, @string) IS NULL
THEN 'Ok'
ELSE 'Not Ok'
END AS Result;
SELECT
CASE WHEN TRY_CONVERT(int, @string2) IS NULL
THEN 'Ok'
ELSE 'Not Ok'
END AS Result2;
GO

TRY_PARSE

SQL Server TRY_PARSE function perform a value cast conversion to the specified data type if the cast succeeds, else if cast is not succeeds returns null.

USE model;
GO
DECLARE @string AS varchar(100);
DECLARE @string2 AS varchar(100);
SET @string = 'test123';
SET @string2 = '10267';
SELECT
CASE WHEN TRY_PARSE(@string AS int) IS NULL
THEN 'True'
ELSE 'False'
END AS Result;
SELECT
CASE WHEN TRY_PARSE(@string2 AS int) IS NOT NULL
THEN 'True'
ELSE 'False'
END AS Result2;
GO