Playing with Sql String | Splitting String in SQL

Playing with Sql String  | Splitting String in SQL

How to Split value of column in Sql Query?

Requirement : 

Sometime we need to filter/ split the values of column, for example, we need to take only first name from the column since there is value in the column is firstname.lastname and we need to show only first name, middile name and last name.

We have played with CharIndex and Substring to get separate strings with dot(.) .

Table Example :

SQL Split

SQL Split

in the above example of table, we will try to get first name, middle name and last name separately .

Let’s try to get first name from name column of the table

Sql Query:

SELECT Name, SUBSTRING(Name, 0,CHARINDEX('.',Name)) FROM Testtbl
Get First name

Get First name

Let’s try to get middle name from name column of the table

Sql Query:

SELECT 
 Name
 ,SUBSTRING(Name, CHARINDEX('.',Name)+1,case when CHARINDEX('.',Name,CHARINDEX('.',Name)+1)<=0 then len(name)else CHARINDEX('.',Name,CHARINDEX('.',Name)+1)-1 end -CHARINDEX('.',Name)) 
 
FROM 
 Testtbl
Get Middle Name

Get Middle Name


Let’s try to get Last name from name column of the table

Sql Query:

SELECT
Name
,SUBSTRING(Name, CHARINDEX('.',Name)+1,case when CHARINDEX('.',Name,CHARINDEX('.',Name)+1)<=0 then len(name)else CHARINDEX('.',Name,CHARINDEX('.',Name)+1)-1 end -CHARINDEX('.',Name))
,reverse(substring(REVERSE(Name),0,charindex('.',REVERSE(Name))))
FROM
Testtbl
Get Last name

Get Last name

Leave a Reply

Your email address will not be published. Required fields are marked *

2 + 4 =


All Rights Reserved 2019 | DesignzRush (OPC) Pvt. Ltd.
Inline
Inline