Sql-Server
嘗試檢查字元串是否僅包含數字
我一直在嘗試編寫一個函式來檢查字元串是否包含一個數字，而該數字不是更大數字的一部分（換句話說，如果要搜尋的數字是'6’並且字元串是'7+16+2’它應該返回 false，因為這個字元串中的 ‘6’ 是數字 ‘16’ 的一部分）
我寫了下面的函式（它很長，但我會在重構之前先測試它）
經過測試，我發現了一個錯誤，它僅通過邏輯執行找到的數字的第一個實例。因此，使用 ‘6’ 對 ‘16+7+9+6’ 執行此函式將返回 false，因為它確定第一個 ‘6’ 是更大數字的一部分並停止處理。
我認為要解決這個問題，我必須實現一個循環來縮短 ‘haystack’ 字元串（這樣，使用範例 ‘16+7+9+6’ 函式在消除後繼續檢查 ‘+7+9+6’第一個'6’）但在花時間使已經復雜的功能更加複雜之前，我想檢查是否有更簡單的方法來實現相同的目標？
drop function dbo.runners_contain_runner go create function dbo.runners_contain_runner(@runner varchar(max), @runners varchar(max)) returns int as begin /* eliminate the plus sign from @runners so that the 'isnumeric' function doesn't return false positives (it returns 1 for '+') */ set @runners = replace(@runners,'+','_' ) declare @ret int; set @ret = 0; -- if the runner is the only runner return 1 if @runners = @runner set @ret = 1 else begin declare @charindex int; set @charindex = charindex(@runner,@runners) if @charindex > 0 begin -- if it is at the beginning then check the char after it if @charindex = 1 begin if isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0 set @ret = @charindex end -- if it is at the end then check the char before it else if @charindex = len(@runners) - (len(@runner) - 1) begin if isnumeric(substring(@runners,@charindex - 1,1)) = 0 set @ret = @charindex end -- if it is in the middle check the chars either side of it else begin if isnumeric(substring(@runners,@charindex - 1,1)) + isnumeric(substring(@runners,@charindex + len(@runner),1)) = 0 set @ret = @charindex end end end return @ret end
也許您過於關注想要一個數字，從而使問題變得過於復雜。退後一步。你真正想要的是一個兩邊都沒有任何數字的子字元串。一個數字可以成為更大數字的一部分的唯一方法是在它的兩側至少有一個數字，對嗎？所以只要你只傳入數字，那麼這個定義仍然應該產生兩邊都沒有任何數字的數字。
考慮到這一點，我們只需要 3 個
PATINDEX謂詞來覆蓋傳入的值在最左邊、最右邊或中間。嘗試以下方法，因為它似乎有效：GO CREATE PROCEDURE #TestFindRunner ( @Runner VARCHAR(10) ) AS SET NOCOUNT ON; DECLARE @Data TABLE ( [ID] INT NOT NULL PRIMARY KEY, [Runners] VARCHAR(50) NULL ); INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6'); INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5'); INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5'); INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45'); INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92'); INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6'); INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7'); INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63'); INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7'); INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL); INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6'); SELECT tmp.* FROM @Data tmp WHERE @Runner COLLATE Latin1_General_100_BIN2 = tmp.[Runners] OR PATINDEX('%[^0123456789]' + @Runner COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0 OR PATINDEX(@Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0 OR PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2, tmp.[Runners]) > 0 GO然後測試：
EXEC #TestFindRunner 0; EXEC #TestFindRunner 2; EXEC #TestFindRunner 4; EXEC #TestFindRunner 8; EXEC #TestFindRunner 11; -- 0 rows EXEC #TestFindRunner 3; -- 4 EXEC #TestFindRunner 77; -- 3 EXEC #TestFindRunner 111; -- 5 -- 1 row EXEC #TestFindRunner 5; -- 2 and 3 -- 2 rows EXEC #TestFindRunner 1; -- 6, 7, 8, and 9 -- 4 rows EXEC #TestFindRunner 6; -- 1, 4, 6, 7, and 11 -- 5 rows EXEC #TestFindRunner 7; -- 1, 2, 6, 7, 8, and 9 -- 6 rows EXEC #TestFindRunner 9; -- 1, 2, 3, 6, 7, 8, and 9 -- 7 rows有 3 個變體的原因
PATINDEX是PATINDEX搜尋模式不是正則表達式 (RegeEx)，這與許多人所說/認為的相反（與LIKE模式相同）。PATINDEX並且LIKE模式沒有量詞，因此無法指定[^0123456789]單個字元替換應為“0 或更多”；它是“一個，只有一個；不多也不少”。強制二進制排序規則（即
COLLATE Latin1_General_100_BIN2在每個@Runner引用之後）確保我們只處理這 10 個十進制數字，而不是任何其他可能被視為等效的字元要將上述邏輯放入內聯表值函式 (TVF) 中，使其更易於使用（並且比類似易於使用的標量 UDF 更高效），請嘗試以下操作：
USE [tempdb]; GO CREATE FUNCTION dbo.IsRunnerPresent ( @Runner VARCHAR(10), @Runners VARCHAR(8000) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT CONVERT(BIT, CASE WHEN @Runner COLLATE Latin1_General_100_BIN2 = @Runners OR PATINDEX('%[^0123456789]' + @Runner COLLATE Latin1_General_100_BIN2, @Runners) > 0 OR PATINDEX(@Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2, @Runners) > 0 OR PATINDEX('%[^0123456789]' + @Runner + '[^0123456789]%' COLLATE Latin1_General_100_BIN2, @Runners) > 0 THEN 1 ELSE 0 END) AS [RunnerFound]; GO然後測試：
DECLARE @Runner VARCHAR(10); SET @Runner = '6'; DECLARE @Data TABLE ( [ID] INT NOT NULL PRIMARY KEY, [Runners] VARCHAR(50) NULL ); INSERT INTO @Data ([ID], [Runners]) VALUES (1, '16+7+9+6'); INSERT INTO @Data ([ID], [Runners]) VALUES (2, '16+7+9+5'); INSERT INTO @Data ([ID], [Runners]) VALUES (3, '26+77+9+5'); INSERT INTO @Data ([ID], [Runners]) VALUES (4, '6+3+45'); INSERT INTO @Data ([ID], [Runners]) VALUES (5, '63,808,111,92'); INSERT INTO @Data ([ID], [Runners]) VALUES (6, '1-7-9,6'); INSERT INTO @Data ([ID], [Runners]) VALUES (7, '1-6-9,7'); INSERT INTO @Data ([ID], [Runners]) VALUES (8, '1-7-9,63'); INSERT INTO @Data ([ID], [Runners]) VALUES (9, '1-63-9,7'); INSERT INTO @Data ([ID], [Runners]) VALUES (10, NULL); INSERT INTO @Data ([ID], [Runners]) VALUES (11, '6'); SELECT tmp.[ID], tmp.[Runners], fnd.[RunnerFound] FROM @Data tmp CROSS APPLY dbo.IsRunnerPresentTVF(@Runner, tmp.[Runners]) fnd;返回：
ID Runners RunnerFound 1 16+7+9+6 1 2 16+7+9+5 0 3 26+77+9+5 0 4 6+3+45 1 5 63,808,111,92 0 6 1-7-9,6 1 7 1-6-9,7 1 8 1-7-9,63 0 9 1-63-9,7 0 10 NULL 0 11 6 1