一個很奇怪的問題!!!!!!!!!!!!!!!!!!!!!!!希望魚兄等人來幫下忙
我的數據庫中有一個表
select * from #t1
DESC1
----------------------------------------
底蓋 不銹鋼 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數據庫為
英文的
[解决办法]
try
select * from #t1 where patindex(N '%外箱% ',DESC1) <> 0
[解决办法]
看下排序规则是什么，包括本身库的排序规则和tempdb的排序规则
[解决办法]
有點奇怪
[解决办法]
create table #t2
(
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 #t2
select * from #t2 where patindex(N '%外箱% ',desc0) <> 0
drop table #t2
