sql，varchar转换float出错，数据看上去没问题 数据库执行后 提示：将数据类型 varchar 转换为 fl...
SQL \u4ece\u6570\u636e\u7c7b\u578b varchar \u8f6c\u6362\u4e3a float \u65f6\u51fa\u9519\u3002SQL \u4ece\u6570\u636e\u7c7b\u578b varchar \u8f6c\u6362\u4e3a float \u65f6\u51fa\u9519\u3002
\u53ef\u4ee5\u7528where isnumeric(\u52177)=0 \u67e5\u770b\u4e00\u4e0b\u54ea\u4e9b\u4e0d\u80fd\u8f6c\u6362\u4e3a\u6570\u5b57
\u5982\u679c\u662f\u7b97\u672f\u6ea2\u51fa\uff0c\u90a3\u53ef\u4ee5\u653e\u5927decimal\u7684\u4f4d\u6570
\u539f\u56e0\uff1a\u8f6c\u6362\u9519\u8bef\u5bfc\u81f4\u3002\u89e3\u51b3\u65b9\u6cd5\uff1a\u91cd\u65b0\u67e5\u770b\u5185\u5bb9\u6765\u89e3\u51b3\u6b64\u95ee\u9898\u3002
\u5982\u4e0b\u53c2\u8003\uff1a
1.\u6253\u5f00\u6570\u636e\u5e93\u8fde\u63a5\u5ba2\u6237\u7aefNavicatPremium\uff0c\u5e76\u521b\u5efa\u4e00\u4e2a\u65b0\u7684\u8868\u7ed3\u6784\uff0c\u5176\u4e2dage\u5217\u88ab\u6545\u610f\u8bbe\u7f6e\u4e3avarchar\u3002
2.\u6210\u529f\u521b\u5efa\u8868\u4e4b\u540e\uff0c\u4e3a\u8868\u521b\u5efa\u4e00\u4e9b\u6570\u636e\uff0c\u5982\u4e0b\u6240\u793a\u3002
3.\u5f53\u6709\u5c11\u91cf\u6570\u636e\u65f6\uff0c\u53ef\u4ee5\u4f7f\u7528sum()\u51fd\u6570\u76f4\u63a5\u6c42\u548c\uff0c\u56e0\u4e3a\u5728SQL\u4e2d\u5b83\u53ef\u4ee5\u81ea\u52a8\u8bc6\u522b\u662f\u5b57\u7b26\u4e32\u7c7b\u578b\u8fd8\u662f\u6570\u5b57\u7c7b\u578b\u3002
4.\u4ee5\u4e0a\u9002\u7528\u4e8e\u6574\u6570\u3001\u4f53\u6216\u5c0f\u6570\u636e\uff0c\u5982\u679c\u6570\u636e\u592a\u5927\uff0c\u4e14\u7cbe\u5ea6\u4e0d\u9ad8\u4e0d\u9002\u7528\uff0c\u90a3\u4e48\u53ef\u4ee5\u8003\u8651\u4f7f\u7528SQLCAST()\u548cCONVERT()\u51fd\u6570\u3002
5.\u63a5\u4e0b\u6765\uff0c\u770b\u4e00\u4e0bCONVERT()\u51fd\u6570\uff0c\u5b83\u4e3b\u8981\u6ee1\u8db3\u7cbe\u5ea6\u8981\u6c42\uff0c\u5e76\u901a\u8fc7\u66f4\u6539\u539f\u59cb\u6570\u636e\u548c\u5c06age\u5217\u66f4\u6539\u4e3a\u5e26\u6709\u5c0f\u6570\u7684\u5217\u6765\u6d4b\u8bd5\u5b83\u3002
把数据先贴出来看看。
绛旓細杩欎釜閿欒閫氬父鏄洜涓哄湪灏嗕竴涓 varchar 绫诲瀷鐨勫艰浆鎹负 float 绫诲瀷鏃讹紝鏁版嵁涓寘鍚潪鏁板瓧瀛楃鎴栨牸寮忎笉姝ｇ‘鐨勬暟瀛楀瓧绗銆傛偍鍙互鍦 case 璇彞涓愪釜璋冭瘯姣忎釜鏉′欢鏉ョ‘瀹氬叿浣撶殑闂锛屼緥濡傦紝灏濊瘯鍒嗗埆鎵ц浠ヤ笅 SQL 璇彞锛歋ELECT CAST('abc' AS float) -- 閿欒锛氭棤娉曞皢瀛楃涓测渁bc鈥濊浆鎹负 float 绫诲瀷銆係ELECT CAS...
绛旓細鍘熷洜锛氳浆鎹㈤敊璇鑷銆傝В鍐虫柟娉曪細閲嶆柊鏌ョ湅鍐呭鏉ヨВ鍐虫闂銆傚涓嬪弬鑰冿細1.鎵撳紑鏁版嵁搴撹繛鎺ュ鎴风NavicatPremium锛屽苟鍒涘缓涓涓柊鐨勮〃缁撴瀯锛屽叾涓璦ge鍒楄鏁呮剰璁剧疆涓簐archar銆2.鎴愬姛鍒涘缓琛ㄤ箣鍚庯紝涓鸿〃鍒涘缓涓浜涙暟鎹紝濡備笅鎵绀恒3.褰撴湁灏戦噺鏁版嵁鏃讹紝鍙互浣跨敤sum()鍑芥暟鐩存帴姹傚拰锛屽洜涓哄湪SQL涓畠鍙互鑷姩璇嗗埆鏄瓧绗︿覆绫诲瀷...
绛旓細鍏堝皢nvarchar杞崲鎴恉ecimal,鍐嶈浆鎹㈡垚float鍨嬪氨琛屼簡锛屼緥濡:declare @vnum varchar(20)declare @fnum float --set @fnum=CONVERT(decimal(18,2),'12.34567')--nvarchar杞琭loat --set @vnum=CONVERT(varchar(20),12.34567)+'%'--float杞琻varchar set @fnum=CAST('12.3452145' AS DECIMAL(20,...
绛旓細varchar寮鸿杞寲鎴恌loat鐨勮瘽锛閭ｅ緱鐪嬬湅浣犵幇鍦ㄥ瓨鍦ㄧ殑琛ㄩ噷闈㈡槸涓嶆槸鏈変簺鏁版嵁涓嶇鍚堣浆鎴恌loat鐨勬暟鎹紝姣斿涓浜涗腑鏂囷紝瀛楁瘝锛屾垨瀛楃涔嬬被鐨銆傞鍏堝皢杩欎簺鏁版嵁鏀逛簡锛屽啀淇敼灏卞彲浠ヤ簡銆
绛旓細浣犲彲浠ュ湪鎵惧埌 宸ュ叿鈥斺旈夐」鈥斺擠esigners 鎶婇樆姝繚瀛樿姹傞噸鏂板垱寤鸿〃鐨勬洿鏀 鏃佽竟鐨勨垰鍘绘帀 涔熷彲浠ョ敤SQL璇彞鏇存敼 alter table tablename alter column cname float
绛旓細str鏄 杩斿洖浠庢暟瀛楁暟鎹杞崲鑰屾垚鐨勫瓧绗︽暟鎹傝屼綘澹版槑鐨勬槸nvarchar,骞朵笖浼犲叆鐨勫弬鏁版槸'41e620ee-b34c-499c-b0e6-fc454583e63f',鎴戞兂浣犵殑鎰忔濇槸鎯宠浆鎹㈡垚瀛楃鍨嬫暟鎹惂 灏唖tr(@StartNum) 鏇挎崲鎴恈ast (@ChannelID as nvarchar)銆俢reate table #(f float, i int)declare @f float, @i int, @sql ...
绛旓細濡傛灉浣犵殑varchar閮芥槸鏁板肩被鍨嬶紝鏄暣鍨嬪氨杞崲int锛屾槸娴偣鍨嬪氨杞崲float select sum(convert(float,filed)) from table
绛旓細濡傛灉浣犳槸鐢sql server鐨勮瘽锛岀洿鎺ュ湪SQl璇彞閲岄潰鐢╟ast灏卞彲浠杞崲銆俢ast( '43.56 ' as money)灏卞彲浠ヤ簡銆傛垨鑰呯敤convert(money, '43.56 ')涔熷彲浠
绛旓細鐢ㄥ唴缃嚱鏁癱onvert(int,浣犵殑瀛楁)銆備緥濡 select * from 浣犵殑琛ㄥ悕 order by convert(int,浣犵殑瀛楁);
绛旓細鐢╟ast(閲戦 as decimal(10,4))>500鎴栬 convert(decimal(10,4),閲戦)>500 娉細decimal鐨勯暱搴﹀拰灏忔暟浣嶆暟鏍规嵁鑷繁闇瑕佽瀹 sql涓皢瀛楃涓叉暟瀛杞崲鎴愭暟瀛楋細鏂规硶涓锛歋ELECT CAST('123' AS SIGNED integer);鏂规硶浜岋細SELECT CONVERT('123',SIGNED);鏂规硶涓夛細SELECT '123'+0;...
