CODEWITHSUNDEEP
sql-serverstringsql-server-2012substring
BRDroid
BRDroid

Reputation: 4388

Substring after a space

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

Upvotes: 8

Views: 76287

Answers (3)

Kristen
Kristen

Reputation: 4291

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

Upvotes: 3

Steve Ford
Steve Ford

Reputation: 7753

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)

Upvotes: 21

Steven Lemmens
Steven Lemmens

Reputation: 1491

Don't use substring, but use string_split. This returns an array of strings. 

 SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

This returns the following array: 

Lorem
ipsum
dolor
sit
amet.

More information can be found here: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql

Upvotes: 1

Related Questions