将varchar值''）'转换为数据类型int时转换失败

本文介绍了将varchar值''）'转换为数据类型int时转换失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我在下面有一个查询，其中有out count函数。我得到了答案，如果使用count函数，我得到了这个错误---->转换失败时将varchar值''）'转换为数据类型int 。 请解雇我.. 选择 ' INSERT INTO CM_ONTHEFLYCOMMUNICATION（c_source，c_classification，c_name，c_email，c_mobile，c_bookscount） values （''' + ' Library' + ' ''，''' + isnull（' CheckOut - ' + cio_branchname，' '）+ ' ''，''' + ISNULL（cio_membername， ' '）+ ' ''，''' + ISNULL（mstmember.m_email1，' '）+ ' ''，''' + ISNULL（mstmember.m_mobile，' '）+ ' ''，''' + count（trncheckinout.cio_bookname ）+ ' '''' AS MAILQRY 来自 trncheckinout inner join mstmember on cio_mrecid = m_recid 其中 CIO_CheckOutDt = 20141208 group by m_email1，m_mobile，cio_branchname，cio_membername 解决方案 问题是字符串comcatenation使用相同的运算符另外： + 。如果其中一个操作数是一个数字SQL服务器假定它是添加并尝试将其余的转换为数字。所以你需要的是将count的结果转换为varchar： 选择 ' INSERT INTO CM_ONTHEFLYCOMMUNICATION（c_source，c_classification，c_name，c_email，c_mobile，c_bookscount） values（''Library''，'''' + isnull（' CheckOut - ' + cio_branchname，' '）+ ' ' '，''' + ISNULL（cio_membername，' '）+ ' ''，''' + ISNULL（mstmember.m_email1，' '）+ ' ' '，''' + ISNULL（mstmember.m_mobile，' '）+ ' ''，''' + cast（count（trncheckinout.cio_bookname） as varchar （ 100 ））+ ' '''' AS MAILQRY 来自 trncheckinout 内部 join mstmember on cio_mrecid = m_recid 其中 CIO_CheckOutDt = 20141208 group by m_email1，m_mobile，cio_branchname，cio_membername 我冒昧地删除第一个操作数'Library'周围的连接。 exec th e低于查询。 插入 进入 CM_ONTHEFLYCOMMUNICATION（c_source，c_classification，c_name，c_email，c_mobile，c_bookscount） 选择 ' Library' ，' CheckOut - ' + cio_branchname ，cio_membername ，mstmember.m_email1 ，mstmember.m_mobile ，count（trncheckinout.cio_bookname） 来自 trncheckinout inner join mstmember on cio_mrecid = m_recid where CIO_CheckOutDt = 20141208 group by m_email1，m_mobile，cio_bra nchname，cio_membername I have a query below , in that with out count function .i got answer,if using count function ,i got this error ---->Conversion failed when converting the varchar value '')' to data type int.Please crack me out..select 'INSERT INTO CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount) values ( '''+'Library'+''','''+isnull('CheckOut -'+cio_branchname,'')+''', '''+ISNULL(cio_membername,'')+''','''+ISNULL(mstmember.m_email1,'')+''', '''+ISNULL(mstmember.m_mobile,'')+''','''+count(trncheckinout.cio_bookname)+''')' AS MAILQRY from trncheckinout inner join mstmember on cio_mrecid=m_recid where CIO_CheckOutDt = 20141208 group by m_email1,m_mobile, cio_branchname,cio_membername 解决方案 The problem is string comcatenation is using the same operator as addition: +. If one of the operands is a number SQL server assumes it's addition and tries to convert the rest to numbers too. So what you need is to convert result of count to varchar:select 'INSERT INTO CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount) values ( ''Library'','''+isnull('CheckOut -'+cio_branchname,'')+''', '''+ISNULL(cio_membername,'')+''','''+ISNULL(mstmember.m_email1,'')+''', '''+ISNULL(mstmember.m_mobile,'')+''','''+cast(count(trncheckinout.cio_bookname) as varchar(100))+''')' AS MAILQRY from trncheckinout inner join mstmember on cio_mrecid=m_recid where CIO_CheckOutDt = 20141208 group by m_email1,m_mobile, cio_branchname,cio_membernameI took the liberty to remove the concatenation around the first operand 'Library'.exec the below query.insert into CM_ONTHEFLYCOMMUNICATION (c_source,c_classification,c_name,c_email,c_mobile,c_bookscount)Select 'Library','CheckOut -'+cio_branchname,cio_membername,mstmember.m_email1,mstmember.m_mobile,count(trncheckinout.cio_bookname)fromtrncheckinout inner joinmstmember on cio_mrecid=m_recid where CIO_CheckOutDt = 20141208group by m_email1,m_mobile, cio_branchname,cio_membername 这篇关于将varchar值''）'转换为数据类型int时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！