CREATE TABLE #TempAbbrev
(
Abbrev VARCHAR(50),
Name VARCHAR(50)
)

INSERT INTO #TempAbbrev (Abbrev, Name)
VALUES
('ma', 'Maria'),
('ma', 'Ma.'),
('ma.', 'Ma'),
('ma.', 'Maria'),
('ma.', 'ma.'),
('Maria', 'Ma.'),
('Maria', 'Ma'),
('sta', 'Santa'),
('sta', 'Sta.'),
('sta.', 'Sta'),
('sta.', 'Santa')

DECLARE @ParamInput nvarchar(255);
SET @ParamInput = 'ma. teresa';

DECLARE @normalize VARCHAR(200) = REPLACE(@ParamInput, ' ', '|')
DECLARE @individual VARCHAR(20) = NULL

CREATE TABLE #TEMPNORMALIZED
(
Name VARCHAR(250)
)

WHILE LEN(@normalize) > 0
BEGIN
    IF PATINDEX('%|%', @normalize) > 0
    BEGIN
        SET @individual = SUBSTRING(@normalize,
                                    0,
                                    PATINDEX('%|%', @normalize))
        SET @normalize = SUBSTRING(@normalize,
                                  LEN(@individual + '|') + 1,
                                  LEN(@normalize))
    END
    ELSE
    BEGIN
        SET @individual = @normalize
        SET @normalize = NULL
    END
    INSERT INTO #TEMPNORMALIZED (Name) VALUES (@individual)
END

--SELECT * FROM #TEMPNORMALIZED

--SELECT * FROM #TempAbbrev WHERE Name IN (SELECT Name FROM #TEMPNORMALIZED) -- Substitution

--declare @abbrev varchar(50) =  (SELECT top 1 Name FROM #TempAbbrev WHERE Name in (SELECT Name FROM #TEMPNORMALIZED))

        CREATE TABLE #TB1
        (
        Split VARCHAR(250),
        NAME2 VARCHAR(250)
        )

DECLARE @count2 INT;
DECLARE @results VARCHAR(255);
DECLARE db_cursor CURSOR FOR SELECT Name FROM #TEMPNORMALIZED
DECLARE @myName VARCHAR(256);
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @myName;
WHILE @@FETCH_STATUS = 0
BEGIN

       --Do stuff with scalar values

        DECLARE db_cursor2 CURSOR FOR SELECT Name FROM #TempAbbrev WHERE Abbrev = @myName;
        DECLARE @myName2 VARCHAR(256);
        OPEN db_cursor2;
        FETCH NEXT FROM db_cursor2 INTO @myName2;
        WHILE @@FETCH_STATUS = 0
        BEGIN
               --Do stuff with scalar values
               INSERT INTO #TB1 (Split, NAME2) VALUES (@myName, @myName2)

               FETCH NEXT FROM db_cursor2 INTO @myName2;
        END;
        CLOSE db_cursor2;
        DEALLOCATE db_cursor2;

        SET @count2 = (SELECT COUNT(Split) FROM #TB1 WHERE Split = @myName)

        SELECT * FROM #TB1


       FETCH NEXT FROM db_cursor INTO @myName
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;

DROP TABLE #TEMPNORMALIZED

DROP TABLE #TempAbbrev

DROP TABLE #TB1