Delete character from string

As much as I appreciate the community's help in improving posts, do NOT change the point/crux of the paragraphs. Information lost is opportunity missed.

• You should do a little more discovery on the structure and consistency of your data.

Ask questions such as 'Do you have Surrogate keys that identify the names uniquely?' or 'Does your first table containing ending spaces or Leading spaces?' If there is consistency in the naming standards, your solution can be elegant and simple. Otherwise, you may need to cleanse your data first before comparing tables.

• Character string comparisons are slow and usually require a perfect match (unless the LIKE operator is used with a wildcard character. Col_1 LIKE 'Name%'). Furthermore, if possible, avoid using functions on both sides of your predicates (ON, WHERE, HAVING) as SQL will likely not be able to properly use Indexes on your columns and perform costly table/index scans.

Solution A using unique IDs on the table:

SELECT CustomerID, [First Name] AS First_Name FROM TableA INNER JOIN TableB ON CustomerID = BuyerID WHERE TableA.First_Name = SUBSTRING(TableB.firstname, 1, LEN(TableA.First_Name) )

Easy, no? This uses a SARG in the WHERE clause and only compares the length of code that matters.

• Q. But I am not sure my data does not have duplicates or uses Surrogate keys

A. Then run the DISTINCT clause on your tables. DISTINCT is basically a GROUP BY, and eliminates M:M comparisons. Also, your code remains lean and can still utilize indexes properly and clearly.

WITH C AS (SELECT FirstName FROM TableA)
SELECT B.FirstName
FROM   C
RIGHT OUTER JOIN (SELECT DISTINCT First_Name FROM TableB) B ON C.FirstName = SUBSTRING(B.First_Name, 1, LEN(C.FirstName) ) 
WHERE TableA.[Tie_Breaker] = TableB.[Tie_Breaker]

Note the RIGHT OUTER JOIN gives you NULLS for the rows that do not have a match in the right table (TABLEB). I left it here since you might like to compare what matches and mismatches you have between the tables (for quality assurane). Plus, you take advantage of simple functions like ISNULL or COALESCE and take pride that you have the leanest code in your department. :)

Special Thanks to @Matt for pointing out the need for tie breakers via the WHERE clause

Sources: MSDN, (n.d.) Predicates (Transact_SQL). Retrieved from MSDN

Declare @Name Varchar(20) = 'David L'

SELECT LEFT(@Name , ISNULL(NULLIF(CHARINDEX(' ' , @name), 0), LEN(@Name)))

The above select will only return anything to the left of the white space if there is a white space in the string, you can use the same logic to compare the Names stored in two different column of the different tables

tablea.firstname = LEFT(tableb.firstname
                         , ISNULL(NULLIF(CHARINDEX(' ' , tableb.firstname), 0)
                       , LEN(tableb.firstname)))

One option would be to join on a substring of the name column which includes only the first name:

SELECT t1.firstname
FROM tableA t1 INNER JOIN tableB t2
    ON t1.firstname = 
       SUBSTRING(t2.firstname,
                 1,
                 CASE WHEN CHARINDEX(' ', t2.firstname) > 0
                      THEN CHARINDEX(' ', t2.firstname) - 1
                      ELSE LEN(t2.firstname)
                 END)

The join condition compares the firstname from tableA against the first word from tableB, assuming that this first word is the first name.

You can compare parts of the fields before the space. Easy way is like this:

select t1.firstname,t2.firstname t2_name
from table1 t1
inner join table2 t2 on 
--add space after the value to ensure the space exists
left(t1.firstname+' ',charindex(' ',t1.firstname,1)-1) 
= left(t2.firstname+' ',charindex(' ',t2.firstname,1)-1)

You can use charindex to check whether your TableB.FirstName(John L) Contains TableA.FirstName(John),Hope this helps.

Where CHARINDEX('tablea.firstname','tableb.firstname')=1

Okay this topic seems to started to go way down the rabbit hole because there is never an easy answer to matching based upon first names in any larger sized datasets. That's why there is an entire industry built on it. There are a few data cleansing products out there to assist with a task like that, which I have used but don't represent. The Microsoft tool available in developer edition and some others editions is Data Quality Services https://msdn.microsoft.com/en-us/library/ff877925.aspx This will utilize several string matching algorithms and multiple fields to determines duplicates within a dataset(s) it is somewhat cumbersome. SSIS offers a fuzzy matching task which also utilizes matching algorithms... You can build your own solutions etc. The general consensus of all of these techniques is to match on data in addition to name. Such as email, address, birth date.

My recommendation in your particular case is to determine the cleanliness of your dataset and try a few of the string manipulation techniques in these answers and see what gets you closest to your goal. It is very likely that a combination or several passes of more than 1 of the techniques will be the answer, if other information is also able to be related to people in both databases I would recommend including additional fields for your matching purposes.

My suggestion(s) as far as fast string manipulations to try.

As long as it is always First name space initial you can strip out the initial and join the value in the on statement. Also reversing the string will let you find the position of the last space in the case the person has 2 first names and an initial.

DECLARE @FirstNameWithInitial VARCHAR(100) = 'bobby lee w'
DECLARE @FirstName VARCHAR(100) = 'bobby lee'

SELECT 
    --unknown number of characters after last space
    LEFT(@FirstNameWithInitial,LEN(@FirstNameWithInitial) - CHARINDEX(' ',REVERSE(@FirstNameWithInitial)))

    --always 2 characters
    ,LEFT(@FirstNameWithInitial, LEN(@FirstNameWithInitial) - 2)

    ,IIF(LEFT(@FirstNameWithInitial,LEN(@FirstNameWithInitial) - CHARINDEX(' ',REVERSE(@FirstNameWithInitial))) = @FirstName,'Join','No Match')

SELECT *
FROM
    TableWithouInitial t
    INNER JOIN TableWithInitial ti
    ON t.Firstname = LEFT(ti.FirstNameWithInitial,LEN(ti.FirstNameWithInitial) - CHARINDEX(' ',REVERSE(ti.FirstNameWithInitial)))

Adding the way to get everything up to the first space so that I am not over engineering to get to the last if more than 1 exists.

SELECT
    LEFT(@FirstNameWithInitial,LEN(@FirstNameWithInitial) - CHARINDEX(' ',@FirstNameWithInitial))

Yep just remove the REVERSE() function from the solution above. It is a personal preference not to use the SUBSTRING or NULLIF or ISNULL. note CHARINDEX() will return 0 if no match is found so the LEN - 0 will be the entire length of the string and will not create any errors if you do not include the NULLIF or ISNULL.