March 15, 2014

SQL Server - Split the string

To split the string in SQL Server, you can use CHARINDEX and SUBSTRING function.

CHARINDEX function helps to find the position of the character in the string. This function accepts two arguments. First one is an expression to find and second is an expression to search, based on these argument it will return the first index position of the character of the first argument.


CHARINDEX( expressiontofind, expressiontosearch, start_location)

start_location is optional, if provided then search should start from there otherwise from start.

For example:

SELECT CHARINDEX(' ','Avesh Dhakal') --Result: 6

SELECT CHARINDEX(' ','Avesh Dhakal, 4') -- Result: 6

SELECT CHARINDEX(' ','Avesh Dhakal, 7') -- Result: 0

SUBSTRING function returns the part of the character of the given expression. This function accepts three arguments.


SUBSTRING(expression, start, length)

expression: Its a given string.

start: Its a start position. If the position is less than one, the returned character will begin from first position of the string.

length: It specifies how many character of the string will be returned.

For Example:

SELECT SUBSTRING('Avesh Dhakal',3,6) -- Result: esh Dh

Now, using CHARINDEX and  SUBSTRING function, lets do an example to split the string:

For Example:

DECLARE @String VarChar(255) = 'Hello! How are you?'

SELECT SUBSTRING(@String , CHARINDEX('!', @String ) + 2, 50)

Result: How are you?

No comments: