اگر بخواهید تنها اعداد را از یک رشته ای که در آن کاراکترهای مختلفی درج شده است بدست آورید چه راه حلی ارائه می دهید؟
اولین و ساده ترین فکر این است که در یک حلقه ی While کاراکتر به کاراکتر رشته را بررسی کنیم و در صورت عدد بودن کاراکتر، آن را انتخاب کنیم.
CREATE FUNCTION dbo.ExtractInteger (@String VARCHAR(2000))
RETURNS VARCHAR(2000) AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(2000)
SET @Count = 0
SET @IntNumbers = ''
WHILE @Count <= LEN(@String)
BEGIN
IF SUBSTRING(@String, @Count, 1) BETWEEN '0' AND '9'
SET @IntNumbers = @IntNumbers + SUBSTRING(@String, @Count, 1)
SET @Count = @Count + 1
END
RETURN @IntNumbers
END
GO
SELECT dbo.ExtractInteger('It is a string that contains some integer like 1 and 2')
روش بعدی در حقیقت عکس روش قبلی عمل می کند، یعنی به جای اضافه کردن کاراکتر های اعداد به متغیر رشته ای تعریف شده، متغیر های غیر عددی را از رشته حذف می کنیم تا جایی که دیگر کاراکتر غیر عددی در رشته یافت نشود. این کار را می توانیم با PATINDEX انجام دهیم. اگر الگوی تعریف شده در تابع PATINDEX در رشته یافت نشود عدد 0 برگردانده خواهد شد.
در این روش در بهترین حالت حلقه اجرا نخواهد شد و بدترین حالت زمانی خواهد بود که تمام کاراکترهای رشته، غیر عددی و غیر یکسان باشند. نکته ای که وجود دارد این است که کاراکترهای غیر عددی یکسان با تنها یک دستور REPLACE به یکباره حذف می شوند.
CREATE FUNCTION dbo.smartExtracingNumber(@NumStr VARCHAR(8000))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @Str VARCHAR(8000)
SET @Str = @NumStr
WHILE PATINDEX('%[^0-9]%', @Str) > 0
SET @Str = REPLACE(@Str, SUBSTRING(@Str, PATINDEX('%[^0-9]%', @Str), 1), '')
RETURN @Str
END
GO
SELECT dbo.smartExtracingNumber('It is a string that contains some integer like 1 and 2')
روش دیگر که توسط madhivanan معرفی شده است دارای منطق متفاوتی است. ابتدا با کمک یک جدول اعداد تمام کاراکترهای رشته را split کرده سپس بعد از فیلتر کردن کاراکترها آنها را الحاق می کنیم.
CREATE FUNCTION set_based_extrac_number(@s VARCHAR(1000))
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @result VARCHAR(100)
SET @result=''
SELECT @result = @result + number
FROM (SELECT SUBSTRING(@s, number, 1) AS number
FROM (SELECT number
FROM master..spt_values
WHERE type='p' AND number BETWEEN 1 AND LEN(@s)
) AS t
) AS t
WHERE number LIKE '[0-9]'
RETURN @result
END
GO
SELECT dbo.set_based_extrac_number('123 it is just 456 for testing 678');
روش Madhi را می توانیم طوری طراحی کنیم که بی نیاز به تعریف و استفاده از تابع scalar باشد. چونکه روش Madhi مجموعه گرا یا به اصطلاح فنی set-based است. پس روش به شکل زیر در خواهد آمد:
;WITH CTE(i) AS
(SELECT 'sadlfjks 213 dfj 234324 sf' UNION ALL
SELECT '2342sadfwe4234sdf9o7234' UNION ALL
SELECT '2432sfsf234'),
Numbs(n) AS
(SELECT 1
UNION ALL
SELECT n + 1 FROM Numbs WHERE n < 100)
SELECT D.i
FROM CTE
CROSS APPLY
(SELECT i + ''
FROM (SELECT SUBSTRING(i, n, 1)
FROM Numbs
WHERE n <= LEN(i)
)D(i)
WHERE i LIKE '[0-9]'
FOR XML PATH(''))D(i);
/*
i
-----------------
213234324
2342423497234
2432234
*/
توضیح روش: ابتدا با کمک Common Table Expression یک جدول مجازی تعریف شده و سه سطر با عملگر UNION ادغام و به جدول انتساب داده شده، و بعد دوباره با کمک یک CTE یک جدول اعداد از 1 تا 100 با روش Recursive CTE تعریف نموده و در پایان در قسمت SELECT اعمال زیر انجام گرفته است:
ابتدا به ازای هر سطر در جدول مورد نظر، مقدار به کاراکتر ها Split شده و بعد از فیلتر با عبارت LIKE، کاراکتر ها با یکدیگر به روش FOR XML PATH الحاق شده اند.
امید وارم توضیحات به درک بیشتر Query کمک کرده باشند.
روش دیگری که آقای Tony Rogerson استفاده کرده است:
CREATE FUNCTION dbo.Tony (@source VARCHAR(8000))
RETURNS INTEGER AS
BEGIN
DECLARE @start INT = 1;
WHILE @start <= 255
BEGIN
IF @start NOT BETWEEN ASCII( '0' ) AND ASCII( '9' )
SET @source = REPLACE( @source, CHAR( @start ), '' )
SET @start = @start + 1
END
RETURN @source * 1
END
GO
SELECT dbo.Tony('123 it is a string 9237 mix with integer 2');
مراجع:
Pinalkumer Dave (SQL Server MVP)
Suprotim Agarwal (MVP)
Madhivanan (SQL Server MVP)
Tony Rogerson (SQL Server MVP)