T-SQL Compare two Strings and remove duplicates between them

naisnet
naisnet used Ask the Experts™
on
I have a web search function that takes a first and last name and searches it against a full text catalog of names.  The issue I'm having is that I need to compare both strings and remove duplicate words between them without permanently concatenating them.  It doesn't matter which string is the reference and which is the target.

INPUT:

   FIRSTNAME: Jean ValJean
   LASTNAME: Smith Jean



DESIRED RESULT:

   FIRSTNAME: Jean ValJean
   LASTNAME: Smith

   -or-

   FIRSTNAME: ValJean
   LASTNAME: Smith Jean



Thanks!

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,
  Please try this logic.
SELECT 
LEFT(FIRSTNAME,CHARINDEX(' ',FIRSTNAME,0)) FIRSTNAME,
SUBSTRING(FIRSTNAME,CHARINDEX(' ',FIRSTNAME,0)+1 ,LEN(FIRSTNAME)+1) AS LASTNAME
FROM
(SELECT 'FIRST LAST' AS FIRSTNAME , 'FIRST LAST' AS LASTNAME )Q

Open in new window


declare @FIRSTNAME nvarchar(100), @LASTNAME nvarchar(100), @NEWFIRSTNAME nvarchar(100), @NEWLASTNAME nvarchar(100),
declare
declare @F1 nvarchar(50),@F2 nvarchar(50),@L1 nvarchar(50),@L2 nvarchar(50)

set @FIRSTNAME= 'Jean ValJean'
SET @LASTNAME= 'Smith Jean'

SET @F1=SUBSTRING(@FIRSTNAME, 1, CHARINDEX(' ', @FIRSTNAME) - 1)
SET @F2=SUBSTRING(@FIRSTNAME, CHARINDEX(' ', @FIRSTNAME) + 1, LEN(@FIRSTNAME))

SET @L1=SUBSTRING(@LASTNAME, 1, CHARINDEX(' ', @LASTNAME) - 1)
SET @L2=SUBSTRING(@LASTNAME, CHARINDEX(' ', @LASTNAME) + 1, LEN(@LASTNAME))

PRINT @F1
PRINT @F2
PRINT @L1
PRINT @L2

if (@F1=@L2)
BEGIN
SET @NEWLASTNAME=@L1
--@NEWFIRSTNAME=@F2
END

-- You can add multiple IF conditions based on your business requirement.

THANKS,
Some Typos in earlier post

declare @FIRSTNAME nvarchar(100), @LASTNAME nvarchar(100), @NEWFIRSTNAME nvarchar(100), @NEWLASTNAME nvarchar(100)
declare @F1 nvarchar(50),@F2 nvarchar(50),@L1 nvarchar(50),@L2 nvarchar(50)

set @FIRSTNAME= 'Jean ValJean'
SET @LASTNAME= 'Smith Jean'

SET @F1=SUBSTRING(@FIRSTNAME, 1, CHARINDEX(' ', @FIRSTNAME) - 1)
SET @F2=SUBSTRING(@FIRSTNAME, CHARINDEX(' ', @FIRSTNAME) + 1, LEN(@FIRSTNAME))

SET @L1=SUBSTRING(@LASTNAME, 1, CHARINDEX(' ', @LASTNAME) - 1)
SET @L2=SUBSTRING(@LASTNAME, CHARINDEX(' ', @LASTNAME) + 1, LEN(@LASTNAME))

if (@F1=@L2)
BEGIN
SET @NEWLASTNAME=@L1
--@NEWFIRSTNAME=@F2
END
PRINT @NEWLASTNAME

Author

Commented:
I actually ended up taking care of it by listing both strings into a table variable and then doing a select distinct.  Thank you both - I'm assigning the points half and half.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial