Home / Questions / 320858
Accepted
Hackerman
Asked: 2020-01-10 12:02:55 +0800 CST 2020-01-10 12:02:55 +0800 CST

Separate words from a field

  • 772

I have a last name field, this field usually has two last names

MALDONADO VELASQUEZ

what I want to achieve is to separate them, that is to say that in one field I have surname 1 and in the other surname 2

FIELD 1 MALDONADO
FIELD 2 VELASQUEZ

I managed to capture the first surname with this Script, but the second surname I could not capture

SELECT LEFT(ape_empl, ISNULL(NULLIF(CHARINDEX(' ',ape_empl ) - 1, -1), LEN(ape_empl))) FROM [dbo].[bi_emple]
sql-server

2 Answers

  1. Best Answer

    If your requirement does not go beyond 4 surnames, the simplest thing is to use PARSENAME(), previously replacing the spaces with a period .:

    declare @Apellidos varchar(255) = 'MALDONADO VELASQUEZ'

select  parsename(replace(@Apellidos, ' ', '.') , 1) as Apellido1, 
        parsename(replace(@Apellidos, ' ', '.') , 2) as Apellido2,
        parsename(replace(@Apellidos, ' ', '.') , 3) as Apellido3,
        parsename(replace(@Apellidos, ' ', '.') , 4) as Apellido4


Apellido1 Apellido2 Apellido3 Apellido4
--------- --------- --------- ---------
VELASQUE  MALDONAD  NULL      NULL
    • 3

  2. You can use LEFT() as you were doing but combined with the SUBSTRING() function and the CHARINDEX() function .

    SELECT LEFT(ape_empl,CHARINDEX(' ',ape_empl)-1) AS APELLIDO1,
SUBSTRING(ape_empl,CHARINDEX(' ',ape_empl)+1,LEN(ape_empl)) AS APELLIDO2
FROM EMPLEADOS

    The output would be:

    APELLIDO1 | APELLIDO2
MALDONADO | VELASQUEZ

    CHARINDEX() returns the position in which the character that we are specifying is found in this case, the space . It would be position 10

    LEFT() selects the characters to the left and before the space, hence the -1 in the first line 10 - 1 = 9 -> MALDONADO

    SUBSTRING() allows you to pass a string ape_empl , specify which is going to do the position from which it is going to start taking characters ' '+1 the space that is in position 10 + 1 = 11 and the last parameter is the amount of characters that LEN(ape_empl) -> VALENCIA will be taken into account .

    LEN(ape_empl) returns the length of the surname so we don't have to specify how many characters to take from the starting position LEN(ape_empl) = 19 . You can define a static numeric value but it is better to determine the word length dynamically.

    • 2