T-SQL substring - separating first and last name

Question

I have a column which has FirstName and LastName together  I m writing a report to separate the FirstName And LastName  How do I get the FirstName and LastName separated in T-SQL

User · Answer

You could do this if firstname and surname are separated by space   SELECT SUBSTRING FirstAndSurnameCol  0  CHARINDEX      FirstAndSurnameCol   Firstname  SUBSTRING FirstAndSurnameCol  CHARINDEX      FirstAndSurnameCol  1  LEN FirstAndSurnameCol   Surname FROM

User · Answer

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

User · Answer

The easiest way I can find to do it is   SELECT    SUBSTRING FullName  1  CHARINDEX      FullName  - 1  AS FirstName    REVERSE SUBSTRING REVERSE FullName   1  CHARINDEX      REVERSE FullName   - 1   AS LastName FROM    PERSON TABLE

User · Answer

You may have problems if the Fullname doesn t contain a space   Assuming the whole of FullName goes to Surname if there is no space and FirstName becomes an empty string  then you can use this   SELECT   RTRIM LEFT FullName  CHARINDEX      FullName    AS FirstName    SUBSTRING FullName  CHARINDEX      FullName    1  8000  AS LastName FROM   MyNameTable

User · Answer

Here is a more elaborated solution with a SQL function   GetFirstname  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        amp gt  0 THEN left  ltrim  rtrim   FullName      charindex       ltrim  rtrim   FullName        - 1   ELSE    END     RETURN  RetName   END   GetLastName  CREATE FUNCTION  dbo   ufn GetLastName          FullName varchar 500        RETURNS varchar 500    AS   BEGIN    DECLARE  RetName varchar 500      IF right ltrim rtrim  FullName    2   amp lt  amp gt    I      BEGIN     set  RetName   left        CASE WHEN        charindex       reverse  ltrim  rtrim         replace  replace  replace  replace  replace  replace   FullName    Jr           III           II           Jr            Sr         Sr                       amp gt  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                       amp gt  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   USE    SELECT dbo ufn GetFirstName Fullname  as FirstName  dbo ufn GetLastName Fullname  as LastName FROM  Names

User · Answer

For the last name as in US standards  i e   last word in the  Full Name  column  and considering first name to include a possible middle initial  middle name  etc    SELECT DISTINCT               Full Name               REVERSE  Full Name                     --  to visualize what the formula is doing              CHARINDEX      REVERSE  Full Name      --  finds the last space in the string               Last Name         REVERSE RTRIM LTRIM LEFT REVERSE  Full Name    CHARINDEX      REVERSE  Full Name                      First Name        RTRIM LTRIM LEFT  Full Name   LEN  Full Name   - CHARINDEX      REVERSE  Full Name         FROM      Note that this assumes  Full Name  has no spaces before or after the actual string  Otherwise  use RTRIM and LTRIM to remove these

User · Answer

This should work   Select       LTRIM RTRIM SUBSTRING FullName  0  CHARINDEX      FullName     As FirstName     LTRIM RTRIM SUBSTRING FullName  CHARINDEX      FullName  1  8000   As LastName FROM TABLE   Edit  Adopted Aaron s and Jonny s hint with the fixed length of 8000 to avoid unnecessary calculations

User · Answer

The code below works with Last  First M name strings  Substitute  Name  with your name string column name  Since you have a period as a final character when there is a middle initial  you would replace the 2 s with 3 s in each of the lines  2  6  and 8 - and change  RIGHT Name  1   to  RIGHT Name  2   in line 8   SELECT  SUBSTRING Name  1  CHARINDEX      Name  - 1  LastName   CASE WHEN LEFT RIGHT Name  2   1   lt  gt           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

User · Answer

Assuming the FirstName is all of the characters up to the first space   SELECT   SUBSTRING username  1  CHARINDEX      username  - 1  AS FirstName    SUBSTRING username  CHARINDEX      username    1  LEN username   AS LastName FROM   whereever

User · Answer

This will take care of names like  Firstname Z  Lastname  and  First Z Last   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

User · Answer

I think below query will be helpful to split FirstName and LastName from FullName even if there is only FirstName  For example   Philip John  can be split into Philip and John  But if there is only Philip  because of the charIndex of Space is 0  it will only give you      Try the below one   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   Hope this will help you

[sql-server-2008] T-SQL substring - separating first and last name

Examples related to sql-server-2008

Examples related to tsql