遇到SQL Server中因為欄位Collation分別設為Width-Sensitive與Width-Insensitive而衍生問題,之前約略知道它與全型半型有關,今天才認真地用實驗驗證一下WS與WI的區別。建一個TABLE,ColWS設為Width-Sensitive,ColWI則設為Width-Insensitive,並分別塞入半型123及全型123。

CREATE TABLE [dbo].[WSTest](
[ColWS] [nchar](10) COLLATE Chinese_Taiwan_Stroke_CI_AS_WS NULL,
[ColWI] [nchar](10) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
INSERT INTO WSTest VALUES ('123','123')
INSERT INTO WSTest VALUES ('123','123')

接著試試以下的兩個SELECT,就可看出二者有什麼不同了。

SELECT * FROM WSTest WHERE ColWS='123'

ColWS      ColWI
---------- ----------
123        123

(1 row(s) affected)

SELECT * FROM WSTest WHERE ColWI='123'

ColWS      ColWI
---------- ----------
123        123
123      123

(2 row(s) affected)


Comments

Be the first to post a comment

Post a comment


59 + 24 =