مساله
اگر رشته ای داشته باشیم که کاراکترهای تکراری در کنار هم داشته باشد و بخواهین این کاراکتر ها تکراری کنار هم را حذف کنیم و تنها یک نمونه از آن باقی بگذاریم، از چه راه حل هایی برای حل این مشکل می توانیم استفاده کنیم؟
شاید اولین الگوریتمی که به ذهن یک برنامه نویس تابع گرا/رویه ای برسد استفاده از یک حلقه ی While باشد. اما بهتر نیست هنگامی که با یک زبان قدتمند مجموعه گرایی (set-oriented) چون SQL برنامه نویسی می کنیم از روشهای مجموع گرا (set-based) برای حل مسائل بهره مند شویم؟
البته در بعضی موارد ممکن است روش حلقه ی While عملکرد بهتری از روشهایی که با کمک جدول اعداد حل می شوند داشته باشد. در هر صورت در مقاله هایی که می نویسم سعی می کنم از هر دو دیدگاه به مساله نگاه کنم تا هم آشنایی و مهارت استفاده از کدهای T-SQL را تقویت کنم و هم دیدگاه مجموعه ای شما را گسترش بدهم.
به مثال زیر توجه کنید:
Before
------------------------------------
AAAAABBBC DEEEEEFF GGHHIGGAAB
After
--------------
ABC DEF GHIGAB
همانطوری که مشاهده می شود، کاراکترهای تکراری کنارهم حذف شده اند، حتی Space ها نیز فشرده شده اند.
برای حل این مساله راه حل های متنوعی وجود دارد که به بیشتر آنها پرداخته خواهد شد.
WHILE Loop
شاید ساده ترین روش همین روش باشد. با کمک یک حلقه که به تعداد طول رشته تکرار می شود کاراکترهایی را که مخالف کاراکتر قبل از خود هستند را به متغیر موقت خود اضافه می کنیم.
DECLARE @s VARCHAR(500) = 'SSSSSSSQQQQQQLLLLL SSSSSeeeervvvvveerr',
@r VARCHAR(500) = '',
@i INT = 1;
WHILE @i <= LEN(@s)
BEGIN
IF SUBSTRING(@s, @i, 1) <> SUBSTRING(@s, @i - 1, 1)
SET @r = @r + SUBSTRING(@s, @i, 1);
SET @i = @i + 1;
END;
SELECT @r AS [After]
/*--Result
After
------------
SQL Server
*/
حتی با کمک گرفتن از Assignment Select و ماده ی WHERE می توانیم از دستورات DML به جای IF در بدنه ی حلقه استفاده کنیم:
(توجه: سعی شده است از آخرین Syntax نرم افزار SQL Server استفاده شود، بطور مثال با کمک یک دستور DECLARE تمام متغیر ها را تعریف می کنیم و در همان مرحله مقداردهی اولیه نیز انجام می دهیم. همچنین از عملگر =+ نیز استفاده شده است)
DECLARE @s VARCHAR(500) = 'SSSSSSSQQQQQQLLLLL SSSSSeeeervvvvveerr',
@r VARCHAR(500) = '',
@i INT = 1;
WHILE @i <= LEN(@s)
BEGIN
SELECT @r = @r + SUBSTRING(@s, @i, 1)
WHERE SUBSTRING(@s, @i, 1) <> SUBSTRING(@s, @i - 1, 1);
SELECT @i += 1;
END;
SELECT @r AS [After];
/*--Result
After
------------
SQL Server
*/
روش بعدی در واقع استفاده از تکنیکی هست که نمونه های تکراری کنارهم از یک کاراکتر خاص را حذف می کنند. بطور کلی 256 کاراکتر اصلی وجود دارد.
DECLARE @s VARCHAR(500) ='SSSSSSSQQQQQQLLLLL SSSSSeeeervvvvveerr',
@i AS INTEGER = 0;
WHILE @i <= 255
BEGIN
IF CHARINDEX(CHAR(@i) COLLATE SQL_Latin1_General_CP1_CS_AS, @s COLLATE SQL_Latin1_General_CP1_CS_AS) > 0
SET @s = REPLACE(REPLACE(REPLACE(@s, CHAR(@i),CHAR(@i) + '~!@#$%^&*'), '~!@#$%^&*' + CHAR(@i),''), '~!@#$%^&*', '');
SET @i += 1;
END
SELECT @s AS [Result]
/*
Result
-------------
SQL Server
*/
Numbers Table
راه حل های گروه دوم، روش هایی هستند که بر اساس جدول اعداد پایه ریزی شده اند. یعنی ابتدا رشته را Split می کنیم به کاراکترهای تشکیل دهنده ی آن سپس پس از فیلتر کردن سطرهای مورد نظر، عناصر/کاراکترهای باقی مانده را با همدیگر الحاق می کنیم. برای فیلتر کردن کاراکترها روش های متعددی وجود دارد که به سه روش آن در اینجا اشاره خواهم کرد.
توابع Ranking و GROUP BY
CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @result VARCHAR(8000) = '';
SELECT @result = @result + Data
FROM (SELECT ID,
Data,
ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) - ID
FROM (SELECT SUBSTRING(@String, n, 1), n
FROM Nums
WHERE n <= LEN(@String)
) D(data, ID)
) D(ID, Data, RowNum)
GROUP BY Data, RowNum
ORDER BY MIN(ID)
RETURN @result
END;
NOT EXISTS
CREATE FUNCTION dbo.fnRemoveDupesII (@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @result VARCHAR(8000) = '';
WITH k(k, n) AS
(SELECT SUBSTRING(@String, nbr, 1), nbr
FROM Nums
WHERE nbr <= LEN(@String))
SELECT @result = @result + k
FROM k k1
WHERE NOT EXISTS
(SELECT *
FROM k k2
WHERE k1.k = k2.k
AND k1.n + 1 = k2.n);
RETURN @result;
END;
STUFF and CASE
CREATE FUNCTION dbo.fnReduceDupes(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000);
SELECT @Result = @string;
SELECT @Result =
STUFF(@Result, nbr, 1,
CASE SUBSTRING(@Result, nbr, 1)
WHEN SUBSTRING(@Result, nbr + 1, 1) THEN '!'
ELSE SUBSTRING(@Result, nbr, 1)
END)
FROM dbo.Nums
WHERE nbr <= LEN(@Result);
SELECT @Result = REPLACE(@Result, '!', '');
RETURN @Result;
END;
اکنون این سه تابع فوق را با یک داده، آزمایش می کنیم:
--Try This!
DECLARE @String VARCHAR(800) = 'SSSSSSSSQQQQQQQQQQLLLLLLLLLLLLL Serrrrrrrrrrrrrrvverrrrr';
SELECT dbo.fnRemoveDupesI(@String) AS [String 1],
dbo.fnRemoveDupesII(@String) AS [String 3],
dbo.fnReduceDupes(@String) AS [String 2];
/*--------------------------------
String 1 String 2 String 3 |
---------- ---------- -----------|
SQL Server SQL Server SQL Server |
*/--------------------------------