Início / dba / Perguntas / 237182
Accepted
youcantryreachingme
Asked: 2019-05-02 22:42:09 +0800 CST

Como substituir várias partes de uma string por dados de várias linhas?

  • 772

Dada uma tabela com duas colunas - um ID inteiro e uma string baseada em texto - quero começar com um valor de string que codifique qualquer número de inteiros entre chaves, misturados com quaisquer outros caracteres de texto válidos.

Exemplo:'{1} / {9} ... {12}'

Com uma única SELECTinstrução, quero retornar uma string pela qual todos os inteiros (e suas chaves) foram substituídos por um valor derivado da minha tabela; especificamente, o valor de texto para a linha com um ID que corresponde ao número encontrado na string de origem.... e quaisquer caracteres fora das chaves permanecem intocados.

Aqui está um exemplo que não consegue concluir a tarefa:

select
  replace('{13} {15}','{'+cast(id as varchar)+'}',isNull(display,''))
from testing;

Isso retornaria 1 linha por linha na testingtabela. Para a linha com idvalor = 13, a parte '{13}' da string é substituída com sucesso, mas a parte '{15}' não é (e vice-versa na linha 15).

Imagino que criar uma função que percorra todas as testinglinhas e tente repetidamente substituições resolveria o problema. Seja como for, uma instrução SQL direta seria preferível ao loop.

Dados de exemplo

+----+-------------------+
| id |  display          |
+----+-------------------+
|  1 |  Apple            |
|  2 |  Banana           |
|  3 |  Celery           |
|  4 |  Dragonfruit      |
|  5 |  Eggplant         |
|  6 |  Fenugreek        |
|  7 |  Gourd            |
|  8 |  Honeydew         |
|  9 |  Iceberg Lettuce  |
| 10 |  Jackfruit        |
| 11 |  Kale             |
| 12 |  Lemon            |
| 13 |  Mandarin         |
| 14 |  Nectarine        |
| 15 |  Olive            |
+----+-------------------+

Casos de uso de exemplo

select replace('{1} {3}',null,null) 
-- Returns 'Apple Celery'

select replace('{3},{4},{5}',null,null); 
-- Returns 'Celery,Dragonfruit,Eggplant'

select replace('{1} / {9} ... {12}',null,null); 
-- Returns 'Apple / Iceberg Lettuce ... Lemon'

Claramente, a palavra- replacechave não faz o trabalho.

PS. Se uma solução exigir que o formato da string seja alterado para facilitar isso, essa é uma opção.

Por exemplo: '#1 / #9 ... #12'(para correlacionar com o exemplo anterior)

Nesse formato, talvez pudéssemos dividir a string em um conjunto de linhas, com base em #, pegar os leftcaracteres até encontrarmos um não numérico, joinpara a testingtabela com base nos números obtidos, substituir os #e números pelo valor testingda tabela e displaydepois stufftodos individualmente tokens modificados de volta em uma única string for xml path?

Estou usando o SQL Server 2016, que não oferece suporte ao string_agg. Dito isso, se houver uma solução usando string_agg, ainda estou interessado em analisá-la.

sql-server t-sql

2 respostas

  1. Best Answer

    Aqui está um exemplo de como usar um recursivo ctepara traduzir as variáveis

    drop table if exists testing;
go
create table testing (id int, display varchar(16));
insert into testing values (1, 'Apple');
insert into testing values (2, 'Banana');
insert into testing values (3, 'Celery');
insert into testing values (4, 'Dragonfruit');
insert into testing values (5, 'Eggplant');

DROP FUNCTION IF EXISTS dbo.TranslateVariables
go
CREATE FUNCTION dbo.TranslateVariables
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (

--Common Table Expression for Translation
WITH TranslationTable
AS (
    SELECT FindValue = '{' + convert(varchar(5),id) + '}' ,ReplaceValue = display,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM testing
    )
--Recursive CTE to loop through the TranslationTable and replace FindValue with ReplaceValue
,RecursiveCte as
(
SELECT @StringValue AS StrValue
    ,(
        SELECT count(*)
        FROM TranslationTable
        ) AS cnt

UNION ALL

SELECT replace(StrValue, tt.FindValue, tt.Replacevalue)
    ,cnt - 1
FROM RecursiveCte
JOIN TranslationTable tt
    ON tt.rn = cnt )

SELECT StrValue
    ,cnt
FROM RecursiveCte where cnt = 0
    )
go

    --Verify translation
SELECT *
FROM dbo.TranslateVariables('{1} {3}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

     StrValue     | cnt |
|--------------|-----|
| Apple Celery | 0   |

    SELECT *
FROM dbo.TranslateVariables('{3},{4},{5}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

    | StrValue                    | cnt |
|-----------------------------|-----|
| Celery,Dragonfruit,Eggplant | 0   |
    • 4

  2. Supondo que você esteja em uma versão que o suporte (e desative a versão em seu violino original ), você pode usar as funções nativas string_split()& string_agg().

    declare @id_list varchar(10) = '1,3'; -- for 'Apple,Celery' 
-- set @id_list = '3,4,5'; --for 'Celery,Dragonfruit,Eggplant'

select string_agg(display, ',') as agg
from (
    select t.display 
    from testing t
    cross apply string_split(@id_list,',') ss 
    where try_cast(ss.[value] as int) = t.id
) x;

    O exemplo anterior pressupõe que você abandonou as chaves no caminho e é apenas uma lista de números separados por vírgula. Se você quiser manter as chaves no caminho, tente impor que ele esteja chegando como um JSON bem formado e use algumas das funções JSON nativas para analisá-lo. Os bits-chave acima são:

    1. fornecer uma matriz de [Id]s para ...
    2. filtrar apenas os testing.displays que você deseja e depois ...
    3. ...alimentar essa matriz de texto parastring_agg()

    Dado o esclarecimento de que você está em 2016, e que string_agg()só estará disponível em 2017, você ainda pode usar string_split()para criar a matriz conforme necessário e usar uma das abordagens herdadas que contornam a ausência de string_agg()como você menciona considerando no OP. Por exemplo:

    select stuff(agg,1,1,'') as agg_trim_first_comma
from (
    select stuff(x.display,1,0,'') 
    from (
        select ',' + t.display 
        from testing t
        cross apply string_split('1,3',',') ss 
        where try_cast(ss.[value] as int) = t.id
    ) x (display )
    for xml path('')
) y (agg);
    • 0

relate perguntas