如何将varchar [00001.00]转换为int [1] [英] How to convert varchar [00001.00] to int [1]
本文介绍了如何将varchar [00001.00]转换为int [1]的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨!
我一直试图将此列转换为int。
这是我的查询:
SELECT CAST(SUBSTRING(loanAmount,PATINDEX(' %[^ 0]%',loanAmount + < span class =code-string>' '),LEN(loanAmount)) AS INT )
FROM [dbCMSextract]。[dbo]。[tblLoanMaster_copy]
但它一直给我这个错误:
Msg 245,Level 16,State 1 ,第1行< br />
将varchar值'1.00'转换为数据类型int时转换失败。
示例数据
loanAmount:00001.00
解决方案
CAST(' 00001.00' as float )将工作
如果你需要int类型
SELECT CAST(CAST(' 00001.00' as float ) as int )
或
SELECT CAST( ROUND(' 00001.00', 0 , 1 ) AS INT )
试试这个,
SELECT Cast (Cast(SUBSTRING(' 1.00',PATINDEX('' %[^ 0]%',' 1.00' + ' '),LEN(' 1.00')) as 十进制) as Int )
或
SELECT Cast(强制转换(SUBSTRING(' 1.00',PATINDEX(' %[^ 0]%',' 1.00' + ' '),LEN(' 1.00')) as float ) as 内部跨度>)
Hi!
I have been trying to convert this column to int.
Here is my query:
SELECT CAST(SUBSTRING(loanAmount, PATINDEX('%[^0 ]%', loanAmount + ' '), LEN(loanAmount))AS INT)
FROM [dbCMSextract].[dbo].[tblLoanMaster_copy]
But it keeps returning me this error:
Msg 245, Level 16, State 1, Line 1<br /> Conversion failed when converting the varchar value '1.00' to data type int.
Sample Data
loanAmount: 00001.00
解决方案
CAST('00001.00' as float)will work
if you need int type
SELECT CAST( CAST('00001.00' as float) as int)
or
SELECT CAST(ROUND('00001.00',0,1) AS INT)
Try this ,
SELECT Cast(Cast(SUBSTRING('1.00', PATINDEX('%[^0 ]%', '1.00' + ' '), LEN('1.00')) as Decimal) as Int)
Or
SELECT Cast(Cast(SUBSTRING('1.00', PATINDEX('%[^0 ]%', '1.00' + ' '), LEN('1.00')) as float) as Int)
这篇关于如何将varchar [00001.00]转换为int [1]的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文