Find the second space in a string in SQL [closed]

I already found the solution, just perform a function of your own; which creates a cycle, using twice charindex

The personal proposal is first to think about all the possibilities; Carlos Muñoz asks about what happens with "recognizing compound surnames" for example:

• De La Torre

If the user has such a surname "Pedro José De La Torre" would give you problems in your algorithm to want to extract only the surname and the first name since the extraction would be something like "DE LA". Despite this, I think it is interesting to propose a solution and so my first solution is to generate a column to the table where the number of spaces in the chain you are looking for will be printed to distinguish between compound and normal surnames of a single word, for this I recommend you first create a function such that:

CREATE FUNCTION [dbo].[CountSubStr](@str varchar(1000),@sub varchar(1000))
RETURNS int
BEGIN
    DECLARE @VECES  INT= len(@str);
    DECLARE @i      INT=1
    DECLARE @s2     varchar(100)=@str;
    DECLARE @s3     varchar(100);
    DECLARE @ESPACIOS INT=0;
    --
    WHILE(@i<@VECES+1)
        BEGIN
        set @s3=SUBSTRING(@s2,@i,1) 
        if (@s3=@sub) set @ESPACIOS=@ESPACIOS+1
        --
        set @i=@i+1
        END 
  RETURN @ESPACIOS
END
GO

WHERE: @str will be the total string of the full name string and @sub will be the substring you are looking for and will be counted in the variable @ESPACIOS .

After this process you can know exactly or filter depending on how many spaces you have for each Full Name.

Then .. When you already know all those that have 3 spaces only to them you can apply a CharIndex function with which you can know the value of the position of your second space with the following algorithm:

DECLARE @STRING VARCHAR(100)='MORENO MARIO JOSÉ';
DECLARE @POSICION INT=0;
DECLARE @SUBSTR VARCHAR(100) = @STRING;
DECLARE @POSICIONRESULT INT=0;


        SET @POSICION=CHARINDEX(' ',@SUBSTR);
        SET @SUBSTR=SUBSTRING(@SUBSTR,@POSICION,LEN(@SUBSTR));  
        SET @POSICIONRESULT=@POSICION   
        SET @POSICION=CHARINDEX(' ',LTRIM(RTRIM(@SUBSTR)));
        SET @SUBSTR=SUBSTRING(@SUBSTR,@POSICION,LEN(@SUBSTR));      
        SET @POSICIONRESULT=@POSICIONRESULT+@POSICION


PRINT @POSICIONRESULT

This script works in mysql:

SELECT locate(' ',nombre,locate(' ',nombre) + 1) as pos,nombre FROM tabla

With locate we look for the first appearance of a sub-chain in a chain from a starting point (the third parameter of the function), what we do here is to call twice locate , the first one will tell us the position of the first space, and this we enter as a parameter to the second call to start the search from the first space + 1.

if the 1 is not added, it will always return the value of the first space.