String split insert into columns in MS SQL

Ayyappan CNN 61 Reputation points
2022-08-04T10:48:10.98+00:00

Hi,

ref. my Ms SQL script. 

If(OBJECT_ID('tempdb..#StudProfileReviewer') Is Not Null)  
Begin  
    Drop Table #StudProfileReviewer  
End  
  
create table #StudProfileReviewer  
(  
    StudID [INT],   
    StudName Varchar(50),   
    StudProfileRemarks Varchar(Max),   
    RemarksDate DatetIme,   
    RemarksSplit_NameMistakes Varchar(100),  
	RemarksSplit_MobilePhoneMistakes Varchar(100),  
	RemarksSplit_DOBMistakes Varchar(100),  
	RemarksSplit_AddressMistakes Varchar(100),  
	RemarksSplit_OtherMistakes Varchar(100),  
	RemarksCount INT  
)  
  
Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1001,'SriRaman','Mobile Phone No length mistake | AddressMistake', '2022/05/03')   
Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1007,'Janani','NameMistake | Mobile Phone No length mistake | AddressMistake', '2022/06/12')  
Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1003,'Guru','DOB Mistake | Pincode Wrong ', '2022/03/11')  
Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1002,'Sankaran','Vehile Reg. No is Wrong ', '2022/03/11')  
Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1004,'Zaheer','', '2021/01/08')  
  
Select * from [#StudProfileReviewer]

Expected Result:

228102-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions
Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points
    2022-08-05T07:47:56.937+00:00

    Please try this,the code based on lijingyang： 

    ;  
with mycte as   
(  
  select StudID,StudName,StudProfileRemarks, RemarksDate,trim(value) StudProfileRemark  
  FROM [#StudProfileReviewer]    
 cross apply openjson('["'+(replace(StudProfileRemarks,'|','","')+'"]'))  
),mycte1 AS  
(  
  select  StudID,StudName,StudProfileRemarks, RemarksDate, StudProfileRemark,S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Remarksplit_item1  
         ,S.a.value('(/H/r)[2]', 'VARCHAR(100)')  AS Remarksplit_item2,S.a.value('(/H/r)[3]', 'VARCHAR(100)')  AS Remarksplit_item3  
  FROM   
 (  
   SELECT *,CAST (N'<H><r>' + REPLACE(StudProfileRemarks, '|', '</r><r>')  + '</r></H>' AS XML) AS [vals]  
   FROM mycte   
) d   
CROSS APPLY d.[vals].nodes('/H/r') S(a)   
  )  
Select   StudID, StudName,    StudProfileRemarks,    RemarksDate ,  
Remarksplit_item1,Remarksplit_item2,Remarksplit_item3,  
   Max(Case when StudProfileRemark = 'NameMistake' then 'NameMistake' else '' end) RemarksSplit_NameMistakes  
   ,Max(Case when StudProfileRemark = 'Mobile Phone No length mistake' then 'Mobile Phone No length mistake' else '' end) RemarksSplit_MobilePhoneMistakes      
    ,Max(Case when StudProfileRemark = 'DOB Mistake' then 'DOB Mistake' else '' end) RemarksSplit_DOBMistakes      
     ,Max(Case when StudProfileRemark = 'AddressMistake' then 'AddressMistake' else '' end) RemarksSplit_AddressMistakes  
      ,Max(Case   
      when StudProfileRemark  <> 'NameMistake'  
      and StudProfileRemark <> 'Mobile Phone No length mistake'   
      and StudProfileRemark <> 'DOB Mistake'  
      and StudProfileRemark <> 'AddressMistake'  
      and StudProfileRemark <>''  
      then StudProfileRemark else '' end)      RemarksSplit_OtherMistakes  
      ,  Max(Case when StudProfileRemark = 'NameMistake' then 1 else 0 end)   
   + Max(Case when StudProfileRemark = 'Mobile Phone No length mistake' then 1 else 0 end)       
    + Max(Case when StudProfileRemark = 'DOB Mistake' then 1 else 0 end)       
     + Max(Case when StudProfileRemark = 'AddressMistake' then 1 else 0 end)   
      + Max(Case   
      when StudProfileRemark  <> 'NameMistake'  
      and StudProfileRemark <> 'Mobile Phone No length mistake'   
      and StudProfileRemark <> 'DOB Mistake'  
      and StudProfileRemark <> 'AddressMistake'  
      and StudProfileRemark <>''  
      then 1 else 0 end)  cnt  
 from mycte1 t  
 Group by StudID, StudName, StudProfileRemarks, RemarksDate ,Remarksplit_item1,Remarksplit_item2,Remarksplit_item3

    228437-image.png

    Bert Zhou

    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jingyang Li 5,891 Reputation points
    2022-08-04T14:19:30.763+00:00 
     If(OBJECT_ID('tempdb..#StudProfileReviewer') Is Not Null)  
 Begin  
     Drop Table #StudProfileReviewer  
 End  
      
 create table #StudProfileReviewer  
 (  
     StudID [INT],   
     StudName Varchar(50),   
     StudProfileRemarks Varchar(Max),   
     RemarksDate DatetIme,   
     RemarksSplit_NameMistakes Varchar(100),  
     RemarksSplit_MobilePhoneMistakes Varchar(100),  
     RemarksSplit_DOBMistakes Varchar(100),  
     RemarksSplit_AddressMistakes Varchar(100),  
     RemarksSplit_OtherMistakes Varchar(100),  
     RemarksCount INT  
 )  
      
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1001,'SriRaman','Mobile Phone No length mistake | AddressMistake', '2022/05/03')   
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1007,'Janani','NameMistake | Mobile Phone No length mistake | AddressMistake', '2022/06/12')  
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1003,'Guru','DOB Mistake | Pincode Wrong ', '2022/03/11')  
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1002,'Sankaran','Vehile Reg. No is Wrong ', '2022/03/11')  
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1004,'Zaheer','', '2021/01/08')  
      
 Select   
  
 StudID,	StudName,	StudProfileRemarks,	RemarksDate ,  
   
   
 Max(Case when StudProfileRemark = 'NameMistake' then 1 else 0 end) RemarksSplit_NameMistakes  
  ,Max(Case when StudProfileRemark = 'Mobile Phone No length mistake' then 1 else 0 end) RemarksSplit_MobilePhoneMistakes	  
   ,Max(Case when StudProfileRemark = 'DOB Mistake' then 1 else 0 end) RemarksSplit_DOBMistakes	  
    ,Max(Case when StudProfileRemark = 'AddressMistake' then 1 else 0 end) RemarksSplit_AddressMistakes  
	 ,Max(Case   
	 when StudProfileRemark  <> 'NameMistake'  
	 and StudProfileRemark <> 'Mobile Phone No length mistake'   
	 and StudProfileRemark <> 'DOB Mistake'  
	 and StudProfileRemark <> 'AddressMistake'  
	 and StudProfileRemark <>''  
	 then 1 else 0 end)  	RemarksSplit_OtherMistakes  
	 ,  Max(Case when StudProfileRemark = 'NameMistake' then 1 else 0 end)   
  + Max(Case when StudProfileRemark = 'Mobile Phone No length mistake' then 1 else 0 end) 	  
   + Max(Case when StudProfileRemark = 'DOB Mistake' then 1 else 0 end) 	  
    + Max(Case when StudProfileRemark = 'AddressMistake' then 1 else 0 end)   
	 + Max(Case   
	 when StudProfileRemark  <> 'NameMistake'  
	 and StudProfileRemark <> 'Mobile Phone No length mistake'   
	 and StudProfileRemark <> 'DOB Mistake'  
	 and StudProfileRemark <> 'AddressMistake'  
	 and StudProfileRemark <>''  
	 then 1 else 0 end)  cnt  
from (  
select StudID,	StudName,	StudProfileRemarks,	RemarksDate  
,trim(value) StudProfileRemark  from [#StudProfileReviewer]  
cross apply  string_split(StudProfileRemarks,'|')   d   
   
)  
t  
Group by StudID,	StudName,	StudProfileRemarks,	RemarksDate
    0 comments
  2. Yitzhak Khabinsky 25,026 Reputation points
    2022-08-04T15:13:34.983+00:00

    Hi @Ayyappan CNN ,

    Please try the following solution. It is using XML and XQuery functionality of SQL Server.

    SQL 

    -- DDL and sample data population, start  
DECLARE @tbl TABLE  
(  
	StudID INT PRIMARY KEY,   
	StudName VARCHAR(50),   
	StudProfileRemarks VARCHAR(MAX),   
	RemarksDate DATE  
	--RemarksSplit_NameMistakes Varchar(100),  
	--RemarksSplit_MobilePhoneMistakes Varchar(100),  
	--RemarksSplit_DOBMistakes Varchar(100),  
	--RemarksSplit_AddressMistakes Varchar(100),  
	--RemarksSplit_OtherMistakes Varchar(100),  
	--RemarksCount INT  
);  
      
INSERT @tbl (StudID,StudName,StudProfileRemarks,RemarksDate) VALUES  
(1001,'SriRaman','Mobile Phone No length mistake | AddressMistake', '2022-05-03'),  
(1007,'Janani','NameMistake | Mobile Phone No length mistake | AddressMistake', '2022-06-12'),  
(1003,'Guru','DOB Mistake | Pincode Wrong ', '2022-03-11'),  
(1002,'Sankaran','Vehile Reg. No is Wrong ', '2022-03-11'),  
(1004,'Zaheer','', '2021-01-08');  
-- DDL and sample data population, end  
  
DECLARE @separator CHAR(3) = ' | '  
	, @NameMistakes VARCHAR(100) = 'NameMistake'  
	, @MobilePhoneMistakes VARCHAR(100) = 'Mobile Phone'  
	, @DOBMistakes VARCHAR(100) = 'DOB Mistake'  
	, @AddressMistakes VARCHAR(100) = 'AddressMistake';  
  
SELECT t.* --, c  
   , NameMistakes = TRIM(c.query('for $x in /root/r[contains(text()[1], sql:variable("@NameMistakes"))]/text() return $x')  
      .value('.','VARCHAR(100)'))  
   , MobilePhoneMistakes = TRIM(c.query('for $x in /root/r[contains(text()[1], sql:variable("@MobilePhoneMistakes"))]/text() return $x')  
      .value('.','VARCHAR(100)'))  
   , DOBMistakes = TRIM(c.query('for $x in /root/r[contains(text()[1], sql:variable("@DOBMistakes"))]/text() return $x')  
      .value('.','VARCHAR(100)'))  
   , AddressMistakes = TRIM(c.query('for $x in /root/r[contains(text()[1], sql:variable("@AddressMistakes"))]/text() return $x')  
      .value('.','VARCHAR(100)'))  
   , OtherMistakes = TRIM(c.query('for $x in /root/r[not(contains(text()[1], sql:variable("@NameMistakes"))) and  
			not(contains(text()[1], sql:variable("@MobilePhoneMistakes"))) and  
			not(contains(text()[1], sql:variable("@DOBMistakes"))) and  
			not(contains(text()[1], sql:variable("@AddressMistakes")))]/text() return $x')  
      .value('.','VARCHAR(100)'))  
	, RemarksCount = c.value('count(/root/r[text()])', 'INT')  
FROM @tbl AS t  
CROSS APPLY (SELECT TRY_CAST('<root><r>' +   
      REPLACE(StudProfileRemarks, @separator, '</r><r>') +   
      '</r></root>' AS XML)) AS t2(c)  
ORDER BY StudID;
    0 comments
  3. Jingyang Li 5,891 Reputation points
    2022-08-04T15:55:21.23+00:00 
     If(OBJECT_ID('tempdb..#StudProfileReviewer') Is Not Null)  
 Begin  
     Drop Table #StudProfileReviewer  
 End  
      
 create table #StudProfileReviewer  
 (  
     StudID [INT],   
     StudName Varchar(50),   
     StudProfileRemarks Varchar(Max),   
     RemarksDate DatetIme,   
     RemarksSplit_NameMistakes Varchar(100),  
     RemarksSplit_MobilePhoneMistakes Varchar(100),  
     RemarksSplit_DOBMistakes Varchar(100),  
     RemarksSplit_AddressMistakes Varchar(100),  
     RemarksSplit_OtherMistakes Varchar(100),  
     RemarksCount INT  
 )  
      
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1001,'SriRaman','Mobile Phone No length mistake | AddressMistake', '2022/05/03')   
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1007,'Janani','NameMistake | Mobile Phone No length mistake | AddressMistake', '2022/06/12')  
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1003,'Guru','DOB Mistake | Pincode Wrong ', '2022/03/11')  
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1002,'Sankaran','Vehile Reg. No is Wrong ', '2022/03/11')  
 Insert into [#StudProfileReviewer] (StudID,StudName,StudProfileRemarks,RemarksDate)  Values(1004,'Zaheer','', '2021/01/08')  
     
     
   ;with mycte as (  
    select StudID,	StudName,	StudProfileRemarks,	RemarksDate  
,trim(value) StudProfileRemark  
 FROM [#StudProfileReviewer]    
cross apply openjson('["'+(replace(StudProfileRemarks,'|','","')+'"]'))  
 )  
 Select   StudID, StudName,	StudProfileRemarks,	RemarksDate ,  
  Max(Case when StudProfileRemark = 'NameMistake' then 'NameMistake' else '' end) RemarksSplit_NameMistakes  
  ,Max(Case when StudProfileRemark = 'Mobile Phone No length mistake' then 'Mobile Phone No length mistake' else '' end) RemarksSplit_MobilePhoneMistakes	  
   ,Max(Case when StudProfileRemark = 'DOB Mistake' then 'DOB Mistake' else '' end) RemarksSplit_DOBMistakes	  
    ,Max(Case when StudProfileRemark = 'AddressMistake' then 'AddressMistake' else '' end) RemarksSplit_AddressMistakes  
	 ,Max(Case   
	 when StudProfileRemark  <> 'NameMistake'  
	 and StudProfileRemark <> 'Mobile Phone No length mistake'   
	 and StudProfileRemark <> 'DOB Mistake'  
	 and StudProfileRemark <> 'AddressMistake'  
	 and StudProfileRemark <>''  
	 then StudProfileRemark else '' end)  	RemarksSplit_OtherMistakes  
	 ,  Max(Case when StudProfileRemark = 'NameMistake' then 1 else 0 end)   
  + Max(Case when StudProfileRemark = 'Mobile Phone No length mistake' then 1 else 0 end) 	  
   + Max(Case when StudProfileRemark = 'DOB Mistake' then 1 else 0 end) 	  
    + Max(Case when StudProfileRemark = 'AddressMistake' then 1 else 0 end)   
	 + Max(Case   
	 when StudProfileRemark  <> 'NameMistake'  
	 and StudProfileRemark <> 'Mobile Phone No length mistake'   
	 and StudProfileRemark <> 'DOB Mistake'  
	 and StudProfileRemark <> 'AddressMistake'  
	 and StudProfileRemark <>''  
	 then 1 else 0 end)  cnt  
from mycte t  
Group by StudID, StudName, StudProfileRemarks, RemarksDate
    0 comments
  4. Ayyappan CNN 61 Reputation points
    2022-08-05T06:45:21.327+00:00

    Friends,

    Thanks for your prompt responses, can I one more output like this;

    228360-image.png