SQL заменяет список

Я пытаюсь понять, как я могу заменить строку, используя данные из другой таблицы

У меня есть таблица, которая выглядит так:

Id Translation
1  Peter
2  Sandra
3  Olga

Теперь я хочу выбрать все и заменить переводы, используя список, который выглядит так:

Original New
e        #
r        ?
lg       *%

Итак, список выбора выглядит так:

Id  Translation
1   P#t#?
2   Sand?a
3   O*%a

Итак, для каждого перевода мне нужно иметь ЗАМЕНИТЬ (Перевод, Оригинал, Новый). Или другими словами: мне нужно пройти каждый "перевод" в моем первом списке и сделать еще один цикл в моей таблице замены, чтобы увидеть, что заменить

Упомяните, что первый список имеет 25'000 строк, а второй - 50 000, поэтому я не могу просто набрать его вручную:)

ИЗМЕНИТЬ

Просто уточнить: Оригинал и Новый из моей таблицы поиска могут быть буквами и словами, поэтому таблица может выглядеть так:

Original New
one      two
three    fifty
sun      moon

Ответ 1

Чтобы сделать это в одном запросе, вам нужно использовать рекурсивный CTE. Что-то вроде:

with trans as (
      select t.original, t.new, row_number() over (order by t.original) as seqnum,
             count(*) over () as cnt
      from translations
     ),
     t as (
      select tt.id, tt.string, replace(tt.string, trans.original, trans.new) as replaced,
             seqnum + 1 as seqnum, cnt
      from totranslate tt join
           trans
           on trans.id = 1
      union all
      select t.id, t.string, replace(t.string, trans.original, trans.new),
             seqnum + 1 as seqnum, cnt
      from t join
           trans
           on t.seqnum = trans.id
      where t.seqnum <= t.cnt
     )
select t.id, t.string, t.replaced
from t
where seqnum = cnt;

Ответ 2

Также с рекурсивным cte:

DECLARE @translations TABLE
    (
      Id INT ,
      Translation NVARCHAR(20)
    )
INSERT  INTO @translations
VALUES  ( 1, 'Peter' ),
        ( 2, 'Sandra' ),
        ( 3, 'Olga' )

DECLARE @replacements TABLE
    (
      Original VARCHAR(2) ,
      New VARCHAR(2)
    )
INSERT  INTO @replacements
VALUES  ( 'e', '#' ),
        ( 'r', '?' ),
        ( 'lg', '*%' );


WITH    cte1 AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT 1)) rn
                 FROM @translations CROSS JOIN @replacements),
        cte2 AS (SELECT Id, rn, REPLACE(Translation, Original, New) AS NTranslation
                 FROM cte1 
                 WHERE rn = 1
                    UNION ALL
                 SELECT c2.Id, c2.rn + 1, REPLACE(c2.NTranslation, c1.Original, c1.New)
                 FROM cte1 c1
                 JOIN cte2 c2 ON c2.Id = c1.Id AND c2.rn + 1 = c1.rn)
SELECT * FROM cte2
WHERE rn = (SELECT COUNT(*) FROM @replacements)
ORDER BY Id

EDIT:

WITH    cte1 AS (SELECT t.*, p.Id AS Old, p.Code, ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY (SELECT 1)) rn
                 FROM translations t CROSS JOIN Property p),
        cte2 AS (SELECT Id, rn, REPLACE(Trans, Old, Code) AS NTranslation
                 FROM cte1 
                 WHERE rn = 1
                    UNION ALL
                 SELECT c2.Id, c2.rn + 1, REPLACE(c2.NTranslation, c1.Old, c1.Code)
                 FROM cte1 c1
                 JOIN cte2 c2 ON c2.Id = c1.Id AND c2.rn + 1 = c1.rn)
SELECT * FROM cte2
WHERE rn = (SELECT COUNT(*) FROM Property)
ORDER BY Id

Ответ 3

Вы можете использовать UDF:

CREATE FUNCTION [dbo].[Translate]
(
-- Add the parameters for the function here
@Str nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
  DECLARE @Result nvarchar(max) = @Str;

  SELECT @Result = replace(@Result,Original,New) from dbo.Mappings order BY Pos; 

  RETURN @Result;
END

Здесь я предположил, что таблица, содержащая переводы, называется dbo.Mappings, а рядом с столбцами Original и New нужен другой столбец Pos int, который будет использоваться для определения порядка, в котором применяются переводы (для адресации проблемы, упомянутые @Thorsten Kettner в комментариях)

Ответ 4

Вот что я разработал, чтобы вы могли заменить несколько символов одной указанной строкой.

[Split2] украден из https://blogs.msdn.microsoft.com/amitjet/2009/12/11/convert-comma-separated-string-to-table-4-different-approaches/

USE <Your Database>
GO

CREATE FUNCTION [dbo].[Split2]
( 
@strString varchar(4000)
)
RETURNS @Result TABLE
(
 RID INT IDENTITY(0,1) Primary Key
,Value varchar(4000)
)
AS
    BEGIN
        WITH StrCTE(start, stop) AS
        (
        SELECT 1, CHARINDEX(',' , @strString )
        UNION ALL
        SELECT  stop + 1, CHARINDEX(',' ,@strString  , stop + 1)
            FROM StrCTE
            WHERE stop > 0
        )

        INSERT INTO @Result
        SELECT SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop - start ELSE 4000 END) AS stringValue
            FROM StrCTE

        RETURN
    END
GO

USE <Your Database>
GO

CREATE FUNCTION [dbo].[MultiReplace]
(
 @MyString varchar(MAX)
,@RepChars varchar(4000)
,@NewChars varchar(4000)
)
RETURNS varchar(MAX)
AS
    BEGIN
        DECLARE @CurRow int = 0
        DECLARE @MaxRow int

        SELECT @MaxRow = MAX(RID)
            FROM dbo.split2 ( @RepChars )

        WHILE @CurRow <= @MaxRow
            BEGIN
                SELECT @MyString = REPLACE(@MyString,VALUE,@NewChars)
                    FROM dbo.split2 ( @RepChars )
                    WHERE RID = @CurRow

                SET @CurRow = @CurRow + 1
            END

        RETURN (@MyString);

    END
GO

В этом примере я заменяю каждый символ пробелом

SELECT [dbo].[MultiReplace]('6th month 2016-06 (test / requested)',',1st,2nd,3rd,4th,5th,6th,0,1,2,3,4,5,6,7,8,9,(,),/,-,+, ','')

Результат: monthtestrequested

Я надеюсь, что это будет полезно для вас.