SELECT
  SUBSTRING(username, 1, CHARINDEX(' ', username) - 1) AS FirstName,
  SUBSTRING(username, CHARINDEX(' ', username) + 1, 8000) AS LastName
FROM
  whereever

SELECT
  SUBSTRING(FullName, 1, CHARINDEX(' ', FullName) - 1) AS FirstName,
  REVERSE(SUBSTRING(REVERSE(FullName), 1, CHARINDEX(' ', REVERSE(FullName)) - 1)) AS LastName
FROM
  [PERSON_TABLE]

Select
    LTRIM(RTRIM(SUBSTRING(FullName, 0, CHARINDEX(' ', FullName)))) As FirstName
,   LTRIM(RTRIM(SUBSTRING(FullName, CHARINDEX(' ', FullName)+1, 8000)))As LastName
FROM TABLE

validate last name is blank

SELECT
person.fullName,
(CASE WHEN 0 = CHARINDEX(' ', person.fullName)
    then  person.fullName
    ELSE SUBSTRING(person.fullName, 1, CHARINDEX(' ', person.fullName)) end) as first_name,
(CASE WHEN 0 = CHARINDEX(' ', person.fullName)
    THEN ''
    ELSE SUBSTRING(person.fullName,CHARINDEX(' ', person.fullName), LEN(person.fullName) )end) last_name

FROM person

SELECT
CASE
    WHEN CHARINDEX(' ',name) = 0 THEN name
    WHEN CHARINDEX(' ',name) = PATINDEX('% _[., ]%',name) THEN RTRIM(SUBSTRING(name, 1, CHARINDEX(' ',name) + 2))
    ELSE SUBSTRING(name,1, CHARINDEX(' ',name))
END [firstname]
,CASE
    WHEN CHARINDEX(' ',name) = 0 THEN ''
    WHEN CHARINDEX(' ',name) = PATINDEX('% _[., ]%',name) THEN LTRIM(SUBSTRING(name, CHARINDEX(' ',name) + 3,1000))
    ELSE SUBSTRING(name,CHARINDEX(' ',name)+1,1000)
END [lastname]
FROM [myTable]

CREATE FUNCTION [dbo].[ufn_GetFirstName]
(
 @FullName varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @RetName varchar(500)

 SET @FullName = replace( replace( replace( replace( @FullName, '.', '' ), 'Mrs', '' ), 'Ms', '' ), 'Mr', '' )

 SELECT
  @RetName =
    CASE WHEN charindex( ' ', ltrim( rtrim( @FullName ) ) ) > 0 THEN left( ltrim( rtrim( @FullName ) ), charindex( ' ', ltrim( rtrim( @FullName  ) ) ) - 1 ) ELSE '' END

 RETURN @RetName
END

CREATE FUNCTION [dbo].[ufn_GetLastName]
(
 @FullName varchar(500)
)
RETURNS varchar(500)
AS
BEGIN
 DECLARE @RetName varchar(500)

 IF(right(ltrim(rtrim(@FullName)), 2) <> ' I')
 BEGIN
  set @RetName = left(
   CASE WHEN
    charindex( ' ', reverse( ltrim( rtrim(
    replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
    ) ) ) ) > 0
   THEN
    right( ltrim( rtrim(
    replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
    ) ) , charindex( ' ', reverse( ltrim( rtrim(
    replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
    ) ) )  ) - 1 )
   ELSE '' END
  , 25 )
 END
 ELSE
 BEGIN
  SET @RetName = left(
   CASE WHEN
    charindex( ' ', reverse( ltrim( rtrim(
    replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
    ) ) ) ) > 0
   THEN
    right( ltrim( rtrim(
    replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
    ) ) , charindex( ' ', reverse( ltrim( rtrim(
    replace( replace( replace( replace( replace( replace( replace( @FullName, ' Jr', '' ), ' III', '' ), ' II', '' ), ' I', '' ), ' Jr.', '' ), ' Sr', ''), 'Sr.', '')
    ) ) )  ) - 1 )
   ELSE '' END
  , 25 )
 END

 RETURN @RetName
END

SELECT dbo.ufn_GetFirstName(Fullname) as FirstName, dbo.ufn_GetLastName(Fullname) as LastName FROM #Names

declare @FullName varchar(100)='Philp John'

Select
    LTRIM(RTRIM(SUBSTRING(@FullName, 0, CHARINDEX(' ', @FullName+' ')))) As FirstName
,   LTRIM(RTRIM(SUBSTRING(@FullName, CHARINDEX(' ', @FullName+' ')+1, 8000)))As LastName

SELECT SUBSTRING(FirstAndSurnameCol, 0, CHARINDEX(' ', FirstAndSurnameCol)) Firstname,
SUBSTRING(FirstAndSurnameCol, CHARINDEX(' ', FirstAndSurnameCol)+1, LEN(FirstAndSurnameCol)) Surname FROM ...

SELECT
  RTRIM(LEFT(FullName, CHARINDEX(' ', FullName))) AS FirstName,
  SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 8000) AS LastName
FROM
  MyNameTable;

SELECT  SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1) LastName ,
CASE WHEN LEFT(RIGHT(Name, 2), 1) <> ' '
     THEN LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99))
     ELSE LEFT(LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)),
               LEN(LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)))
               - 2)
END FirstName ,
CASE WHEN LEFT(RIGHT(Name, 2), 1) = ' ' THEN RIGHT(Name, 1)
     ELSE NULL
END MiddleName