我的數據庫中有一個表select * from #t1DESC1
----------------------------------------
底蓋 不銹鋼 C-375
外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "
紙箱 單坑 B33 13-1/4*4-1/8*7-1/8"
外箱 雙坑 A=B 17-1/8*15-7/8*6-3/8 "
紙箱 單坑 B33 16-5/8*5*5-5/8"
單平咭B3B 12-7/8*3-7/8"
單平咭B3B 6-3/4*3-7/8"
單平咭B3B 16-1/8*4-5/8"
單平咭B3B 4-5/8*5-1/8"
外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "(10 row(s) affected)但是,
select * from #t1 where patindex('%外箱%',DESC1)<>0
很明顯是有結果的,但是
結果是:
DESC1
---------------------------------------- (0 row(s) affected)
我測試了一下SELECT PATINDEX ('%外箱%','外箱外箱')結果:-----------
1(1 row(s) affected)後來,我為了進一測試,我把desc0 改為varchar(1000)
create table #t2
(
desc0 nvarchar(1000) collate Latin1_General_BIN)
insert into #t2
select '張鴻亮C-375' union all
select '外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "' union all
select '紙箱 單坑 B33 13-1/4*4-1/8*7-1/8"' union all
select '外箱 雙坑 A=B 17-1/8*15-7/8*6-3/8 "' union all
select '紙箱 單坑 B33 16-5/8*5*5-5/8"' union all
select '單平咭B3B 12-7/8*3-7/8"' union all
select '單平咭B3B 6-3/4*3-7/8"' union all
select '單平咭B3B 16-1/8*4-5/8"' union all
select '單平咭B3B 4-5/8*5-1/8"' union all
select '外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "'select * from #t2結果:
???C-375
?? ?? A=B 17-3/8*13-3/4*7-7/8 "
?? ?? B33 13-1/4*4-1/8*7-1/8"
?? ?? A=B 17-1/8*15-7/8*6-3/8 "
?? ?? B33 16-5/8*5*5-5/8"
???B3B 12-7/8*3-7/8"
???B3B 6-3/4*3-7/8"
???B3B 16-1/8*4-5/8"
???B3B 4-5/8*5-1/8"
?? ?? A=B 17-3/8*13-3/4*7-7/8 "(10 row(s) affected)這些繁體字都變成???了這是怎么回事啊,在表#t1中desc0 為text 型,我的系統也是繁體系統,sql數據庫為
英文的
----------------------------------------
底蓋 不銹鋼 C-375
外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "
紙箱 單坑 B33 13-1/4*4-1/8*7-1/8"
外箱 雙坑 A=B 17-1/8*15-7/8*6-3/8 "
紙箱 單坑 B33 16-5/8*5*5-5/8"
單平咭B3B 12-7/8*3-7/8"
單平咭B3B 6-3/4*3-7/8"
單平咭B3B 16-1/8*4-5/8"
單平咭B3B 4-5/8*5-1/8"
外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "(10 row(s) affected)但是,
select * from #t1 where patindex('%外箱%',DESC1)<>0
很明顯是有結果的,但是
結果是:
DESC1
---------------------------------------- (0 row(s) affected)
我測試了一下SELECT PATINDEX ('%外箱%','外箱外箱')結果:-----------
1(1 row(s) affected)後來,我為了進一測試,我把desc0 改為varchar(1000)
create table #t2
(
desc0 nvarchar(1000) collate Latin1_General_BIN)
insert into #t2
select '張鴻亮C-375' union all
select '外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "' union all
select '紙箱 單坑 B33 13-1/4*4-1/8*7-1/8"' union all
select '外箱 雙坑 A=B 17-1/8*15-7/8*6-3/8 "' union all
select '紙箱 單坑 B33 16-5/8*5*5-5/8"' union all
select '單平咭B3B 12-7/8*3-7/8"' union all
select '單平咭B3B 6-3/4*3-7/8"' union all
select '單平咭B3B 16-1/8*4-5/8"' union all
select '單平咭B3B 4-5/8*5-1/8"' union all
select '外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "'select * from #t2結果:
???C-375
?? ?? A=B 17-3/8*13-3/4*7-7/8 "
?? ?? B33 13-1/4*4-1/8*7-1/8"
?? ?? A=B 17-1/8*15-7/8*6-3/8 "
?? ?? B33 16-5/8*5*5-5/8"
???B3B 12-7/8*3-7/8"
???B3B 6-3/4*3-7/8"
???B3B 16-1/8*4-5/8"
???B3B 4-5/8*5-1/8"
?? ?? A=B 17-3/8*13-3/4*7-7/8 "(10 row(s) affected)這些繁體字都變成???了這是怎么回事啊,在表#t1中desc0 為text 型,我的系統也是繁體系統,sql數據庫為
英文的
tryselect * from #t1 where patindex(N'%外箱%',DESC1)<>0----------------------------------
加了這句也不行啊,後來我把DESC1 TEXT 型轉為nvarchar(1000)字體都為亂碼了,只能轉為varchar(1000)這種類型
看下排序规则是什么,包括本身库的排序规则和tempdb的排序规则
-------------------
排序規則為Latin1_General_BIN
(
desc0 nvarchar(1000) collate Latin1_General_BIN)
insert into #t2
select N'張鴻亮C-375' union all
select N'外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "' union all
select N'紙箱 單坑 B33 13-1/4*4-1/8*7-1/8"' union all
select N'外箱 雙坑 A=B 17-1/8*15-7/8*6-3/8 "' union all
select N'紙箱 單坑 B33 16-5/8*5*5-5/8"' union all
select N'單平咭B3B 12-7/8*3-7/8"' union all
select N'單平咭B3B 6-3/4*3-7/8"' union all
select N'單平咭B3B 16-1/8*4-5/8"' union all
select N'單平咭B3B 4-5/8*5-1/8"' union all
select N'外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "'select * from #t2select * from #t2 where patindex(N'%外箱%',desc0)<>0drop table #t2
select N'張鴻亮C-375' union all ---这个地方错了,在insert的时候前面也要加大写的 N,就不会有乱码了, 下面的都是。
select N'外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "' union all
select N'紙箱 單坑 B33 13-1/4*4-1/8*7-1/8"' union all
select N'外箱 雙坑 A=B 17-1/8*15-7/8*6-3/8 "' union all
select N'紙箱 單坑 B33 16-5/8*5*5-5/8"' union all
select N'單平咭B3B 12-7/8*3-7/8"' union all
select N'單平咭B3B 6-3/4*3-7/8"' union all
select N'單平咭B3B 16-1/8*4-5/8"' union all
select N'單平咭B3B 4-5/8*5-1/8"' union all
select N'外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "'
外箱 雙坑 A=B 17-1/8*15-7/8*6-3/8 "
外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "(3 row(s) affected)但這是個測試表啊,我實際表中#t1 desc 為text 型,select * from #t1 where patindex(N'%外箱%',desc0)<>0它還是沒有結果啊
-------------
text改為Ntext試下看
select N'張鴻亮C-375' union all
select N'外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "' union all
select N'紙箱 單坑 B33 13-1/4*4-1/8*7-1/8"' union all
select N'外箱 雙坑 A=B 17-1/8*15-7/8*6-3/8 "' union all
select N'紙箱 單坑 B33 16-5/8*5*5-5/8"' union all
select N'單平咭B3B 12-7/8*3-7/8"' union all
select N'單平咭B3B 6-3/4*3-7/8"' union all
select N'單平咭B3B 16-1/8*4-5/8"' union all
select N'單平咭B3B 4-5/8*5-1/8"' union all
select N'外箱 雙坑 A=B 17-3/8*13-3/4*7-7/8 "'select * from #t2select * from #t2 where patindex(N'%外箱%',desc0)<>0drop table #t2
WHERE patindex(N'%美國%',DES)<>0UN_ITEM1為實際表,可這樣還是不行啊,只要是查找中文字體,就是空的,如查字母,不要轉成
ntext,都有結果,sql好象不認中文似的
WHERE patindex(N'%美國%',CAST(DES AS ntext))<>0不行的話,直接在表上修改,將類型改為Ntext再試select * from #t1 where patindex(N'%外箱%',desc0)<>0
" 這樣插入數據啊,有沒有辦法把我數據庫中的數據孌成前面加'N',讓sql識別中文字體真的要瘋了
CAST(DES AS ntext)as DES ,就變成亂碼了