Substring in SQL or Split the column value in SQL

Some times in SQL we need to use substring or split the selected value. For that the below methods we can use to split the string in the SQL Server. The Below table we have first name+ last name separated with | simple.  The below query I wrote to get First name from the names column. I used two methods to get the results.

SELECT * FROM [dbo].[MyTable]

ID            Names

1              vijay | sekar

2              antony | amal

3              yuvan | karthick

Splitting string using SUBSTRING options in SQL:

 Syntax:   SUBSTRING ( expression ,start , length )

Query:

SELECT SUBSTRING(Names,0,CHARINDEX('|',Names)) AS Result FROM MyTable

Result

vijay

antony

yuvan

Splitting string using LEFT, RIHGHT characters in SQL:

Syntax:  LEFT ( character_expression , integer_expression )

Query:

SELECT Names,(LEFT(Names,CHARINDEX('|',Names) -1)) AS LeftResult,

(RIGHT(Names,CHARINDEX('|',Names) -1)) AS RightResult FROM MyTable

Result

Names                                  LeftResult           RightResult

vijay | sekar                       vijay                        sekar

antony | amal                    antony                 | amal

yuvan | karthick               yuvan                    rthick

Based on your requirement you can use the splitting string option in SQL. I hope this will help you to solve your split string in SQL.

How to get string length in SQL?

The below query will help you to get the single columns string length. This is the one of easy way to get string count in SQL.

SELECT Names,LEN(Names) AS NameCount FROM MyTable

Result:

Names                                  NameCount

vijay | sekar                       13

antony | amal                    13

yuvan | karthick               16