Como extrair apenas os valores que estão entre aspas no mesmo campo depois gravá-los em outra tabela separados por cada campo

Question

Boa tarde, Pessoal"

Sou iniciante em SQL, em uma tabela do nosso BD tempos um campo que possui o registro com as informações do paciente concatenado com uma serie de valores que não precisamos.

Como eu faço, para montar uma select para extrair desse registro apenas o que está entre aspas?

O meu registro está assim:

a:28:{i:18;s:13:"Sérgio silva";i:19;s:11:"1199900001";i:20;s:0:"n/i";i:21;s:0:"n/i";i:23;s:15:"Amil One Health";i:24;s:0:"n/i";i:27;s:0:"n/i";i:31;s:1:"2";i:32;s:0:"n/i";i:33;s:1:"2";i:34;s:0:;}

Eu preciso dele assim:

"Sérgio silva";"1199900001";"n/i";"n/i";"Amil One Health";"n/i";"n/i";"2";"n/i";"2";

Observação, esses monte de valores a:28: ...etc... eles mudam, não são valores fixos, se fossem até usaria o replace, mas são vários valores.

Depois que eu chegar nessa mineração: "Sérgio silva";"1199900001";"n/i";"n/i";"Amil One Health";"n/i";"n/i";"2";"n/i";"2";

Como eu faço para alocar cada valor em campo campo qualquer, pode ser uma tabela temporária, tipo assim:

|nome           |campo 1        |Campo 2 |Campo 3 |Campo 4               |Campo  5 | .....

|Sérgio silva|1199900001|n/i              |n/i              |Amil One Health|n/i               |n/i|2|n/i|2| .....

Muito obrigado amigos

Answer 1

Deleted

Answer 2

Mestre José Diz, muito obrigado pela orientação.

Deu certo, a unica coisa que tive que criar foi DelimitedSplit8K, encontrei o codigo completo na web.

Muito obrigado, mestre.

Valeu mesmo.

para quem não tem o código DelimitedSplit8K, segue abaixo:

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

Answer 3

Deleted

Answer 4

Mestre, José!

Perfeito, já estou com o conteúdo em PDF para ler calmamente e simular aqui.

Por favor, só mais uma ajuda :)

Como eu faço, para agrupar o resultado em uma unica linha, tirando todos esses Null's, assim:

Como está atualmente:

Preciso que fique assim:

Novamente, sou muito grato á você mestre José Diz.

Answer 5

Deleted

Answer 6

Além de acrescentar a coluna RA, houve outra alteração no código? É que estranhei o [2], [4], [6], ..

Resp: Sim, alterei o código para adaptar a necessidade da empresa, segue a estrutura:

WITH 
-- OBTÉM O TRECHO ENTRE "{" E "}"
PASSO1 AS (
SELECT ID_UNICA= ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
       alumatricula as RA,
       SUBSTRING (FIELD, 
                  CHARINDEX ('{', FIELD) +1,
                  (CHARINDEX ('}', FIELD) - CHARINDEX ('{', FIELD) -1)) AS MIOLO
  FROM 
  (SELECT alumatricula, REPLACE (CONVERT(VARCHAR(MAX),dados_especificos),'""','"n/i"') AS FIELD
FROM (SELECT * FROM OPENQUERY(WEBSQL, 'select * from webc.cliente_saude') ) AS TB
) AS MINERACAO
),

-- separa token por ";"
PASSO2 AS (
SELECT P1.RA,
   P1.ID_UNICA, 
       SS.ITEMNUMBER AS SEQ_2,
       SS.ITEM AS TOKEN
 FROM PASSO1 AS P1
      CROSS APPLY DBO.DELIMITEDSPLIT8K (P1.MIOLO, ';') AS SS
  ),

-- seleciona tokens do tipo "s"
PASSO3 AS (
SELECT ID_UNICA, 
   TOKEN, 
   SEQ_2,
   RA
  FROM PASSO2
  WHERE LEFT (TOKEN, 2) = 's:'
),

-- obter o terceiro elemento de cada token
PASSO4 AS (
SELECT  P3.ID_UNICA, 
P3.SEQ_2 AS COLUNA, 
P3.TOKEN, 
P3.RA,
REPLACE (SS.ITEM,'"','') AS ELEMENTO
 FROM PASSO3 AS P3
      CROSS APPLY DBO.DELIMITEDSPLIT8K (P3.TOKEN, ':') AS SS
WHERE SS.ITEMNUMBER = 3
),

PASSO5 AS
(
SELECT RA, ID_UNICA, [2], [4], [6], [8], [10], [12], [14], [16], [18], [20], [22], [24], [26], [28], [30], [32], [34], [36], [38], [40], [42], [44], [46], [48], [50], [52], [54], [56]
  from Passo4 
      pivot (MAX (ELEMENTO) for COLUNA in ([2], [4], [6], [8], [10], [12], [14], [16], [18], [20], [22], [24], [26], [28], [30], [32], [34], [36], [38], [40], [42], [44], [46], [48], [50], [52], [54], [56])) as P
) 


SELECT * FROM PASSO5 WHERE RA = '09867'

RA é a chave primária da tabela que contém os textos multivalorados? 

Resp: Sim.

Você rodou o código #1 v2?

Resp: Rodei, segue os resultados

Irei seguir a sua sugestão para tentar agrupar em uma unica linha os dados, logo postarei o resultado.

Answer 7

Deleted

Answer 8

Mestre José :) ... sei q vc não gosta desse titulo, mas continua sendo mestre.

Desculpe pela demora em dar um retorno.

Deu tudo certo, consegui obter os resultados conforme a sua orientação.

No caso do linked server, agradeço a você, pois eu li a sua matéria no likendin e coloquei em pratica a metodologia.

Irei seguir a sua orientação.

Novamente eu agradeço a sua atenção.

abs e sucesso.