用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


原始文章出自於此