Finding a space in T-SQL

[Guest]

Hello,

If I have a string "Mr. Bill Gates" stored as an nvarchar variable how would I split it into two strings "Mr. Bill" and "Gates" using T-SQL? I assume I could use Substring but to use that I would have to know the index of the last whitespace and don't know how to get that index. Any suggestions would be appreciated. Thanks!

 

[Guest]

Hi
Here is a quick fix. There could be a better way.

declare @v varchar(50)
select @v='Mr. Bill Gates'

select ltrim(reverse(substring(reverse(@v),1,charindex(' ',reverse(@v)))))

 

[Mark Rae]

Hello,

If I have a string "Mr. Bill Gates" stored as an nvarchar variable how

would I split it into two strings "Mr. Bill" and "Gates" using T-SQL? I
assume I could use Substring but to use that I would have to know the index
of the last whitespace and don't know how to get that index. Any
suggestions would be appreciated. Thanks!

DECLARE @strInput varchar(50)
SELECT @strInput = 'Mr. Bill Gates'

SELECT LEFT(@strInput, CHARINDEX(' ', @strInput) - 1) AS LeftSide
SELECT SUBSTRING(@strInput, CHARINDEX(' ', @strInput) + 1,
DATALENGTH(@strInput) - CHARINDEX(' ', @strInput)) AS RightSide

 

[Guest]

Thank you so much Ibrahim! This works great.

Hi
Here is a quick fix. There could be a better way.

declare @v varchar(50)
select @v='Mr. Bill Gates'

select ltrim(reverse(substring(reverse(@v),1,charindex(' ',reverse(@v)))))