TRANSLATE function in SQL SERVER

Question

I read that there is a function equivalent to the standard function TRANSLATE under DB2 under SQL Server 2017. But how to do under earlier versions?

For definition of function : here

Answer 1

Adapted from @Shnugo's answer. This is closer to what you want. You just need to make certain you have a dbo.numbers table (they're REALLY useful to have).

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=627828307504174dcf3f61313ba384a8

CREATE FUNCTION dbo.MultiReplace(@ReplaceTarget NVARCHAR(MAX), @from_chars NVARCHAR(MAX), @to_chars NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    --Quirky Update: One of the rare situations where this is a good idea 
    SELECT @ReplaceTarget=REPLACE(@ReplaceTarget,SUBSTRING(@from_chars, id+1, 1), SUBSTRING(@to_chars, id+1, 1))
    FROM numbers
    WHERE id < LEN(@from_chars) AND id < LEN(@to_chars)
    ORDER BY id;

    RETURN @ReplaceTarget;
END

And a slightly over the top way to meet your requirement that TRANSLATE('abc', 'abc', 'bcd') => 'bcd').

CREATE FUNCTION dbo.Translate(@ReplaceTarget NVARCHAR(MAX), @from_chars NVARCHAR(MAX), @to_chars NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN

  DECLARE
    @steps INT = LEN('_' + @from_chars + '_') - 2
  ;

  WITH
    dictionary(id, string_from, string_interim, string_to) AS
  (
    SELECT
      id, string_from, N'<' + string_from + N'>', string_to
    FROM
    (
      SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY string_from ORDER BY id)  AS occurence,
        string_from,
        string_to
      FROM
        numbers
      CROSS APPLY
      (
        SELECT
          CAST(SUBSTRING(@from_chars, numbers.id, 1) AS NVARCHAR(5))  AS string_from,
          CAST(SUBSTRING(@to_chars,   numbers.id, 1) AS NVARCHAR(5))  AS string_to
      )
        chars
      WHERE
            numbers.id >  0
        AND numbers.id <= @steps
    )
      sorted_dictionary
    WHERE
      occurence = 1
  )
  ,
    mapping_sequence(id, string_from, string_to) AS
  (
              SELECT 1,               N'<',            N'<<>'                        WHERE @from_chars LIKE N'%<%'
    UNION ALL SELECT 2,               N'>',            N'<>>'                        WHERE @from_chars LIKE N'%>%'
    UNION ALL SELECT 3,               N'<<<>>',        N'<<>'                        WHERE @from_chars LIKE N'%<%' AND @from_chars LIKE N'%>%'

    UNION ALL SELECT 3 + id,          string_from,    string_interim FROM dictionary WHERE string_from NOT IN (N'<', N'>')
    UNION ALL SELECT 3 + @steps + id, string_interim, string_to      FROM dictionary
  )
  SELECT
    @ReplaceTarget = REPLACE(@ReplaceTarget, string_from, string_to)
  FROM
    mapping_sequence
  ORDER BY
    id
  ;

  RETURN @ReplaceTarget;

END

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9dbe7214ac4b5bb00060686cfaa879c2

A possible minor optimisation of the above (To reduce the number of REPLACE calls where possible)...

CREATE FUNCTION dbo.Translate(
  @ReplaceTarget NVARCHAR(MAX),
  @from_chars    NVARCHAR(MAX),
  @to_chars      NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

  DECLARE
    @steps INT = LEN('_' + @from_chars + '_') - 2
  ;

  WITH
    dictionary AS
  (
    SELECT
      id, string_from, string_to
    FROM
    (
      SELECT
        ROW_NUMBER() OVER (    ORDER BY string_from            )  AS id,
        ROW_NUMBER() OVER (PARTITION BY string_from ORDER BY id)  AS occurence,
        string_from,
        string_to
      FROM
        numbers
      CROSS APPLY
      (
        SELECT
          CAST(SUBSTRING(@from_chars, numbers.id, 1) AS NVARCHAR(5))  AS string_from,
          CAST(SUBSTRING(@to_chars,   numbers.id, 1) AS NVARCHAR(5))  AS string_to
      )
        chars
      WHERE
            numbers.id >  0
        AND numbers.id <= @steps
    )
      sorted_dictionary
    WHERE
      occurence = 1
  ),
    two_stage AS
  (
    SELECT
      map.*
    FROM
      dictionary   dict
    CROSS APPLY
    (
      SELECT COUNT(*) FROM dictionary WHERE dictionary.id > dict.id AND dictionary.string_from = dict.string_to
    )
      remap(hits)
    CROSS APPLY
    (
      SELECT id,                     dict.string_from,               dict.string_to          WHERE remap.hits = 0 AND dict.string_from NOT IN (N'<', N'>')
      UNION ALL
      SELECT id,                     dict.string_from,        N'<' + dict.string_from + N'>' WHERE remap.hits > 0 AND dict.string_from NOT IN (N'<', N'>')
      UNION ALL
      SELECT id + @steps,     N'<' + dict.string_from + N'>',        dict.string_to          WHERE remap.hits > 0 AND dict.string_from NOT IN (N'<', N'>')
      UNION ALL
      SELECT id + @steps * 2, N'<' + dict.string_from + N'>',        dict.string_to          WHERE                    dict.string_from     IN (N'<', N'>')
    )
      map
  )
  ,
    mapping_sequence(id, string_from, string_to) AS
  (
              SELECT 1,               N'<',          N'<<>'                   WHERE @from_chars LIKE N'%<%'
    UNION ALL SELECT 2,               N'>',          N'<>>'                   WHERE @from_chars LIKE N'%>%'
    UNION ALL SELECT 3,               N'<<<>>',      N'<<>'                   WHERE @from_chars LIKE N'%<%' AND @from_chars LIKE N'%>%'

    UNION ALL SELECT 3 + id,          string_from,   string_to FROM two_stage
  )
  SELECT
    @ReplaceTarget = REPLACE(@ReplaceTarget, string_from, string_to)
  FROM
    mapping_sequence
  ORDER BY
    id
  ;

  RETURN @ReplaceTarget;

END

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8af6ae050dc8d425521ae911b70a7968

Or...

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1451aa88780463b1e7cfe15dd0071194

Or...

http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3079d4dd4289e8696072f6ee37be76ae

Answer 2

Better than a WHILE loop is - at least in my eyes - the quirky update wrapped in a function:

You can maintain replace values in a table. You might add some grouping keys (e.g. for languag selection or topic focus) and pass this into the function as additional parameter:

CREATE TABLE ReplaceValues (FindChar NVARCHAR(100) NOT NULL
                           ,ReplWith NVARCHAR(100) NOT NULL
                           ,SortOrder INT NOT NULL);
INSERT INTO ReplaceValues VALUES('a','x',1)         --all "a" will be "x"
                               ,('test','yeah!',2)  --"test" will be "yeah"
                               ,('hello','ciao',3)  --"hello" will be "ciao"
                               ,('xxx','magic',4);  --this is magic (see below)
GO

--You cannot use the quirky update inlined, but you can wrap it within a scalar function:

CREATE FUNCTION dbo.MultiReplace(@ReplaceTarget VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    --Quirky Update: One of the rare situations where this is a good idea 
    SELECT @ReplaceTarget=REPLACE(@ReplaceTarget,rv.FindChar,rv.ReplWith)
    FROM ReplaceValues AS rv
    ORDER BY rv.SortOrder;

    RETURN @ReplaceTarget;
END
GO

--A table with test data

declare @t table(TestString varchar(100))
insert into @t values('This string is without repls')
                    ,('This is a test, hello, one more test')
                    ,('See the cascade replace with aaa, which is converted to xxx, then to magic');

--...and the magic is going in here:

SELECT t.TestString
      ,dbo.MultiReplace(t.TestString) AS Translated
FROM @t AS t
GO

--Clean-Up

DROP FUNCTION dbo.MultiReplace;
DROP TABLE ReplaceValues;

The result

This string is without repls
This is x yeah!, ciao, one more yeah!
See the cxscxde replxce with magic, which is converted to magic, then to mxgic

Answer 3

I propose my translate function:

CREATE FUNCTION [dbo].[F_Translate]
(
       @String   varchar(8000), 
       @FromChar varchar(200), 
       @ToChar   varchar(200)
)
RETURNS varchar(8000)
AS
BEGIN
        DECLARE @result as varchar(8000) = NULL
        DECLARE @currentChar as char(1) = NULL
        DECLARE @CurrentIndexFounded as int = 0
        DECLARE @CurrentIndexString as int = 0

        IF(@FromChar IS NULL OR @ToChar IS NULL)
        BEGIN
            return cast('Parameters @FromChar and @ToChar must contains 1 caracter minimum' as int);
        END
        ELSE IF(DATALENGTH(@FromChar) <> DATALENGTH(@ToChar) OR DATALENGTH(@FromChar) = 0)
        BEGIN
            return cast('Parameters @FromChar and @ToChar must contain the same number of characters (at least 1 character)' as int);
        END

       IF(@String IS NOT NULL) 
       BEGIN
            SET  @result = '';
            WHILE(@CurrentIndexString < DATALENGTH(@String))
            BEGIN 
                    SET @CurrentIndexString = @CurrentIndexString + 1;
                    SET @currentChar = SUBSTRING(@String, @CurrentIndexString, 1);
                    SET @CurrentIndexFounded  = CHARINDEX(@currentChar COLLATE Latin1_General_CS_AS, @FromChar COLLATE Latin1_General_CS_AS);
                    IF(@CurrentIndexFounded > 0)
                    BEGIN
                            SET @result = CONCAT(@result, SUBSTRING(@ToChar, @CurrentIndexFounded, 1)) ;
                    END
                    ELSE
                    BEGIN
                            SET @result = CONCAT(@result, @currentChar);
                    END
             END
       END
       return @result
END

Answer 4

EDITED:

I'm feeling dumb - MatBailie correctly pointed out that my original solution was incorrect. I actually always thought that TRANSLATE('abc', 'abc', 'bcd') was supposed to return ddd but, after testing SQL Server 2017's TRANSLATE I see that 'bcd' would be the correct answer. You can see my original (incorrect version) by looking at this history of this post. Here's an updated solution that uses ngrams8k:

DECLARE
  @string varchar(8000)  = 'abc',
  @fromChar varchar(100) = 'abc', -- note: no mutation
  @toChar varchar(100)   = 'bcd';

SELECT newString = 
(
  SELECT CASE WHEN x>z THEN '' WHEN x>0 THEN s ELSE t END+''
  FROM dbo.ngrams8k(@string,1) ng
  CROSS APPLY (VALUES (charindex(ng.token,@fromChar),len(@toChar),ng.token)) x(x,z,t)
  CROSS APPLY (VALUES (ng.position, substring(@toChar,x.x,1))) xx(p,s)
  ORDER BY xx.p
  FOR XML PATH(''), TYPE
).value('(text())[1]', 'varchar(8000)');

Returns > bcd

Answer 5

Thought I'd put my idea in as well. This avoids the dreaded WHILE loop, and, also, doesn't use a self referencing variable (which can get ugly).

Note the use of a Tally table, first, and then I use a Table Valued Function (rather than Scalar, which are slow) to do the work.

Note, that I have set it so that if you provide fewer arguments on the right hand side, that the character will be removed. So, if the parameter @FindChars had the value 'AB' and @ReplaceChars the value 'C', then 'A' would be replaced with 'C' and 'B' would be replaced with ''. I note that with TRANSLATE this would produce the error The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

The problem, however, with a function is that you can't use things like THROW or RAISERROR. This means that actually producing an error inside a function isn't possible. You could, however, set something up so that NULL is returned if the two lengths don't match, but (unfortunately) the error production cannot be performed inside the function itself.

CREATE VIEW dbo.Tally WITH SCHEMABINDING
AS 

    WITH C1 AS (SELECT 1 AS I UNION ALL SELECT 1),
         C2 AS (SELECT 1 AS I FROM C1 AS L CROSS JOIN C1 AS R),
         C3 AS (SELECT 1 AS I FROM C2 AS L CROSS JOIN C2 AS R),
         C4 AS (SELECT 1 AS I FROM C3 AS L CROSS JOIN C3 AS R),
         C5 AS (SELECT 1 AS I FROM C4 AS L CROSS JOIN C4 AS R),
         C6 AS (SELECT 1 AS I FROM C5 AS L CROSS JOIN C5 AS R),
         RN AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM C6)
    SELECT TOP (1000000) N
    FROM RN
    ORDER BY N;
GO

CREATE FUNCTION dbo.OwnTranslate (@String varchar(8000),@FindChars varchar(8000), @ReplaceChars varchar(8000))
RETURNS TABLE
AS RETURN (

    WITH ToBeReplaced AS (
    SELECT @String AS String,
        FC.N,
        SUBSTRING(@FindChars, FC.N,1) AS FC,
        ISNULL(SUBSTRING(@ReplaceChars, RC.N,1),'') AS RC
    FROM (SELECT TOP (LEN(@FindChars)) N FROM Tally) FC
        OUTER APPLY (SELECT TOP (LEN(@ReplaceChars)) T.N FROM Tally T WHERE T.N = FC.N AND T.N <= LEN(@ReplaceChars)) RC),
    Replacing AS (
        SELECT N, REPLACE(String, FC, RC) AS ReplacedString
        FROM ToBeReplaced
        WHERE N = 1
        UNION ALL
        SELECT R.N + 1, REPLACE(ReplacedString, TBR.FC, TBR.RC) AS ReplacedString
        FROM ToBeReplaced TBR
            JOIN Replacing R ON TBR.N = R.N + 1)
    SELECT TOP 1 ReplacedString
    FROM Replacing
    ORDER BY N DESC);
GO

WITH VTE AS (
    SELECT *
    FROM (VALUES ('This is a string to be Translated.')) V(S))
SELECT VTE.S, OT.ReplacedString
FROM VTE
     CROSS APPLY dbo.OwnTranslate (VTE.S, 'Ts ', 'qz_') OT;

GO
--Clean up
DROP FUNCTION dbo.OwnTranslate;
DROP VIEW Tally;

Any questions, please do ask.