我正在使用以下 SQL 創建一個洗掉字符或數字的函式。
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
@String NVARCHAR(MAX),
@MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @MatchExpression = '%[' @MatchExpression ']%'
WHILE PatIndex(@MatchExpression, @String) > 0
SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END
我這樣呼叫它來提取字母字符，然后將數字字符提取到兩個排序欄位中：
SELECT
...
(SELECT dbo.fn_StripCharacters(PD.District, '^a-z')) AS Sort1,
CAST((SELECT dbo.fn_StripCharacters(PD.District, '^0-9')) AS INT) AS Sort2,
...
FROM
我正在搜索很多 Postcode District Records 并且對該函式的不斷呼叫導致了相對較大的延遲。有沒有辦法在沒有函式呼叫的情況下重現這個功能？它可以以某種方式合并到 SELECT 陳述句中嗎？
SQL Server 版本是 2017
uj5u.com熱心網友回復：
你可以嘗試這樣的事情：
Create Function [dbo].[fnCleanString] (
@inputString varchar(8000)
, @stringPattern varchar(50) = '[0-9a-zA-Z]'
)
Returns table
With schemabinding
As
Return
With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, iTally (n)
As (
Select Top (len(@inputString))
checksum(row_number() over(Order By @@spid))
From t t1, t t2, t t3 -- 8000 rows
)
Select v.inputString
, outputString = (Select substring(v.inputString, it.n, 1)
From iTally it
Where substring(v.inputString, it.n, 1) Like @stringPattern
For xml Path(''), Type).value('.', 'varchar(8000)')
From (Values (@inputString)) As v(inputString);
GO
并這樣稱呼它：
Declare @testData table (AlphaNumeric varchar(100));
Insert Into @testData (AlphaNumeric)
Values ('a1b2c3d4'), ('5e6f7g8i');
Select *
From @testData td
Cross Apply dbo.fnCleanString(td.AlphaNumeric, '[^A-Z]') cs
Cross Apply dbo.fnCleanString(td.AlphaNumeric, '[^0-9]') cs2;
這適用于 VARCHAR - 但如果這是您真正需要的，您可以輕松地將其更改為回傳 NVARCHAR。請注意，它是為使用固定長度的字串而撰寫的，并且 NVARCHAR 的最大大小為 4000。
您還需要了解整理以及這將如何影響結果。
uj5u.com熱心網友回復：
如何在沒有函式呼叫的情況下從 SELECT 陳述句中的字串中提取字母或數字？
你不能，因為簡單的字串操作REPLACE()也是函式呼叫。并且，用戶定義的函式由 SQL Server 編譯。他們的表現相當不錯。
但是您的問題不是函式的性能本身，而是您必須使用它的頻率。你知道的。
這是加快郵編處理任務的一種可能方法：將持久化的計算列放在您的表上。你甚至可以索引它們。
這是如何做到這一點。
告訴 SQL Server 對存盤的函式使用架構系結。它需要知道模式中的表定義取決于函式。為此，請添加
WITH SCHEMABINDING到您的函式定義中。... RETURNS NVARCHAR(MAX) WITH SCHEMABINDING AS BEGIN ...將兩個計算的、持久的列添加到您的表中。
... ALTER TABLE postcode ADD letters AS (dbo.fn_StripCharacters(postcode, '^A-Z')) PERSISTED; ALTER TABLE dbo.postcode ADD numbers AS (CAST(dbo.fn_StripCharacters(postcode, '^0-9') AS INT)) PERSISTED;如果需要，您可以在計算列上放置索引。
CREATE INDEX numbers ON postcode (numbers DESC)現在，您可以根據需要插入、更新或洗掉非計算列。在插入或更新每一行時，SQL Server 只對存盤的函式進行一次評估。您的函式仍會得到評估，但不會在您從表中選擇時進行評估。
您可以根據需要使用計算列
SELECT * FROM postcode ORDER BY numbers DESC
這是一個db<>fiddle演示了這一點。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/389547.html
標籤：sql sql-server 查询语句 查询优化 存储函数
下一篇：基于ID的關系中的SQL更新順序