Check if substring exists within string

I am building a string of the form:

FirstName LastName, FirstName LastName, FirstName LastName,...

and I would like to only add the current name if it isn't already in the string.

The reason I am doing this is because the table I'm working on has multiple entries for the same key but with different values for one of the columns.

The problem is I can't figure out how to check if current name already exists in the name list string that was previously built.

I appreciate your replies. Thank you.

The direct answer is,

CharIndex('Your Searching String', 'Your Main string)

If the result is NON ZERO Then the value is already exists.

Example,

Select Case When CharIndex('Three Four','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --Yes

Select Case When CharIndex('Three Five','One Two,Two Three,Three Four') <> 0 Then 'Yes' Else 'No' End --No

NOTE:

But it is really bad idea to keep the multiple entry values as columns.

Better you can have a one more table where you can make a new row for each names.

The design which you are using is not recommended.

|||

I am pulling data from several tables and in one of them for some reason ( it might be a bug in how that table is generated or in how inserts are being made) I have multiple values for the same column for the same key. Which is generating multiple rows in my query. I cannot change that design. I can only work with it.

Thanks for your answer.

|||hi you can try this options..

SELECT *
INTO #Names
FROM (
SELECT 'John' AS FirstName, 'Doe' AS LastName, 1 AS OtherColumn UNION ALL
SELECT 'John' AS FirstName, 'Doe' AS LastName, 2 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 3 AS OtherColumn UNION ALL
SELECT 'Rhamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn UNION ALL
SELECT 'Princess Quamille' AS FirstName, 'Golimlim' AS LastName, 4 AS OtherColumn
) Names

SELECT *
FROM #Names

-- option 1
DECLARE @.Names varchar(100)

SET @.Names = ''

SELECT @.Names = @.Names + FirstName + ' ' + LastName + ', '
FROM (
SELECT DISTINCT
FirstName
, LastName
FROM #Names
) Names
ORDER BY
FirstName
, LastName

SET @.Names = LEFT(@.Names,LEN(@.Names) - 1)

-- option 2
DECLARE @.Names2 varchar(100)

SET @.Names2 = ''

SELECT @.Names2 = @.Names2 + (CASE WHEN CHARINDEX(FirstName + ' ' + LastName, @.Names2) > 0 THEN '' ELSE FirstName + ' ' + LastName + ', ' END)
FROM #Names
ORDER BY
FirstName
, LastName

SET @.Names2 = LEFT(@.Names2,LEN(@.Names2) - 1)

SELECT @.Names as FromOption1, @.Names2 AS FromOption2

DROP TABLE #Names