TRANSLATE در SQL Server پاکسازی و تبدیل داده ها

آشنایی با تابع TRANSLATE در SQL Server: راهکاری نوین برای پاکسازی و تبدیل داده‌ها

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

ساختار کلی استفاده از تابع TRANSLATE به صورت زیر است:

TRANSLATE (string_expression, characters, translations)

در این ساختار، string_expression رشته اصلی است که قصد دارید عملیات جایگزینی روی آن انجام شود. characters شامل لیستی از کاراکترهایی است که می‌خواهید جایگزین شوند و translations نیز لیستی از کاراکترهای جدیدی است که به ترتیب جایگزین کاراکترهای متناظر خود در characters خواهند شد.

برای درک بهتر عملکرد تابع TRANSLATE، به مثال‌های زیر توجه کنید. فرض کنید می‌خواهیم در یک رشته، اعداد خاصی را با حروف معادل آن‌ها جایگزین کنیم:

SELECT TRANSLATE('23456789', '234', 'ABC') AS Example;

در این مثال، ‘2’ با ‘A’، ‘3’ با ‘B’ و ‘4’ با ‘C’ جایگزین می‌شوند. نتیجه این کوئری `ABC56789` خواهد بود. تابع TRANSLATE هر کاراکتر را از رشته ‘characters’ برداشته و با کاراکتر متناظر آن در رشته ‘translations’ جایگزین می‌کند.

اگر رشته اصلی (string_expression) دارای مقدار NULL باشد، نتیجه تابع TRANSLATE نیز NULL خواهد بود:

SELECT TRANSLATE(NULL, 'ABC', 'DEF') AS NULL_Example;

همچنین، باید به طول رشته‌های characters و translations توجه داشته باشید. اگر طول این دو رشته متفاوت باشد، بسته به وضعیت، نتایج متفاوتی حاصل می‌شود. به عنوان مثال، اگر طول translations کوتاه‌تر از characters باشد، کاراکترهای اضافی در characters از رشته اصلی حذف می‌شوند:

SELECT TRANSLATE('abc', 'abcd', '123') AS Example;

در این حالت، ‘a’ با ‘1’، ‘b’ با ‘2’ و ‘c’ با ‘3’ جایگزین می‌شوند. از آنجا که کاراکتر ‘d’ در ‘characters’ وجود دارد ولی کاراکتر متناظری در ‘translations’ ندارد، حذف خواهد شد. نتیجه این کوئری `123` است.

حتی می‌توانید از تابع TRANSLATE برای تبدیل و استانداردسازی داده‌ها استفاده کنید. مثلاً برای تغییر نام یک شرکت به یک نام دیگر با جایگزینی کاراکتر به کاراکتر:

SELECT TRANSLATE('SQL', 'SQL', 'Microsoft') AS SQL_Translation;

این کوئری ‘S’ را با ‘M’، ‘Q’ را با ‘i’ و ‘L’ را با ‘c’ جایگزین می‌کند و نتیجه `Mic` خواهد بود. توجه داشته باشید که جایگزینی بر اساس موقعیت کاراکتر انجام می‌شود، نه کل کلمه.

***

یکی از رایج‌ترین سوالات در مورد تابع TRANSLATE، تفاوت آن با تابع REPLACE است. تابع REPLACE برای جایگزینی یک الگوی متنی (sub-string) با الگوی دیگر استفاده می‌شود، در حالی که TRANSLATE برای جایگزینی کاراکتر به کاراکتر عمل می‌کند.

ساختار تابع REPLACE به صورت زیر است:

REPLACE (string_expression, string_pattern, string_replacement)

در این تابع، string_expression رشته اصلی، string_pattern الگویی است که باید جایگزین شود و string_replacement نیز الگوی جدید است.

به تفاوت عملکرد این دو تابع با مثال دقت کنید. ابتدا با تابع REPLACE:

SELECT REPLACE('Hello World', 'o', '*') AS ReplaceExample;

این کوئری تمام رخدادهای حرف ‘o’ را با ‘*’ جایگزین می‌کند و نتیجه `Hell* W*rld` خواهد بود.

حالا همین کار را با تابع TRANSLATE انجام می‌دهیم:

SELECT TRANSLATE('Hello World', 'eo', '**') AS TranslateExample;

در این مثال، ‘e’ با ‘*’ اول و ‘o’ با ‘*’ دوم جایگزین می‌شود. نتیجه `H*ll* W*rld` است. می‌بینید که ‘e’ و ‘o’ هر دو جایگزین شده‌اند، اما به روش کاراکتر به کاراکتر.

برای جایگزینی چندین کاراکتر با تابع REPLACE، باید از چندین تابع REPLACE به صورت تو در تو استفاده کرد که منجر به کدهای طولانی و پیچیده می‌شود:

SELECT REPLACE(REPLACE(REPLACE('SQL Server is Fun', 'S', '$'), 'u', '@'), 'n', '#') AS NestedReplace;

در مقابل، تابع TRANSLATE همین کار را به سادگی و در یک خط انجام می‌دهد:

SELECT TRANSLATE('SQL Server is Fun', 'Sun', '$@#') AS TranslateEasy;

همانطور که مشاهده می‌کنید، ‘S’ با ‘$’، ‘u’ با ‘@’ و ‘n’ با ‘#’ جایگزین می‌شوند. این کارآمدی، TRANSLATE را به ابزاری قدرتمند برای عملیات پاکسازی و استانداردسازی رشته‌ها تبدیل می‌کند.

***

تابع TRANSLATE می‌تواند جایگزین راه‌حل‌های پیچیده‌تر و کم‌بازده شود که شامل استفاده از توابعی مانند CHARINDEX، STUFF، PATINDEX و حلقه‌های WHILE برای حذف یا جایگزینی کاراکترها می‌شدند. این موضوع به ویژه در عملیات پاکسازی داده‌ها برای حذف کاراکترهای خاص یا غیرمجاز اهمیت پیدا می‌کند.

برای مثال، فرض کنید می‌خواهید تمامی کاراکترهای خاص و غیرمجاز را از یک رشته پاک کنید تا تنها حروف و اعداد باقی بمانند. روش سنتی با استفاده از CHARINDEX و STUFF و حلقه WHILE می‌تواند بسیار طولانی و پیچیده باشد:

DECLARE @InputString NVARCHAR(255) = 'This is a string with special characters!@#$%^&*()';
DECLARE @CharIndex INT;
DECLARE @CharactersToClean NVARCHAR(100) = '!@#$%^&*()';

WHILE (LEN(@InputString) > 0)
BEGIN
    SET @CharIndex = 1;
    WHILE (@CharIndex <= LEN(@CharactersToClean))
    BEGIN
        SET @InputString = REPLACE(@InputString, SUBSTRING(@CharactersToClean, @CharIndex, 1), '');
        SET @CharIndex = @CharIndex + 1;
    END;
    BREAK; -- Exit after one pass or refine loop for real-time string changes
END;

SELECT @InputString AS CleanedString;

این کد یک مثال ساده از پیچیدگی این روش را نشان می‌دهد که نیاز به حلقه‌ها و منطق پیچیده برای حذف تک به تک کاراکترها دارد.

در مقابل، تابع TRANSLATE همین کار را به شکلی ساده و بهینه انجام می‌دهد. کافی است لیست کاراکترهایی که باید حذف شوند را در پارامتر characters قرار دهید و یک رشته خالی را در پارامتر translations قرار دهید. در این صورت، هر کاراکتری که در characters باشد ولی متناظر آن در translations نباشد، از رشته اصلی حذف خواهد شد:

SELECT TRANSLATE('This is a string with special characters!@#$%^&*()', '!@#$%^&*()', '') AS CleanedStringTranslate;

نتیجه این کوئری `This is a string with special characters` خواهد بود (با فرض اینکه فاصله و حروف عادی حفظ شوند). این روش بسیار خواناتر، کوتاه‌تر و از نظر عملکردی بهینه‌تر است، به خصوص برای پاکسازی حجم زیادی از داده‌ها.

***

مثال دیگری که نشان‌دهنده برتری TRANSLATE بر ترکیب PATINDEX و REPLACE است، حذف تمامی کاراکترهای غیرعددی از یک رشته است. در گذشته، این کار اغلب با یک حلقه WHILE و استفاده از PATINDEX برای یافتن کاراکترهای غیرعددی و REPLACE برای حذف آن‌ها انجام می‌شد:

DECLARE @Phone NVARCHAR(50) = '(123) 456-7890 Ext. 1234';
DECLARE @CleanPhone NVARCHAR(50) = '';
DECLARE @Char NVARCHAR(1);
DECLARE @Index INT = 1;

WHILE @Index <= LEN(@Phone)
BEGIN
    SET @Char = SUBSTRING(@Phone, @Index, 1);
    IF @Char LIKE '[0-9]'
    BEGIN
        SET @CleanPhone = @CleanPhone + @Char;
    END;
    SET @Index = @Index + 1;
END;

SELECT @CleanPhone AS PhoneNumber;

این کد، تمامی کاراکترهای غیرعددی را حذف کرده و تنها اعداد را نگه می‌دارد. اما تابع TRANSLATE، این عملیات را به شکلی بسیار ساده‌تر و با کارایی بالا انجام می‌دهد. برای حذف تمام کاراکترهای غیرعددی، کافی است تمام کاراکترهای عددی را در `characters` و سپس همان کاراکترها را در `translations` قرار دهید. این باعث می‌شود که کاراکترهای عددی بدون تغییر باقی بمانند و هر کاراکتر دیگری که در `characters` نباشد، از رشته حذف شود. برای حذف کاراکترهای غیرعددی، باید لیستی از تمامی کاراکترهایی که می‌خواهید حذف شوند را در `characters` قرار دهید و `translations` را خالی بگذارید.

یک راهکار هوشمندانه برای حفظ فقط اعداد با TRANSLATE این است که تمام کاراکترهای الفبایی و نمادها را در `characters` قرار داده و `translations` را خالی بگذارید:

SELECT TRANSLATE('(123) 456-7890 Ext. 1234', '()- .abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', '') AS PhoneNumber;

این کوئری تمام پرانتزها، خط فاصله، نقطه، فاصله و حروف الفبا را حذف می‌کند و تنها اعداد را باقی می‌گذارد. نتیجه `12345678901234` خواهد بود. این رویکرد، کد را بسیار خواناتر و نگهداری آن را آسان‌تر می‌کند.

به طور کلی، تابع TRANSLATE در SQL Server ابزاری بسیار مفید برای پاکسازی و تبدیل داده‌ها است که می‌تواند پیچیدگی‌های مرتبط با جایگزینی چندگانه کاراکترها را به حداقل برساند و کد SQL را بهینه‌تر و خواناتر کند. این قابلیت در عملیات ETL (Extract, Transform, Load) و آماده‌سازی داده‌ها برای تحلیل، ارزش فراوانی دارد.