sql - 将全名拆分为姓氏名字中间名和后缀

Question

如何将全名拆分为姓氏名字中间名后缀？例如： 1.Smith John T PHD 2.smith john 3.smith john T 名称以空格分隔。此名称列有这 3 种不同的类型。

Desired output:

1) Smith -- 姓 John -- 名 T -- 中间名 Phd -- 后缀

2）史密斯--姓约翰--名字

3) 史密斯--姓约翰--名字 T--中间名

标签： sqlsql-server

Answer 1

         /*
            APPROACH:
            STEP 1: DECLARE FOUR VARIABLES TO HOLD -- LAST NAME, FIRST NAME, MIDDLE 
            NAME AND SUFFIX
            STEP 2: TRAVERSE THE STRING, AND KEEP A TRACK OF NUMBER OF SPACES YOU ARE 
            GETTING
             STEP 2.1 IF SPACE_NUM = 1, INITIALIZE THE STRINGS YOU ARE VISITED UPTO 
            NOW, INTO 'LAST NAME'
             STEP 2.2 IF SPACE_NUM = 2, INITIALIZE THE STRINGS YOU ARE VISITED UPTO 
            NOW, INTO 'FIRST NAME'
             STEP 2.3 IF SPACE_NUM = 3, INITIALIZE THE STRINGS YOU ARE VISITED UPTO 
            NOW, INTO 'MIDDLE NAME'
             STEP 3. INITIALIZE THE THE LAST STRINGS YOU VISITED INTO 'SUFFIX'

            NOTE: ASSUMING THE STRINGS ARE SAME FORMAT YOU MENTIONED.
            NOTE: FOR GETTING THE FIRST STRING A.K.A LAST NAME, WE HAVE TO PUT EXTRA- 
            SPACE IN FRONT OF STRING.
         */


         DECLARE @STRING AS VARCHAR(50) = 'Smith John T PHD'
         SET @STRING = ' '+@STRING

         DECLARE @FIRST_NAME AS VARCHAR(30) = ''
         DECLARE @LAST_NAME AS VARCHAR(30) = ''
         DECLARE @MIDDLE_NAME AS VARCHAR(30) = ''
         DECLARE @SUFFIX AS VARCHAR(30) = ''


         DECLARE @I AS INT = 1
         DECLARE @COUNT AS INT =0
         DECLARE @STR AS VARCHAR(20) =''

         WHILE @I <> LEN(@STRING)+1
         BEGIN
            IF SUBSTRING(@STRING, @I, 1) = ' '
            BEGIN
                SET @COUNT = @COUNT +1
                SET @I = @I +1
                CONTINUE
            END

            SET @STR += SUBSTRING(@STRING, @I, 1)

            IF @COUNT = 1
            BEGIN
                SET @LAST_NAME += @STR
                SET @STR = ''
            END

            IF @COUNT = 2
            BEGIN
                SET @FIRST_NAME += @STR
                SET @STR = ''
            END

            IF @COUNT = 3
            BEGIN
                SET @MIDDLE_NAME += @STR
                SET @STR = ''
            END
        SET @I = @I +1
        END

   SET @SUFFIX = @STR

    SELECT @FIRST_NAME AS "FIRST NAME", @LAST_NAME AS "LAST NAME", @MIDDLE_NAME 
     "Middle Name", @SUFFIX as "Suffix";