【SQL 编程你也行】SQL Server 2012新功能之函数:转换函数
在SQL Server 2012中,新增了几个转换函数,用于支持数据类型的强制转化。
由于之前主要用的是SQL Server 2008R2,而公司的项目为了提高开发效率,很多表的列都为varchar类型,但也会存储数字。
当对这些列进行聚合计算,比如sum时,要先进行cast转换为numeric类型,经常会报错,现在有了这些新的函数,可以先try一下,通过判断返回值是否为null,通过这个返回值,我们可以判断字段值是否可以转化为数字,如果不是数字,就直接过滤掉,所以这些函数非常实用,也希望对大家有用。
1、PARSE函数
语法:PARSE ( string_value AS data_type [ USING culture ] )
参数:
string_value:nvarchar (4000) 值,表示要解析为指定数据类型的格式化值。
string_value:必须为所请求的数据类型的表示形式,否则 PARSE 将引发错误。
data_type :表示结果的所请求数据类型的文本值。
culture :可选字符串,它标识对 string_value 进行格式化的区域性。
如果未提供 culture 参数,则使用当前会话的语言。 可以使用 SET LANGUAGE 语句隐式或显式设置 此语言。 culture 接受 .NET Framework 支持的任何区域性;它不局限于 SQL Server 显式支持的语 言。 如果culture 参数无效,PARSE 将引发错误。
例子:
--解析为 日期
SELECT PARSE('2010年10月11日' AS datetime2 USING 'zh-CN')
/*
2010-10-11 00:00:00.0000000
*/
--解析为 美元
SELECT PARSE('$345.98' AS money USING 'en-US')
/*
345.98
*/
--先设置要进行格式化的区域性,然后解析为 日期
SET LANGUAGE '简体中文';
SELECT PARSE('2010年10月11日' AS datetime)
/*
2010-10-11 00:00:00.000
*/
2、TRY_CAST函数
返回转换为指定数据类型的值(如果转换成功);否则返回 Null。
可以用来判断指定值是否能转化为相应的数据类型,间接就可以知道值是什么数据类型。
语法:TRY_CAST ( expression AS data_type [ ( length ) ] )
参数:
expression:要转换的值。 任何有效的表达式。
data_type :要将 expression 转换到的数据类型。
length :指定目标数据类型长度的可选整数。可接受值的范围由 data_type 的值确定。
例子:
--返回1,表示“.”是一个数字,这显然是不对的
select isnumeric('.')
--转换失败
SELECT
CASE WHEN TRY_CAST('.' AS numeric(10,2)) IS NULL
THEN '转换失败'
ELSE '转换成功'
END
--转换成功
SELECT
CASE WHEN TRY_CAST('1' AS numeric(10,2)) IS NULL
THEN '转换失败'
ELSE '转换成功'
END
3、TRY_CONVERT函数
返回转换为指定数据类型的值(如果转换成功);否则返回 Null。
可以用来判断指定值是否能转化为相应的数据类型,间接就可以知道值是什么数据类型。
语法:TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
参数:
data_type [ ( length ) ]:要将 expression 转换到的数据类型。
expression : 要转换的值。
style :一个可选的整数表达式,指定 TRY_CONVERT 函数如何转换 expression。style 接受与 CONVERT 函数的 style 参数相同的值。 可接受值的范围由 data_type 的值确定。 如果 style为 null,则TRY_CONVERT 返回 null。
例子:
--函数直接报错,是因为函数的写法有问题
SELECT
CASE WHEN TRY_CONVERT(xml, 4) IS NULL
THEN '转换失败'
ELSE '转换成功'
END
/*
消息 529,级别 16,状态 2,第 2 行
不允许从数据类型 int 到 xml 的显式转换。
*/
--转换失败
SELECT
CASE WHEN TRY_CONVERT(float, 'a') IS NULL
THEN '转换失败'
ELSE '转换成功'
END
--转换成功
SELECT
CASE WHEN TRY_CONVERT(datetime, '20141201 13:12:11') IS NULL
THEN '转换失败'
ELSE CONVERT(datetime, '20141201 13:12:11') --转换成功
END
SELECT
CASE WHEN TRY_CONVERT(numeric(10,2),'1') IS NULL
THEN '转换失败'
ELSE CONVERT(numeric(10,2),'1')
END
4、TRY_PARSE函数
在 SQL Server 2012 中,返回表达式的结果(已转换为请求的数据类型);如果强制转换失败,则返回 Null。
TRY_PARSE 仅用于从字符串转换为日期/时间和数字类型。
语法:TRY_PARSE ( string_value AS data_type [ USING culture ] )
参数:
string_value:nvarchar(4000) 值,表示要解析为指定数据类型的格式化值。
string_value:必须为所请求的数据类型的有效表示形式,否则 TRY_PARSE 将返回 Null。
data_type :表示结果的所请求数据类型的文本。
culture: 可选字符串,它标识对 string_value 进行格式化的区域性。
如果未提供 culture 参数,则使用当前会话的语言。 可以使用 SET LANGUAGE 语句隐式或显式设置此语言。 culture 接受 .NET Framework 支持的任何区域性;它不局限于 SQL Server 显式支持的语言。 如果culture 参数无效,PARSE 将引发错误。
例子:
SELECT
CASE WHEN PARSE('2010年10月11日' AS datetime2 USING 'zh-CN') IS NULL
THEN '失败'
ELSE '成功'
END '转化结果',
PARSE('2010年10月11日' AS datetime2 USING 'zh-CN') '转化后的值'
/*
转化结果 转化后的值
成功 2010-10-11 00:00:00.0000000
*/