Split single String column into Multiple Columns in sql server

We have a log table in that table we are logging the information with the Pipe (|) separated value in that column. We want to split this single column into multiple columns.

See the example.

We have an employee table in that we are storing the address with Pipe separated.  

Now we need to split the Emp_add into multiple columns.

Using xml_data.value we can achieve the same.

See the below sql script

; with cte_emp as   (   SELECT   [id]       ,[name]       ,[Mob]                  ,cast('<N>'+replace([Emp_Add],'|','</N><N>')+'</N>' as XML) as xml_data   FROM [dbo].[emp]   )   select      [id] ,[name],[Mob]   ,replace(xml_data.value('/N[1]','varchar(50)'),'Vill : ','') as vill   ,replace(xml_data.value('/N[2]','varchar(50)'),'Post : ','') as Post   ,replace(xml_data.value('/N[3]','varchar(50)'),'Thana : ','') as Thana   ,replace(xml_data.value('/N[4]','varchar(50)'),'Dist: ','') as Dist   ,replace(xml_data.value('/N[5]','varchar(50)'),'Pin : ','') as Pin   ,replace(xml_data.value('/N[6]','varchar(50)'),'State : ','') as State   from cte_emp

 Running the script.  

Got the expected result.  

Parsename function in sql server

 Its Return Part of an Object Name in SQL Server. We know we are writing the SQL server full qualifier name in the four parts. A full qualifier name is denoted in four parts [Server Name].[Database Name].[Schema Name].[Database object Name]. With the help of Parsename function, we can easily server, schema, database, and table name.

Syntax of this function

PARSENAME (‘object_name’, object_piece)

Where object_name is the (optionally-qualified) object name and object_piece is the part that you want returned.

The object_piece argument must be an int between 1 and 4. The value determines which part of the object name to return. These values correspond to the object part as follows:

Value	Object Part
1	Object name
2	Schema name
3	Database name
4	Server name

Let's see the example

DECLARE @object_name char(50) = 'BAGESH-PC.dbo.Test.Emp_Stg'; SELECT   PARSENAME(@object_name, 4) AS [Server],   PARSENAME(@object_name, 3) AS [Schema],   PARSENAME(@object_name, 2) AS [Database],   PARSENAME(@object_name, 1) AS [Object];

 

  
Got the expected result.