SQLShack
Os programadores de bases de dados necessitam frequentemente de converter um valor separado por vírgulas ou outros itens delimitados num formato tabular. Os delimitadores incluem tubo “|”, hash “#”, dólar “$” e outros caracteres. O SQL Server nível de compatibilidade 130, e versões subsequentes, suportam uma função string_split() para converter valores separados por delimitadores em linhas (formato de tabela). Para níveis de compatibilidade inferiores a 130, os programadores já o fizeram anteriormente com uma função definida pelo utilizador, que incorpora um While loop ou Cursor para extrair os dados.
Por que é necessária uma entrada separada por vírgula para um procedimento ou consulta T-SQL?
Executar um programa numa iteração usando um loop é um método comum na aplicação back-end, bem como na base de dados. Quando a contagem da iteração é menor do que o esperado, o tempo de execução pode ser menor, contudo o número de iterações é maior, o que leva a um tempo de processamento mais longo. Por exemplo, se a aplicação estiver a buscar dados de uma base de dados com a execução de um programa num laço através da análise de diferentes parâmetros de entrada, a aplicação terá de envolver a resposta da base de dados de cada iteração. Para lidar com este tipo de situação, podemos executar o mesmo programa com uma única execução com análise da vírgula ou delimitação de valores separados como parâmetro de entrada na base de dados e fazê-lo em formato tabular no programa T-SQL para proceder com mais lógica.
Hoje em dia, muitas plataformas foram alteradas para arquitectura de micro-serviço. Não só a aplicação, mas também a concepção de bases de dados poderia ser estruturada em arquitectura baseada em micro-serviços e a maior parte da comunicação interna entre as bases de dados é feita apenas com referências inteiras. Esta é a razão para obter alguma forma complicada de conseguir uma melhor execução do lado da base de dados.
Vários inputs para o mesmo parâmetro podem ser realizados com valores separados por vírgula no parâmetro de input do procedimento armazenado ou input para a função tabular, e utilizados com a tabela numa declaração T-SQL. Pode haver mais situações para esta estratégia quando se utiliza o T-SQL. Durante a programação com SQL Server, um programador deve quebrar uma cadeia em elementos utilizando um separador. A utilização da função de divisão contra a cadeia define o separador como um parâmetro de entrada. A cadeia de caracteres inteira será dividida e devolvida como uma tabela. Em determinadas circunstâncias, o parâmetro de entrada pode ser uma combinação de dois ou mais valores separados por caracteres para cada conjunto de entrada. Por exemplo:
N’203616, 198667, 193718, 188769,…’ N’1021|203616$1021|198667$1022|193718$1022|188769$…’ N’1021|203616,198667$1022|193718,188769$…’
Há vantagens na execução de um fio único com valores separados por vírgula sobre a execução de vários fios no SQL Server:
- Execução única para parte do programa, resultando no número de entradas
- Operação de leitura ou escrita única sobre as tabelas da base de dados em vez de várias iterações usando um laço
- Utilização de recursos de um servidor único
- Sem mais obstáculos na aplicação back-end para configurar uma resposta de múltiplos conjuntos de resultados
- Compromisso de um disparo significa que a base de dados/SQL Server não irá bloquear a transacção e atrasar as coisas. Assim, nenhuma possibilidade de bloqueio
- Fácil de supervisionar a consistência da transacção
Dividir função usando loop
Na maioria dos casos, um programador escreve a função tabela-valorizada com um loop para extrair os dados de valores separados por vírgula para uma lista ou formato de tabela. Um laço extrai o tuple um a um com a repetição. Podem surgir problemas de desempenho quando se trabalha com o grande parâmetro de entrada, uma vez que levará mais tempo a extraí-lo. Aqui, temos uma função simples com o nome de split_string, que utiliza um laço dentro da função valorizada pela tabela. Esta função permite dois argumentos ou parâmetros de entrada (1 – cadeia de entrada e 2 – delimitador):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE FUNCTION split_string
(
@in_string VARCHAR(MAX),
@delimeter VARCHAR(1)
)
RETURNS @list TABLE(tuple VARCHAR(100))
AS
BEGIN
WHILE LEN(@in_string) > 0
BEGIN
INSERT INTO @list(tuple)
SELECT left(@in_string) charindex(@delimiter, @in_string+’,’) -1) como tuple
SET @in_string = stuff(@in_string, 1, charindex(@delimiter, @in_string + @delimiter), ”)
fim
RETURN
END
|
Uma tabela de conjunto de resultados devolvida por esta função contém cada substrato da cadeia de parâmetros de entrada que é separado pelo delimitador. Os substratos na tabela estão na ordem em que ocorrem no parâmetro de entrada. Se o separador ou delimitador não coordenar com nenhuma parte da cadeia de entrada, então a tabela de saída terá apenas um componente. A função tabular retornará o resultado definido num formato linha-coluna a partir da cadeia de caracteres separada por vírgulas.
|
1
|
SELECT * FROM split_string(‘1001,1002,1003,1004’, ‘,’)
|
Versões recentes do SQL Server fornecem uma string_split() de função integrada para fazer a mesma tarefa com os parâmetros de entrada da string de entrada e delimitador. Mas existe outra forma eficiente de executar esta tarefa usando XML.
Função Split usando XML
A função Split usando lógica XML é um método útil para separar valores de uma string delimitada. A utilização da lógica XML com a abordagem XQUERY baseada em T-SQL resulta em que o T-SQL não seja caracterizado como uma recursividade usando laço ou cursor. Essencialmente, a cadeia de parâmetros de entrada é convertida para XML através da substituição do nó XML na articulação T-SQL. O XML resultante é utilizado em XQUERY para extrair o valor do nó para o formato de tabela. Também resulta num melhor desempenho.
Por exemplo, temos um valor separado por vírgula com a variável @user_ids na declaração abaixo do T-SQL. Numa variável, uma vírgula é substituída pelas etiquetas XML para a tornar num documento de tipo de dados XML e depois extraída usando XQUERY com a XPATH como ‘/root/U’:
|
1
2
3
4
5
6
7
8
9
10
>/td> |
DECLARE @user_ids NVARCHAR(MAX) = N’203616, 198667, 193718, 188769, 183820, 178871, 173922, 168973, 164024, 159075, 154126, 149177, 144228, 139279, 134330, 129381, 124432, 119483, 114534, 109585, 104636, 99687, 94738, 89789, 84840, 79891, 74942, 69993, 65044, 60095, 55146′
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@user_ids, ‘,’, </U><U></U></root>’ AS XML)
SELECT f.x.valor(‘.’, ‘BIGINT’) AS user_id
INTO #users
FROM @sql_xml.nodes(‘/root/U’) f(x)
SELECT *
FROM #users
|
Na função acima, utilizámos o tipo de dados VARCHAR (100) para o valor extraído em vez de BIGINT. Também pode haver alterações na cadeia de parâmetros de entrada. Por exemplo, se a cadeia de entrada tiver um ‘,’ extra no início ou fim da cadeia, devolve uma linha extra com 0 para o tipo de dados BIGINT e ” para VARCHAR.
Por exemplo,
|
1
2
3
4
5
|
Para lidar com este tipo de ” ou 0 dados no conjunto de resultados, podemos acrescentar a condição abaixo na declaração T-SQL:
|
1
2
3
4
5
6
|
Aqui, f.x.value(‘.’, ‘BIGINT’) <> 0 exclui o 0 e f.x.value(‘.’, ‘VARCHAR(100)’) <> ” exclui o ” no conjunto de resultados da consulta. Esta condição T-SQL pode ser adicionada às funções valorizadas na tabela com o número de parâmetros de entrada para lidar com este delimitador extra.
Função:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
CREATE FUNCTION split_string_XML
(
@in_string VARCHAR(MAX),
@delimeter VARCHAR(1)
)
RETURNS @list TABLE(tuple VARCHAR(100))
AS
BEGIN
DECLARE @sql_xml XML = Cast(‘<root><U>’+ Replace(@in_string_string, @delimetro, </U><U></U></root>’ AS XML)
INSERT INTO @list(tuple)
SELECT f.x.value(‘.’, ‘VARCHAR(100)’) COMO tuple
DE @sql_xml.nodes(‘/root/U’) f(x)
ONDE f.x.value(‘.’, ‘BIGINT’) <> 0
RETURN
END
|
Execução:
|
1
2
|
Combinação de caracteres em cadeia delimitada
Mas e quando o parâmetro de entrada é uma combinação de dois valores com múltiplos separadores? Neste caso, o parâmetro de entrada deve ser extraído duas vezes com a ajuda de uma subconsulta, como se mostra abaixo:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE @in_string VARCHAR(MAX) = ‘1021|203616$1021|198667$1022|193718$1022|188769 XML = Cast(‘<root><U>’+ Replace(@in_string, ‘ </U><U></U></root>’ AS XML)
SELECT X.Y.valor(‘(U)’, ‘VARCHAR(20)’) COMO role_id,
X.Y.valor(‘(U)’, VARCHAR(20)’) COMO user_id
FROM
(
SELECT Cast(‘<root><U>’+ Substituir(f.x.value(‘.”, ‘VARCHAR(MAX)’), ‘|’, </U><U></U></root>’ AS XML) AS xml_
FROM @sql_xml.nodes(‘/root/U’) f(x)
WHERE f.x.value(‘.’, ‘VARCHAR(MAX)’) <> ”
)T
OUTER APPLY T.xml_.nodes(‘root’) como X(Y)
|
Este exemplo é semelhante ao exemplo acima, mas utilizámos o tubo “|” como um delimitador com um segundo delimitador, dólar “$”. Aqui, o parâmetro de entrada é a combinação de duas colunas, role_id, e user_id. Como vemos abaixo, user_id e role_id estão numa coluna separada no conjunto de resultados da tabela:
Podemos utilizar qualquer caracter na função acima, tal como uma única cadeia de caracteres separada ou uma combinação de cadeias de caracteres separadas. Um desenvolvedor deve simplesmente substituir o caractere nas declarações T-SQL acima. Siga os passos aqui descritos para converter qualquer cadeia de caracteres delimitada para uma lista. Basta lembrar que a função XML do SQL Server aceita uma string de entrada e a string de entrada será delimitada com o separador especificado usando a função tabular.
- Autor
- Posts recentes
Ver todos os posts de Jignesh Raiyani
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020
, ‘</U><U></U></root>’ AS XML)
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020
This example is similar to the above example but we have utilized pipe “|” as a delimiter with a second delimiter, dollar “$”. Here, the input parameter is the combination of two-columns, role_id, and user_id. As we see below, user_id and role_id are in a separate column in the table result set:
We can use any character in the above function, such as a single character-separated string or combination of character-separated strings. A developer must simply replace the character in the T-SQL statements above. Follow the steps described here to convert any delimited string to a list. Simply remember that the XML function of SQL Server accepts a string input and the input string will be delimited with the specified separator using the tabular function.
- Author
- Recent Posts
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- How to automate Table Partitioning in SQL Server – July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 – July 6, 2020