問(wèn)題描述
我希望這對(duì) SQL 專家來(lái)說(shuō)是一個(gè)有趣的謎題.
I hope this is an interesting puzzle for an SQL expert out there.
當(dāng)我運(yùn)行以下查詢時(shí)，我希望它不會(huì)返回任何結(jié)果.
When I run the following query, I would expect it to return no results.
-- Create a table variable Note: This same behaviour occurs in standard tables.
DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)
-- Add some test data Note: Without space, space prefix and space suffix
INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')
-- SELECT statement that is filtered by a value without a space and also a value with a space suffix
SELECT
t.Foo
, t.About
FROM @TestResults t
WHERE t.Foo like 'Bar '
AND t.Foo like 'Bar'
AND t.Foo = 'Bar '
AND t.Foo = 'Bar'
結(jié)果返回單行:
[Foo] [About]
Bar Space Suffix
問(wèn)題是人們正在從電子郵件等中復(fù)制和粘貼值，并且他們以某種方式進(jìn)入表格.我正在將此作為一個(gè)單獨(dú)的問(wèn)題進(jìn)行研究，因?yàn)槲覍?LTRIM(RTRIM(Foo)) 作為 INSERT 和 UPDATE 觸發(fā)器，但有些正在以某種方式通過(guò)網(wǎng)絡(luò).
The issue is that people are copying and pasting values from emails etc. and they're getting into the table somehow. I am looking into this as a separate issue as I am LTRIM(RTRIM(Foo)) as an INSERT and UPDATE trigger, but some are getting through the net somehow.
我需要更多地了解這種行為以及我應(yīng)該如何解決它.
I need to know more about this behaviour and how I should work around it.
還值得注意的是 LEN(Foo) 也是奇數(shù)，如下:
It is also worth noting that LEN(Foo) is odd too, as follows:
DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)
INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')
SELECT
t.Foo
, LEN(Foo) [Length]
, t.About
FROM @TestResults t
給出以下結(jié)果:
[Foo] [Length] [About]
Bar 3 No spaces
Bar 3 Space Suffix
Bar 4 Space prefix
沒(méi)有任何橫向思考，我需要將 WHERE 子句更改為什么才能按預(yù)期返回 0 結(jié)果?
Without any lateral thinking, what do I need to change my WHERE clause to in order to return 0 results as expected?
推薦答案
答案是添加以下子句:
AND DATALENGTH(t.Foo) = DATALENGTH('Bar')
運(yùn)行以下查詢...
DECLARE @Chars TABLE (CharNumber INT NOT NULL)
DECLARE @CharNumber INT = 0
WHILE(@CharNumber <= 255)
BEGIN
INSERT INTO @Chars(CharNumber) VALUES(@CharNumber)
SET @CharNumber = @CharNumber + 1
END
SELECT
CharNumber
, IIF('Test' = 'Test' + CHAR(CharNumber),1,0) ['Test' = 'Test' + CHAR(CharNumber)]
, IIF('Test' LIKE 'Test' + CHAR(CharNumber),1,0) ['Test' LIKE 'Test' + CHAR(CharNumber)]
, IIF(LEN('Test') = LEN('Test' + CHAR(CharNumber)),1,0) [LEN('Test') = LEN('Test' + CHAR(CharNumber))]
, IIF(DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber)),1,0) [DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber))]
FROM @Chars
WHERE ('Test' = 'Test' + CHAR(CharNumber))
OR ('Test' LIKE 'Test' + CHAR(CharNumber))
OR (LEN('Test') = LEN('Test' + CHAR(CharNumber)))
ORDER BY CharNumber
...產(chǎn)生以下結(jié)果...
...produces the following results...
CharNumber 'Test' = 'Test' + CHAR(CharNumber) 'Test' LIKE 'Test' + CHAR(CharNumber) LEN('Test') = LEN('Test' + CHAR(CharNumber)) DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber))
0 1 1 0 0
32 1 0 1 0
37 0 1 0 0
DATALENGTH 可用于測(cè)試兩個(gè) VARCHAR 的相等性，因此可以按如下方式更正原始查詢:
DATALENGTH can be used to test the equality of two VARCHAR, therefore the original query can be corrected as follows:
-- Create a table variable Note: This same behaviour occurs in standard tables.
DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)
-- Add some test data Note: Without space, space prefix and space suffix
INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')
-- SELECT statement that is filtered by a value without a space and also a value with a space suffix
SELECT
t.Foo
, t.About
FROM @TestResults t
WHERE t.Foo like 'Bar '
AND t.Foo like 'Bar'
AND t.Foo = 'Bar '
AND t.Foo = 'Bar'
AND DATALENGTH(t.Foo) = DATALENGTH('Bar') -- Additional clause
我也做了一個(gè)函數(shù)來(lái)代替=
I also made a function to be used instead of =
ALTER FUNCTION dbo.fVEQ( @VarCharA VARCHAR(MAX), @VarCharB VARCHAR(MAX) )
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
-- Added by WonderWorker on 18th March 2020
DECLARE @Result BIT = IIF(
(@VarCharA = @VarCharB AND DATALENGTH(@VarCharA) = DATALENGTH(@VarCharB))
, 1, 0)
RETURN @Result
END
..這是對(duì)用作尾隨字符的所有 256 個(gè)字符的測(cè)試，以證明它有效..
..Here is a test for all 256 characters used as trailing characters to prove that it works..
-- Test fVEQ with all 256 characters
DECLARE @Chars TABLE (CharNumber INT NOT NULL)
DECLARE @CharNumber INT = 0
WHILE(@CharNumber <= 255)
BEGIN
INSERT INTO @Chars(CharNumber) VALUES(@CharNumber)
SET @CharNumber = @CharNumber + 1
END
SELECT
CharNumber
, dbo.fVEQ('Bar','Bar' + CHAR(CharNumber)) [fVEQ Trailing Char Test]
, dbo.fVEQ('Bar','Bar') [fVEQ Same test]
, dbo.fVEQ('Bar',CHAR(CharNumber) + 'Bar') [fVEQ Leading Char Test]
FROM @Chars
WHERE (dbo.fVEQ('Bar','Bar' + CHAR(CharNumber)) = 1)
AND (dbo.fVEQ('Bar','Bar') = 0)
AND (dbo.fVEQ('Bar',CHAR(CharNumber) + 'Bar') = 1)
這篇關(guān)于在 VARCHAR 中使用尾隨空格 SQL Server SELECT 時(shí)的未記錄功能的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！