SQL Server 查詢&處理含有回車符&換行符的內(nèi)容
--bwk_sjc_goal中的wareid是從Excel中直接復(fù)制過(guò)來(lái)的(單個(gè)復(fù)制&沒(méi)有雙擊進(jìn)去復(fù)制) --導(dǎo)致含有回車&換行符。查詢的時(shí)候如果直接寫(xiě)等于(='0000024'),則查詢結(jié)果為空 select * from bwk_sjc_goal declare @wareid varchar(10)='0000024'+char(13)+char(10) select g.wareid,u.userid,'',g.goal,'' from bwk_sjc_goal g cross join c_user u where u.status = '01' and u.orgcode_power <> '0000' and g.wareid = @wareid --and g.wareid = '0000024' --結(jié)果為空 order by g.wareid,u.userid
刪除回車&換行符
UPDATE bwk_sjc_goal SET wareid = REPLACE(wareid, CHAR(13) + CHAR(10), '') WHERE wareid LIKE '%' + CHAR(13) + CHAR(10) + '%';
字符前面的“空格”
有時(shí)候查詢結(jié)果中,最前面看起來(lái)好像有一個(gè)空格,但是使用 ltrim 函數(shù)處理后,結(jié)果還是沒(méi)有變化,然后使用 ascii 函數(shù)查詢,結(jié)果為10.
select top 1 ASCII(left(LTRIM(associated_wareid),1)) from temp_124 where associated_wareid like '%1005101%' -- 1005101 --結(jié)果:10
處理方式:
select top 1 replace(associated_wareid,char(10),'') from temp_124 where associated_wareid like '%1005101%'
為了保險(xiǎn)起見(jiàn),把char(13)也處理一下:
REPLACE(REPLACE(wareid,char(10),''),char(13),'') as wareid

浙公網(wǎng)安備 33010602011771號(hào)