Some useful SQL functions, syntax, and more - Part 18 ( REIWA GANNEN Memorial Post )

The end of HEISEI ( 平成 ), REIWA ( 令和 ) has begun. The last year of HEISEI was turning point in my life. However, I still want to evolve, so I want to move forward without fear of change.
I will explain about TRANSLATE (Transact-SQL) - SQL Server | Microsoft Docs. Complex processing in REPLACE function can be described very simply by TRANSLATE function. Let's compare the description contents of REPLACE function and TRANSLATE function.

Let's execute the following query in SQL Server Management Studio.

DECLARE @str NVARCHAR(255) = 'aaa,aaa,aaa|bbb,bbb,bbb'

SELECT
    REPLACE(REPLACE(REPLACE(@str,'|','*'),',','|'),'*',',') [REPLACE],
    TRANSLATE(@str, ',|', '|,') [TRANSLATE]

Execution result of REPLACE and TRANSLATE

The features of TRANSLATE function are as follows.

The behavior of the TRANSLATE function is similar to using multiple REPLACE functions. TRANSLATE does not, however, replace a character more than once. This is dissimilar to multiple REPLACE functions, as each use would replace all relevant characters.

In stored procedures, I often find that the RELACE function is nested in multiple layers. Although not available in all cases, use TRANSLATE function to refactor complex stored procedures.

I bought this magazine. I will do my best to study English!!

AERA English (アエラ・イングリッシュ) 2019 Spring & Summer【表紙:山下智久】 (AERA増刊)