直接在SQL命令中检测和转换数据类型? [英] Detecting and converting data types right in SQL commands?

问题描述

我想知道我们是否有任何方法(查询)直接在SQL命令中转换数据类型?
例如，将字符串转换为整数.在进行转换之前，我可能想检查一下是否可以进行转换，当然应该在SQL命令中检查如何进行转换?

注意:假设该字段没有CHECK约束！
非常感谢！

I wonder if we have any way (query) to convert data types right in SQL commands?
For example, converting a string to an integer. Before converting, I may want to check if the conversion is possible, how can I check it, of course right in SQL commands?

Note: suppose that the field has no constraint of CHECK!
Thank you so much!

推荐答案

您可以这样写-

you can write like this--

declare @var1 as char(20)
set @var1="214"

select case when isnumeric(@var1) =1 then cast(@var1 as float) else null end 

或者您可以直接输入值

or you can directly put the value

select case when isnumeric(col1) =1 then cast(col1 as float) else null end from tablename
select case when isnumeric(''525'') =1 then 525 else null end 

用于访问的查询将更改为

for access the query will change as

select iif(isnumeric(col1) ,cdbl(col1),null) from tablename

而syntex是-

and the syntex is-

Case when condition then truepart else falsepart end
iif(condition,truepart,falsepart)

--Pankaj

--Pankaj

要确定列的数据类型，可以查询INFORMATION_SCHEMA.COLUMNS.例如:

To determine the data type for a column you can query INFORMATION_SCHEMA.COLUMNS. For example:

SELECT * from INFORMATION_SCHEMA.COLUMNS

使用这些信息，您可以创建一个小的函数，该函数将列转换为另一种数据类型(基于参数).

Using that information, you can create a small function which casts the column for another data type (based on parameters).

这篇关于直接在SQL命令中检测和转换数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！