摘要:用DATALENGTH找出VARCHAR欄位中含有中文字的資料列
--## 可以用DATALENGTH找出VARCHAR欄位中含有中文字的資料列
----------------------------------------------------------------------------------------------
SELECT * FROM TABLE_NAME
WHERE LEN(USERID) <> DATALENGTH(USERID)
--## 飯粒
----------------------------------------------------------------------------------------------
CREATE TABLE #T
(
[TEXT] TEXT,
[NTEXT] NTEXT,
[VARCHAR] VARCHAR(30)
)
GO
INSERT INTO #T VALUES('123abcㄅㄆㄇ机楞撒', '123abcㄅㄆㄇ机楞撒', '123abcㄅㄆㄇ机楞撒');
GO
SELECT
--CAST ([TEXT] AS VARCHAR) AS 'TEXT(VARCHAR)',
LEN(CAST ([TEXT] AS VARCHAR)) AS 'LEN_TEXT(VARCHAR)',
DATALENGTH([TEXT]) AS 'DATALEN_TEXT',
LEN(CAST([NTEXT] AS NVARCHAR)) AS 'LEN_NTEXT(NVARCHAR)',
DATALENGTH([NTEXT]) AS 'DATALEN_NTEXT',
LEN([VARCHAR]) AS 'LEN_VARCHAR',
DATALENGTH([VARCHAR]) AS 'DATALEN_VARCHAR'
FROM #T;
GO
DROP TABLE #T;
GO
LEN_TEXT(VARCHAR) DATALEN_TEXT LEN_NTEXT(NVARCHAR) DATALEN_NTEXT LEN_VARCHAR DATALEN_VARCHAR
----------------- ------------ ------------------- ------------- ----------- ---------------
12 18 12 24 12 18
