روش های حذف تکرار کلمات از رشته‌ها در SQL Server

حذف تکرار کلمات از رشته‌ها در SQL Server: راهنمای جامع و کاربردی

یکی از چالش‌های رایج در مدیریت داده‌ها با SQL Server، مواجهه با رشته‌هایی است که شامل مقادیر تکراری و جدا شده با کاما هستند. اغلب لازم است این مقادیر تکراری حذف شوند تا یک لیست متمایز و بدون تکرار از کلمات یا آیتم‌ها به دست آید. این کار برای تحلیل دقیق‌تر داده‌ها، بهبود گزارش‌ها و اطمینان از کیفیت اطلاعات بسیار اهمیت دارد. در این راهنما، چندین روش کارآمد برای حذف تکرار کلمات از رشته‌ها در SQL Server را بررسی می‌کنیم، از تکنیک‌های داخلی SQL گرفته تا استفاده از توابع پیشرفته‌تر.

برای شروع، یک جدول نمونه ایجاد می‌کنیم تا این روش‌ها را روی آن آزمایش کنیم. این جدول شامل اطلاعات کارمندان خواهد بود که در آن نام‌ها ممکن است تکراری باشند.

CREATE TABLE Employee (
    EmployeeId INT IDENTITY(1,1),
    Name VARCHAR(100)
);

INSERT INTO Employee (Name) VALUES ('Greg');
INSERT INTO Employee (Name) VALUES ('Greg');
INSERT INTO Employee (Name) VALUES ('John');
INSERT INTO Employee (Name) VALUES ('Jack');
INSERT INTO Employee (Name) VALUES ('John');
INSERT INTO Employee (Name) VALUES ('Mike');
INSERT INTO Employee (Name) VALUES ('Lisa');
INSERT INTO Employee (Name) VALUES ('Greg');
INSERT INTO Employee (Name) VALUES ('Lisa');

با استفاده از کد بالا، یک جدول به نام Employee با ستون‌های EmployeeId و Name ایجاد می‌شود و سپس چندین نام (برخی تکراری) به آن اضافه می‌گردد. اکنون، می‌خواهیم لیستی متمایز از نام‌ها را به صورت یک رشته جدا شده با کاما به دست آوریم.

روش اول: استفاده از XML PATH و STUFF

یکی از رایج‌ترین و کارآمدترین روش‌ها برای تجمیع رشته‌ها در SQL Server، استفاده ترکیبی از FOR XML PATH و تابع STUFF است. این روش به شما امکان می‌دهد تا مقادیر را از چندین سطر به یک رشته واحد تبدیل کنید. با اضافه کردن DISTINCT در زیرپرس و جو، می‌توانیم اطمینان حاصل کنیم که تنها مقادیر منحصر به فرد در رشته نهایی ظاهر می‌شوند.

SELECT STUFF((SELECT DISTINCT ',' + Name FROM Employee FOR XML PATH('')), 1, 1, '');

در این قطعه کد، زیرپرس و جو (SELECT DISTINCT ',' + Name FROM Employee FOR XML PATH('')) تمام نام‌های متمایز را با یک کاما در ابتدا انتخاب می‌کند و آن‌ها را به فرمت XML تبدیل می‌کند. سپس، FOR XML PATH('') این نام‌ها را به یک رشته واحد XML متصل می‌کند. در نهایت، تابع STUFF(string, start, length, new_string) برای حذف کامای اضافی ابتدای رشته استفاده می‌شود و لیستی از نام‌های منحصر به فرد را به صورت یک رشته جدا شده با کاما به ما می‌دهد.

روش دوم: استفاده از تابع Table-Valued Function (TVF)

اگر نیاز دارید این عملیات را به صورت مکرر و برای رشته‌های مختلف انجام دهید، ایجاد یک تابع Table-Valued Function (TVF) می‌تواند راه حلی بسیار منعطف و قابل استفاده مجدد باشد. این تابع یک رشته ورودی را دریافت کرده، آن را به مقادیر مجزا تقسیم می‌کند، مقادیر منحصر به فرد را استخراج کرده و سپس آن‌ها را دوباره به یک رشته جدید تجمیع می‌کند. برای تقسیم رشته‌ها، از یک تابع جداکننده رشته کمکی استفاده می‌کنیم.

ابتدا، یک تابع برای تقسیم رشته‌ها ایجاد می‌کنیم (اگر از SQL Server 2016 به بالا استفاده می‌کنید، می‌توانید از STRING_SPLIT استفاده کنید):

CREATE FUNCTION [dbo].[SplitString]
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Results TABLE (Item NVARCHAR(MAX))
AS
BEGIN
    DECLARE @Index INT
    DECLARE @Slice NVARCHAR(MAX)

    SELECT @Index = 1
    IF @String IS NULL RETURN

    WHILE @Index != 0
    BEGIN
        SELECT @Index = CHARINDEX(@Delimiter, @String)
        IF @Index != 0
        BEGIN
            SELECT @Slice = LEFT(@String, @Index - 1)
            INSERT INTO @Results(Item) VALUES(@Slice)
            SELECT @String = RIGHT(@String, LEN(@String) - @Index)
        END
        ELSE
        BEGIN
            INSERT INTO @Results(Item) VALUES(@String)
        END
    END
    RETURN
END;

این تابع SplitString یک رشته و یک جداکننده (Delimiter) را می‌گیرد و آن رشته را بر اساس جداکننده به آیتم‌های کوچکتر تقسیم کرده و در یک جدول بازگشتی قرار می‌دهد.

اکنون، تابعی برای حذف تکرار کلمات از رشته ایجاد می‌کنیم که از SplitString بهره می‌برد:

CREATE FUNCTION [dbo].[RemoveDuplicates]
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX) = '';

    SELECT @Result = STUFF(
        (SELECT DISTINCT @Delimiter + Item
         FROM [dbo].[SplitString](@String, @Delimiter)
         FOR XML PATH('')
        ), 1, 1, '');

    RETURN @Result;
END;

این تابع RemoveDuplicates ابتدا رشته ورودی را با استفاده از SplitString تقسیم می‌کند، سپس از DISTINCT برای حذف تکرارها بهره می‌برد و در نهایت با استفاده از FOR XML PATH و STUFF مقادیر منحصر به فرد را به یک رشته جدید با جداکننده مشخص تجمیع می‌کند.

نحوه استفاده از این تابع به شکل زیر است:

SELECT [dbo].[RemoveDuplicates]('Greg,John,Jack,John,Mike,Lisa,Greg,Lisa', ',');

با فراخوانی این تابع، رشته ورودی پردازش شده و لیستی از کلمات منحصر به فرد بدون تکرار به عنوان خروجی بازگردانده می‌شود.

روش سوم: استفاده از CLR Function برای عملکرد بهینه

برای سناریوهایی که نیاز به عملکرد بسیار بالا دارید و حجم داده‌ها زیاد است، استفاده از توابع CLR (Common Language Runtime) می‌تواند بهترین گزینه باشد. توابع CLR در .NET نوشته می‌شوند و در SQL Server اجرا می‌شوند، که امکان استفاده از قدرت زبان‌های برنامه‌نویسی مانند C# را فراهم می‌کنند. این روش به خصوص برای عملیات پیچیده و پرهزینه بر روی رشته‌ها که توابع T-SQL ممکن است کند عمل کنند، مناسب است.

ابتدا، کد C# را برای تابع حذف تکرار کلمات می‌نویسیم:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Collections.Generic;
using System.Linq;

public partial class UserDefinedFunctions
{
    [SqlFunction(IsDeterministic = true, IsPrecise = true)]
    public static SqlString RemoveDuplicateStrings(SqlString inputString, SqlString delimiter)
    {
        if (inputString.IsNull || delimiter.IsNull)
        {
            return SqlString.Null;
        }

        string[] parts = inputString.Value.Split(delimiter.Value.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
        HashSet distinctParts = new HashSet(parts);

        return new SqlString(string.Join(delimiter.Value, distinctParts.OrderBy(s => s)));
    }
}

این کد C# یک تابع به نام RemoveDuplicateStrings را پیاده‌سازی می‌کند. این تابع دو پارامتر دریافت می‌کند: inputString (رشته ورودی) و delimiter (جداکننده). ابتدا رشته ورودی را با استفاده از جداکننده تقسیم می‌کند، سپس با بهره‌گیری از HashSet مقادیر منحصر به فرد را استخراج می‌کند و در نهایت آن‌ها را مجدداً با جداکننده به هم متصل کرده و به عنوان یک SqlString بازمی‌گرداند. OrderBy(s => s) برای مرتب‌سازی خروجی به ترتیب الفبا استفاده شده است.

پس از کامپایل کردن این کد به یک اسمبلی (DLL)، می‌توانید آن را در SQL Server ثبت و استفاده کنید. ابتدا، مطمئن شوید که CLR در SQL Server فعال است:

sp_configure 'clr enabled', 1;
RECONFIGURE;

سپس، اسمبلی را در SQL Server ایجاد کرده و تابع CLR را ثبت کنید:

-- فرض بر این است که DLL در مسیر مشخصی قرار دارد
CREATE ASSEMBLY clr_string_functions
FROM 'C:\YourPathToDLL\YourAssembly.dll' -- مسیر فایل DLL خود را اینجا قرار دهید
WITH PERMISSION_SET = SAFE;

CREATE FUNCTION RemoveDuplicateStringsCLR
(
    @inputString NVARCHAR(MAX),
    @delimiter NVARCHAR(1)
)
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME clr_string_functions.UserDefinedFunctions.RemoveDuplicateStrings;

پس از ثبت، می‌توانید تابع CLR را مانند یک تابع T-SQL معمولی فراخوانی کنید:

SELECT dbo.RemoveDuplicateStringsCLR('Greg,John,Jack,John,Mike,Lisa,Greg,Lisa', ',');

استفاده از توابع CLR می‌تواند عملکرد چشمگیری را برای عملیات پردازش رشته‌های پیچیده و حجیم ارائه دهد، اما نیاز به دانش برنامه‌نویسی .NET و تنظیمات امنیتی خاص در SQL Server دارد.