Pipe seperate dat

Question

Hi,

I have a data in this format with 3 fields

Create table students (Id int, role varchar (100), name varchar (100))

Insert into students values
1,'
Student. | Teacher | other', 'dinesh'
2, 'Student. | Teacher | other', 'dinu'
3 'Student. | Teacher | other', 'kal'

Output

Id. Role. Name

1. Student. Dinesh
2. Teacher. Dinu
3. Other. Kal

Answer 1

Try the following script if my guessing is correct:

DECLARE @Student TABLE (  
	[Id] int,   
	[Role] varchar (100),   
	[Name] varchar (100)  
);  
  
INSERT INTO @Student VALUES  
(1, 'Student | Teacher | other', 'dinesh'),  
(2, 'Student | Teacher | other', 'dinu'),  
(3, 'Student | Teacher | other', 'kal');  
  
SELECT Id,   
	   CASE   
			WHEN [Id] = 1 THEN RTRIM(LEFT([Role], CHARINDEX('|', [Role]) - 1))   
			WHEN [Id] = 2 THEN LTRIM(RTRIM(SUBSTRING([Role], CHARINDEX('|', [Role]) + 1, CHARINDEX('|', [Role], CHARINDEX('|', [Role]) + 1 -CHARINDEX('|', [Role])))))  
			ELSE LTRIM(REVERSE(LEFT(REVERSE([Role]), CHARINDEX('|', REVERSE([Role])) - 1)))  
	   END AS [Role],  
	   [Name]  
FROM @Student;  
GO  

Answer 2

Hi @dinesh ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @Student TABLE (  
     [Id] int,   
     [Role] varchar (100),   
     [Name] varchar (100)  
 );  
      
 INSERT INTO @Student VALUES  
 (1, 'Student | Teacher | other', 'dinesh'),  
 (2, 'Student | Teacher | other', 'dinu'),  
 (3, 'Student | Teacher | other', 'kal');  
-- DDL and sample data population, end  
  
SELECT Id  
	, Role = PARSENAME(c, 4 - id)  
	, Name  
FROM @Student AS t1  
CROSS APPLY (SELECT REPLACE(role, ' | ','.')) AS t(c);  

Output

+----+---------+--------+  
| Id |  Role   |  Name  |  
+----+---------+--------+  
|  1 | Student | dinesh |  
|  2 | Teacher | dinu   |  
|  3 | other   | kal    |  
+----+---------+--------+  

Answer 3

Hi @
If you are using SQL Server 2016 (13.x) and later,you can have a try on STRING_SPLIT function,like this:

Create table #students (Id int, role varchar (100), name varchar (100))  
Insert into #students values  
 (1, 'Student | Teacher | other', 'dinesh'),  
 (2, 'Student | Teacher | other', 'dinu'),  
 (3, 'Student | Teacher | other', 'kal');  
  
;WITH CTE AS  
(  
 SELECT Id,name,TRIM(VALUE)AS Role,ROW_NUMBER()OVER(PARTITION BY Id ORDER BY CHARINDEX('Student,Teacher,other',TRIM(VALUE)))AS RNum  
 FROM #students CROSS APPLY STRING_SPLIT(role,'|')C  
)  
SELECT Id,Role,Name  
FROM CTE  
WHERE Id = RNum  

If you want to capitalize the first letter,then you need to modify the Select statement:

;WITH CTE AS  
(  
 SELECT Id,name,TRIM(VALUE)AS Role,ROW_NUMBER()OVER(PARTITION BY Id ORDER BY CHARINDEX('Student,Teacher,other',TRIM(VALUE)))AS RNum  
 FROM #students CROSS APPLY STRING_SPLIT(role,'|')C  
)  
SELECT Id,  
       UPPER(LEFT(Role,1))+LOWER(SUBSTRING(Role,2,LEN(Role)))Role,  
	   UPPER(LEFT(Name,1))+LOWER(SUBSTRING(Name,2,LEN(Name)))Name  
FROM CTE  
WHERE Id = RNum  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.