CODEWITHSUNDEEP
sqlsql-server
Gloria Santin
Gloria Santin

Reputation: 2136

sql server using SUBSTRING with LIKE operator returns no results

I created this CTE that returns first and last names from 2 different tables. I would like to use the CTE to identify all of the records that have the same last names and the first name of one column starts with the same first letter of another column. This is an example of the results of the CTE. I want the SELECT using the CTE to return only the highlighted results:

Query Results 

;WITH CTE AS
            (
            SELECT AD.FirstName AS AD_FirstName, AD.LastName AS AD_LastName, NotInAD.FirstName As NotInAD_FirstName, NotInAD.LastName As NotInAD_LastName 
            FROM PagingToolActiveDirectoryUsers AD JOIN 
                (
                SELECT FirstName, LastName
                FROM #PagingUsersParseName
                EXCEPT
                SELECT D.FirstName, D.LastName
                FROM PagingToolActiveDirectoryUsers D 
                WHERE D.FirstName <> D.LastName AND D.LastName <> D.LoginName
                    AND D.LoginName LIKE '%[0-9]%[0-9]%'        
                ) AS NotInAD ON NotInAD.LastName = AD.LastName
            )
            SELECT *
            FROM CTE
            WHERE (AD_LastName = NotInAD_LastName) AND (AD_FirstName LIKE ('''' + SUBSTRING(NotInAD_FirstName, 1, 1) + '%''')) 
            ORDER BY AD_LastName, AD_FirstName;

The result of this query returns no rows. What am I doing wrong?

Thanks.

Upvotes: 0

Views: 5125

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You're enclosing the string to be searched for with single-quotes, but it doesn't appear that the data in AD_FirstName has those single-quotes embedded in it. I suggest you replace the first line of the WHERE clause with

WHERE (AD_LastName = NotInAD_LastName) AND (AD_FirstName LIKE (SUBSTRING(NotInAD_FirstName, 1, 1) + '%'))

Best of luck.

Upvotes: 3

Related Questions