none
Match first and last name RRS feed

  • Question

  • We have a sp that we get text search matches on last name and want to be able to filter on both first and last name when user enters a comma (e.g. Johnson, Steve) to get a smaller list. Below is our sp but it is not returning any matches when we enter 'Johnson, Steve' into @SearchText and there should be 4 matches on our database.  Below the sp is part of the table structure of the Customers table.

    ALTER PROCEDURE [dbo].[ms_selCustomerPastJobs] 
    	@SearchText		varchar(1000) = null
    	
    AS
    	BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @SearchLast varchar(50);
    	DECLARE @SearchFirst varchar(50);
    	
    	IF @SearchText IS NOT NULL
    		BEGIN
    			IF CHARINDEX(',', @SearchText) = 0
    				BEGIN
    					SET @SearchText = '%' + @SearchText + '%';
    				END
    			ELSE
    				BEGIN
    					SET @SearchLast = '%' + LEFT(@SearchText,CHARINDEX(',', @SearchText)-1) + '%';
    					SET @SearchFirst = '%' + RIGHT(@SearchText,LEN(@SearchText) - CHARINDEX(',', @SearchText)+1) + '%';
    				END
    		END
    
    	SELECT DISTINCT TOP (100) PERCENT 
    		R.VehicleID,
    		C.CustomerID, 
    		CASE COALESCE (C.LastName, '') 
                 WHEN '' THEN C.CompanyName 
                 ELSE C.LastName + COALESCE (', ' + C.FirstName, '') 
                 END AS Customer,
    		CASE COALESCE (C.LastName, '') 
                 WHEN '' THEN C.CompanyName 
                 ELSE C.LastName + COALESCE (', ' + C.FirstName, '') 
                 END + N'/' + ISNULL(C.Address1,'') AS CustomerAndAddress,
            C.Address1,
            C.City,
            C.[State],
            C.Zip,
    		C.Phone1,
    		C.Email,
            V.VINNumber, 
            V.VehicleYear, 
            V.VehicleMake, 
            V.VehicleModel
    	FROM dbo.Customers C INNER JOIN
             dbo.RepairOrder R ON C.CustomerID = R.CustomerID LEFT OUTER JOIN
             dbo.Vehicles V ON R.VehicleID = V.VehicleID
       WHERE (LEN(CASE COALESCE (C.LastName, '') 
    					WHEN '' THEN C.CompanyName 
                        ELSE C.LastName 
                        END) > 0)
         AND (CASE WHEN @SearchText IS NULL THEN 'F'
    			   WHEN @SearchLast IS NOT NULL AND C.LastName LIKE @SearchLast AND C.FirstName LIKE @SearchFirst THEN 'T'
    			   WHEN @SearchLast IS NULL AND @SearchText IS NOT NULL AND C.LastName LIKE @SearchText THEN 'T'
    			   WHEN @SearchText IS NOT NULL AND C.CompanyName LIKE @SearchText THEN 'T'
    			   ELSE 'F'
    			   END = 'T')
    	ORDER BY Customer;
    	
    	END
    
    
    
    
    

    CREATE TABLE [dbo].[Customers](
    	[CustomerID] [int] NOT NULL,
    	[LastName] [varchar](40) NULL,
    	[FirstName] [varchar](20) NULL,
    	[CompanyName] [varchar](50) NULL,
    	[Address1] [varchar](40) NULL,
    	[Address2] [varchar](40) NULL,
    	[City] [varchar](20) NULL,
    	[State] [varchar](5) NULL,
    	[Zip] [varchar](11) NULL,
    	[Phone1] [varchar](30) NULL,
    	[Phone2] [varchar](30) NULL,
    	[Fax] [varchar](20) NULL,
    	[Email] [varchar](128) NULL,
    
     CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
    (
    	[CustomerID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Thursday, November 7, 2019 8:05 PM

All replies

  • DECLARE @SearchText varchar(1000) = 'Johnson, Steve';
    DECLARE @SearchLast varchar(50);
    DECLARE @SearchFirst varchar(50);
    
    SET @SearchText = REPLACE(REPLACE(@SearchText, ',', SPACE(1)), SPACE(2), SPACE(1));
    SET @SearchFirst = LEFT(@SearchText, CHARINDEX(SPACE(1), @SearchText));
    SET @SearchLast = SUBSTRING(@SearchText, CHARINDEX(SPACE(1), @SearchText), LEN(@SearchText) + 1 - CHARINDEX(SPACE(1), @SearchText));
    
    SELECT @SearchText, @SearchFirst, @SearchLast;
    
    -- Build SELECT statement


    A Fan of SSIS, SSRS and SSAS

    Thursday, November 7, 2019 8:25 PM
  • Nope. Still not getting any records.
    Thursday, November 7, 2019 8:42 PM
  • Try this WHERE statement. Since there is no "%" when you use LIKE, it is equal to =.

    WHERE COALESCE (C.LastName, C.CompanyName, '') <> '' AND (C.LastName = @SearchLast AND C.FirstName = @SearchFirst OR C.CompanyName = @SearchText)


    A Fan of SSIS, SSRS and SSAS

    Thursday, November 7, 2019 8:59 PM
  • I solved it with below. Want to use the LIKE so it returns both Steve and Steven, etc.

    	IF @SearchText IS NOT NULL
    		BEGIN
    			IF CHARINDEX(',', @SearchText) = 0
    				BEGIN
    					SET @SearchText = '%' + @SearchText + '%';
    				END
    			ELSE
    				BEGIN
    					DECLARE @CommaLoc int = CHARINDEX(',', @SearchText);
    					SET @SearchLast = '%' + LTRIM(LEFT(@SearchText, @CommaLoc - 1)) + '%';
    					SET @SearchFirst = '%' + LTRIM(SUBSTRING(@SearchText, @CommaLoc + 1, LEN(@SearchText) + 1 - @CommaLoc)) + '%';
    				END
    		END

    Thursday, November 7, 2019 9:23 PM
  • Hi David Chase89,

     

    Thanks for your reply.

     

    We are glad to hear that you have solved your problem. Please kindly mark the helpful replies as answers or mark your self. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    If you have any question , please create a new posting to ask for help.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 8, 2019 3:11 AM
  • Hi , 

     Please check LIKE (Transact-SQL)

    If you would like to use LIKE , please refer to following script.

     AND C.LastName LIKE '%'+@SearchLast AND C.FirstName LIKE '%'+@SearchFirst+ '%'

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 8, 2019 7:49 AM