Substring after a space

Question

I want to split a string and get the value after the space. I can get the value before space but how to get the string after space.

DECLARE @string varchar(20) 
SELECT @string = 'Test Name'
SELECT SUBSTRING(@string, 0, CHARINDEX(' ', @string))

Edit: I am using SQLServer 2012

Answer 1

Here's a version using SUBSTRING and CHARINDEX for versions of SQL SERVER prior to SQL Server 2016 when STRING_SPLIT was introduced.

Either of the following works:

SELECT  SUBSTRING(@string, CHARINDEX(' ', @string) +1, 20)

SELECT  SUBSTRING(@string, CHARINDEX(' ', @string) +1, DATALENGTH(@string) - CHARINDEX(' ', @string) +1 )

Edited to add description as per Rao's comment

We need to find the position of the space using CHARINDEX (which returns a number representing where the string (the space) begins. However the string that we want to select begins after the space, therefore we must add 1 (+1) to the starting position of our SUBSTRING so that the starting position becomes CHARINDEX(' ', @string) + 1.

The 3rd argument for SUBSTRING is the length of the string to select, in the first case I just assume that the string you specified as a varchar(20) can be no longer than 20 characters hence me using 20. Note SUBSTRING will not try to select characters past the end of the string, so it is safe to specify a length longer than the number of characters left.

My second example gets the length of the string to select based on it being the length of the total string (DATALENGTH) minus the number of characters before our selection (CHARINDEX + 1)

Answer 2

I find the

SELECT  SUBSTRING(@string, CHARINDEX(' ', @string) +1, DATALENGTH(@string) - CHARINDEX(' ', @string) +1 )

syntax is verbose for "get remainder of string FROM given position", so I "delete" the part BEFORE position instead:

SELECT  STUFF(@string, 1, CHARINDEX(' ', @string), '')

STUFF ( character_expression , start , length , replaceWith_expression )

Both methods work the same including where CHARINDEX has no match