SQL Server 定序衝突, 字集定序, 字串比較

compare_string_strictsly.sql

-- 參考: https://blog.xuite.net/zidane741121/process/21010412
-- SQL Server中全形和半形字符的比較問題

-- //※ 指定字集定序讓全形半形與大寫小寫相異。

declare @Input1 varchar(50) = 'A12345'; -- Pass
select 1 where 'A12345' =　@Input1 collate Chinese_PRC_CS_AS_WS

declare @Input2 varchar(50) = 'a12345' -- Not Pass
select 1 where 'A12345' =　@Input2 collate Chinese_PRC_CS_AS_WS

declare @Input3 varchar(50) = 'Ａ１２３４５'; -- Not Pass
select 1 where 'A12345' =　@Input3 collate Chinese_PRC_CS_AS_WS

declare @userID varchar(50) = 'A12345';
-- declare @userID varchar(50) = 'a12345';
-- declare @userID varchar(50) = 'Ａ１２３４５';'
SELECT TOP 1 * FROM UserInfo WHERE UserID = @UserID COLLATE Chinese_PRC_CS_AS_WS;
SELECT TOP 1 * FROM UserInfo WHERE UserID = @UserID;

SQL Server 定序衝突.sql

--參考自:http://blog.xuite.net/dlnk/work/38263590-SQL+Server+%E5%AE%9A%E5%BA%8F%E8%A1%9D%E7%AA%81+

--SQL Server 定序衝突 無法解析equal to 作業中"Chinese_Taiwan_Stroke_CI_AS"

--只要將定序不統一的欄位用COLLATE轉換就可以了

select 
  AutoId,
  Account,
  CharName,
  p.szItemID,
  ItemPrice,
  CreateDate,
  [status],
  (ItemName collate Chinese_Taiwan_Stroke_CI_AS) AS ItemName
 from 
  Tbl_BuyItemLog p
 inner join 
  ItemList q on p.szItemID = (q.szItemID collate Chinese_Taiwan_Stroke_BIN)