Плевать на CR & LF на разные колонки

Question

У меня есть проблема, где у меня есть nvarchar столбец, в котором хранятся имя и адрес, однако в строке есть CR. Мне удалось отследить функцию, которая отображает, что такое пустое пространство.

Пример как ниже

Mr[?]John[?]Spartan[CR][LF]36[?]Kendrick[?]Road[CR][LF]Birmingham[CR][LF]B3[?]7SA[CR][LF][CR][LF]

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

Конечный результат будет 4 столбца вместо 1.

Address 1       | Address 2        | Address 3  | Address 4
Mr John Spartan | 36 Kendrick Road | Birmingham | B3 7SA

Надеюсь, я достаточно ясно со своей проблемой.

Answer 1

Похоже на @SarathAvanavu, но использует PIVOT;

declare @table table (id int not null, NameAndAddress varchar(max) not null)

insert @table values (1, 'Mr John Spartan|36 Kendrick Road|Birmingham|B3 7SA')
insert @table values (2, 'Mrs Victoria Smith|72 Domino Avenue|Leicester|LE1 7RE')
insert @table values (3, 'Miss Sarah Parker|48 Lilly Lane|Bedford|BE2 6TE')

; with cte as (
    select
        id,
        cast('<column>' + replace(NameAndAddress, '|', '</column><column>') + '</column>' as xml) as XmlNameAndAddress
    from
        @table
)
select id, [1] as Column1, [2] as Column2, [3] as Column3, [4] as Column4
from
(
select
    id,
    row_number() over(partition by id order by (select 0)) as rn,
    Split.a.value('.', 'varchar(1024)') as Value
from
    cte
        cross apply XmlNameAndAddress.nodes ('/column') as Split(a)
) AS SourceTable
pivot
(
    min(Value)
    for rn in  ([1], [2], [3], [4])
) as PivotTable

Answer 2

Если вы хотите преобразовать в столбцы, попробуйте это. Я написал логику внутри запроса.

DECLARE @STR NVARCHAR(MAX)='Mr[?]John[?]Spartan[CR][LF]36[?]Kendrick[?]Road[CR][LF]Birmingham[CR][LF]B3[?]7SA[CR][LF][CR][LF]'

-- Convert to CSV
 SELECT @STR = LEFT(REPLACE(REPLACE(@STR,'[?]',' '),'[CR][LF]',' , '),
LEN(REPLACE(REPLACE(@STR,'[?]',''),'[CR][LF]',' , ')))

;WITH CTE AS
(
   -- Split to rows and create a row to set default Row_Number()
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RNO,
    LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Address' 
    FROM  
    (
         -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
         SELECT CAST ('<M>' + REPLACE(@STR, ',', '</M><M>') + '</M>' AS XML) AS Data           
    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)
)
-- Convert to columns
SELECT 
MIN(CASE WHEN RNO=1 THEN [Address] END) [Address1], 
MIN(CASE WHEN RNO=2 THEN [Address] END) [Address2],
MIN(CASE WHEN RNO=3 THEN [Address] END) [Address3],
MIN(CASE WHEN RNO=4 THEN [Address] END) [Address4] 
FROM CTE 

РЕЗУЛЬТАТ

РЕДАКТИРОВАТЬ:

Если вы выбираете строку из столбца в вашей таблице, вы можете выполнить следующую процедуру.

ОБРАЗЕЦ СТОЛ

CREATE TABLE #TEMP(STRINGCOL NVARCHAR(MAX))

INSERT INTO #TEMP
SELECT 'Mr[?]John[?]Spartan[CR][LF]36[?]Kendrick[?]Road[CR][LF]Birmingham[CR][LF]B3[?]7SA[CR][LF][CR][LF]'
UNION ALL
SELECT 'Mrs[?]Smith[?]Tisut[CR][LF]12[?]Criss[?]Road[CR][LF]Birmingham[CR][LF]C4[?]6UI[CR][LF][CR][LF]'

QUERY

;WITH CTE AS
(
    -- Since, its splitting to CSV, we need to get a unique id for those items which was in row
    SELECT ROW_NUMBER() OVER(PARTITION BY RNO ORDER BY (SELECT 0)) RNO2,RNO,
    LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Address' 
    FROM  
    (
         -- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
        SELECT RNO,CAST ('<M>' + REPLACE(REPLACE(REPLACE(STRINGCOL,'[?]',' '),'[CR][LF]',' , ')
        , ',', '</M><M>') + '</M>' AS XML) AS Data
        FROM
        (
            -- Select Row_Number for each row
            SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RNO, STRINGCOL
            FROM #TEMP
        )TAB   

    ) AS A 
    CROSS APPLY Data.nodes ('/M') AS Split(a)   
)
SELECT  RNO,
MIN(CASE WHEN RNO2=1 THEN [Address] END) [Address1], 
MIN(CASE WHEN RNO2=2 THEN [Address] END) [Address2],
MIN(CASE WHEN RNO2=3 THEN [Address] END) [Address3],
MIN(CASE WHEN RNO2=4 THEN [Address] END) [Address4] 
FROM CTE 
GROUP BY RNO