Finding a space in T-SQL

[Solel Software]

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!

[Ibrahim Shameeque]

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)))))
--
Ibrahim

"Solel Software" wrote:

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!

[Mark Rae]

"Solel Software" <So***********@newsgroup.nospam> wrote in message
news:55**********************************@microsof t.com...

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

[Solel Software]

Thank you so much Ibrahim! This works great.
"Ibrahim Shameeque" wrote:

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)))))
--
Ibrahim

"Solel Software" wrote:

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!