Example : The last name is 'Daniel Thomas Kon'
Output is expecting based on this condition - > if less than or equal to 3 put 1st word in last name and remaining will be in firstname

Here the output will be--

last name	first name
Daniel	Thomas Kon

Here the output will be--

last name	first name
Daniel	Thomas Kon

CodePudding user response：

If you want the last one-or-two words as first names and the preceding words as last names then you can use a mix of greedy and non-greedy qualifiers in a regular expression:

SELECT REGEXP_SUBSTR(name, '^(\S (\s \S )*?)\s (\S (\s \S )?)$', 1, 1, NULL, 1) AS last_names,
       REGEXP_SUBSTR(name, '^(\S (\s \S )*?)\s (\S (\s \S )?)$', 1, 1, NULL, 3) AS first_names
FROM   table_name

or, without regular expressions (which is more to type but will be faster):

SELECT CASE
       WHEN sep1 = 0      THEN name
       WHEN sep_last2 = 0 THEN SUBSTR(name, 1, sep1 - 1)
       ELSE                    SUBSTR(name, 1, sep_last2 - 1)
       END AS last_names,
       CASE
       WHEN sep1 = 0      THEN NULL
       WHEN sep_last2 = 0 THEN SUBSTR(name, sep1   1)
       ELSE                    SUBSTR(name, sep_last2   1)
       END AS first_names
FROM   (
  SELECT name,
         INSTR(name, ' ', 1, 1) AS sep1,
         INSTR(name, ' ', -1, 2) AS sep_last2
  FROM   table_name
)

Which, for the sample data:

CREATE TABLE table_name (name) AS
  SELECT 'Daniel Thomas Kon'             FROM DUAL UNION ALL
  SELECT 'Abbot Alice'                   FROM DUAL UNION ALL
  SELECT 'Baron Betty Beryl Becky'       FROM DUAL UNION ALL
  SELECT 'Count Carol Chloe Clara Carly' FROM DUAL;

Both output:

LAST_NAMES	FIRST_NAMES
Daniel	Thomas Kon
Abbot	Alice
Baron Betty	Beryl Becky
Count Carol Chloe	Clara Carly

---

If you want to have at most 2 last-names then:

SELECT CASE
       WHEN sep1 = 0 THEN name
       WHEN sep3 = 0 THEN SUBSTR(name, 1, sep1 - 1)
       ELSE               SUBSTR(name, 1, sep2 - 1)
       END AS last_names,
       CASE
       WHEN sep1 = 0 THEN NULL
       WHEN sep3 = 0 THEN SUBSTR(name, sep1   1)
       ELSE               SUBSTR(name, sep2   1)
       END AS first_names
FROM   (
  SELECT name,
         INSTR(name, ' ', 1, 1) AS sep1,
         INSTR(name, ' ', 1, 2) AS sep2,
         INSTR(name, ' ', 1, 3) AS sep3
  FROM   table_name
)

Which outputs:

LAST_NAMES	FIRST_NAMES
Daniel	Thomas Kon
Abbot	Alice
Baron Betty	Beryl Becky
Count Carol	Chloe Clara Carly

fiddle

CodePudding user response：

Please check below

DECLARE @Table VARCHAR(100) = 'Daniel Thomas Kon'
DECLARE @PartialString VARCHAR(100) = SUBSTRING(@Table, CHARINDEX(' ', @Table)   1, len(@Table))

SELECT CHARINDEX(' ', SUBSTRING(@Table, CHARINDEX(' ', @Table)   1, len(@Table)))   1
    ,CASE 
        WHEN (len(@Table) - len(replace(@Table, ' ', ''))   1) = 3
            THEN SUBSTRING(@Table, CHARINDEX(' ', @Table)   1, len(@Table))
        WHEN (len(@Table) - len(replace(@Table, ' ', ''))   1) > 3
            THEN SUBSTRING(@PartialString, CHARINDEX(' ', @PartialString)   1, len(@PartialString))
        END AS firstname
    ,CASE 
        WHEN (len(@Table) - len(replace(@Table, ' ', ''))   1) = 3
            THEN SUBSTRING(@Table, 0, CHARINDEX(' ', @Table)   1)
        WHEN (len(@Table) - len(replace(@Table, ' ', ''))   1) > 3
            THEN SUBSTRING(@Table, 0, len(@PartialString) - 1)
        END AS lastname

Please check on below link

https://dbfiddle.uk/BJbHD8J0