Splitting delimited string to separate columns in simple SELECT

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 307
DECLARE @IDTag VARCHAR(100)= 'TEST|00000298398293|EQ5|Patient'
SELECT CAST('<Node>' + REPLACE(@IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[1]/text()') AS Region,
CAST('<Node>' + REPLACE(@IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[2]/text()') AS PatientID,
CAST('<Node>' + REPLACE(@IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[3]/text()') AS FormType,
CAST('<Node>' + REPLACE(@IDTag,'|','</Node><Node>') + '</Node>' AS xml).query('Node[4]/text()') AS NurseStation


output:
Region	PatientID	FormType	NurseStation
TEST 00000298398293 EQ5 Patient

Comments or Responses

Login to post response