T-SQL Query Problem?

If I exec this query I am getting a output, but not the desired output. I would like to get the output as:

FirstName                            LastName


ADT                                  NULL 
----------------------------------------------------------------------------

Here is my query:

DECLARE @FullName VARCHAR(100)

SET @FullName = 'ADT'

SELECT SUBSTRING(@FullName, 1, NULLIF(CHARINDEX(' ', @FullName) - 1, -1)) AS [FirstName],

SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) AS [LastName]

The output will be for this query is:

FirstName    LastName

NULL           ADT
-------------------------------------开发者_运维知识库-----

Thanks in advance for your help.

---

Perhaps not the most efficient but meets the requirement.

DECLARE @FullName VARCHAR(100)

SET @FullName = 'ADT'

SELECT SUBSTRING(@FullName, 0, ISNULL(NULLIF(CHARINDEX(' ', @FullName), 0), LEN(@FullName) + 1)) AS [FirstName]
, SUBSTRING(@FullName, NULLIF(CHARINDEX(' ', @FullName) + 1, 1), LEN(@FullName) - CHARINDEX(' ', @FullName) + 1) AS [LastName]

Here is an updated statement based on the clarified requirement. The REVERSE string function comes in handy and the LTRIM is due to a leading space issue on the LastName column.

DECLARE @FullName VARCHAR(100)

SET @FullName = 'ADT SAMPLE DATABASE CASE'

SELECT SUBSTRING(@FullName, 0, ISNULL(NULLIF(LEN(@FullName) + 1 - CHARINDEX(' ', REVERSE(@FullName)), 0), LEN(@FullName) + 1)) AS [FirstName]
, LTRIM(SUBSTRING(@FullName, NULLIF(LEN(@FullName) - CHARINDEX(' ', REVERSE(@FullName)), LEN(@FullName)) + 1, LEN(@FullName) - (LEN(@FullName) - CHARINDEX(' ', REVERSE(@FullName))))) AS [LastName]

---

Two options:

• Add a special-case check if @FullName contains a space

or

• Remove the special case by appending a space to the end.

  declare @fullName varchar(100) = 'ADT'
  select 
      LEFT(@fullName, charindex(' ',@fullName+' ')-1) as FirstName,
      SUBSTRING(@fullname,charindex(' ',@fullName+' ')+1,LEN(@fullname)) as last
  

---

You Mean ADT SAMPLE DATABASE AS FirstName and CASE AS LastName