SQLTeam.com | Weblogs | Forums

Convert the string to multiple column

Hi All,

I am having one requirement where In my sql table one of my column having value like question and answer combination.

given the screen shot of input and output data set.

Please use the below query.

I want to convert key value in different column
create table qa
(
id int,
qa nvarchar(max)
)

INSERT INTO qa
SELECT 100,'Q: What is your name ?
A: Ryan

Q: What is your age ?
A: 35'

image

Pl

Thanks,
Vipin jha

SELECT 
      ID
   , replace(value ,': W','Q: W')
FROM 
   qa  
    CROSS APPLY STRING_SPLIT(qa, 'Q')
WHERE value <> ''

image

Thanks Harish for your quick help, can I get the question which start with Q in one column and answer in another column like below screen shot.
and one more thing you have consider one think like all question will start with 'W'
but we can have many more question which can start with How, could

image

Thanks,
Vipin Jha

I can manage to achieve above concern with below code

I want to get Q in one column and A in another column

Thanks,
Vipin

; with cte as 
(
SELECT 
      ID
   ,  value
FROM 
   qa  
    CROSS APPLY STRING_SPLIT(qa, 'Q')
WHERE value <> ''
) 
select 
   id
  ,replace(left(value,patindex('%A:%',value)-2),':','')
  ,replace(right(value,len(value)-patindex('%A:%',value)),':','') 
from 
  cte

image

Thank you so much Harish for your help.

Hi Harish,

need small help , the logic which given by you worked fine where we dont have any "Q" in questiion and in answer. but it is failing when q comes in question and answer.

please use below sample data for reference

create table qa
(
id int,
qa nvarchar(max)
)

INSERT INTO qa
SELECT 500,'
Q: Foi a primeira vez que utilizou o produto?
A: Não
Q: Quando você observou o problema?
A: Ao abrir o produto
Q: Onde você armazenou o produto? E por quanto tempo?
A: No armário antes do consumo
Q: O produto estava devidamente lacrado quando você o comprou?
A: Sim
Q: Como você consumiu o produto?
A: No estado em que se encontra
Q: Como você preparou o produto?
A: De acordo com as instruções de embalagem
Q: Você adicionou algo ao produto?
A: Não'

Thanks,
Vipin Jha

first add in the string
some rare character

so
Q becomes $Q

now use $ to split

issue of Q should go away

Answered here