SQL案例当检查地址中的空格时

Question

我正在努力确保如果一个地址行中输入了三个以上的空格，那么它就会被标记，但到目前为止我还没有成功。有人能告诉我如何避开这个吗？如果这是个愚蠢的问题，我很抱歉。

以下是无法验证这一点的代码(我尝试过的代码(：

case when c.cAddress1 LIKE '%[ ]%[ ]%[ ]%' 
THEN 'Data Appears Ok'
when c.cAddress1 LIKE '%[ ]%[ ]%'
THEN 'Data Appears Ok'
when c.cAddress1 LIKE '%[ ]%' 
THEN 'Data Appears Ok'
when c.cAddress1 IS NULL
THEN 'Empty'
when c.cAddress1 LIKE '%'
THEN 'Data Appears Ok'
ELSE 'Check AddressLine1 for Comments'
END as [Addressline1 Comment Validation],

Answer 1

如果您需要空格字符计数，请使用此代码并决定它是否对您有效：

DECLARE @table TABLE
(
Address1 NVARCHAR(1000)
);
INSERT @table
(
Address1
)
VALUES
('Part1 Part2 Part3 Part4'),
('Part1 Part2 Part3');
SELECT t.Address1, parts.partCount - 1 AS [Space character]
FROM    @table AS t
OUTER APPLY (SELECT COUNT(1) partCount FROM STRING_SPLIT(t.Address1, ' ') AS ss) parts