CODEWITHSUNDEEP
sqlsql-servert-sql
rumi
rumi

Reputation: 3298

SQL Server query to filter column containing a value

I have a SQL Server table with a column with multiple emails, e.g. Emails with values separated by a comma like:

[email protected],[email protected],[email protected]

From another table with EmailAddress column, I need to filter records containing the Emails e.g.

select EmailAddress 
from [dbo].[SomeTable1] 
where EmailAddress contains (select Emails from [dbo].[SomeTable2])

Upvotes: 0

Views: 130

Answers (4)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Try this:

 SELECT DISTINCT EmailAddress 
 FROM [dbo].[SomeTable1] A
 INNER JOIN [dbo].[SomeTable2] B ON CHARINDEX(',' + Emails, + ',',',' + EmailAddress + ',')>0

Use CHARINDEX to check if the Emails exists in EmailAddress or not and JOIN the tables only when it exists.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82020

Select *
 From  [dbo].[SomeTable1] A
 Join  [dbo].[SomeTable2] B
  on   charindex(','+EmailAddress ,','+Emails )>0

Upvotes: 0

User
User

Reputation: 804

First create a user defined function as below to split the comma separated values

    create FUNCTION [dbo].[Split] (
          @InputString                  VARCHAR(8000),
          @Delimiter                    VARCHAR(50)
    )

    RETURNS @Items TABLE (
          Item                          VARCHAR(8000)
    )

    AS
    BEGIN
          IF @Delimiter = ' '
          BEGIN
                SET @Delimiter = ','
                SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
          END

          IF (@Delimiter IS NULL OR @Delimiter = '')
                SET @Delimiter = ','

          DECLARE @Item           VARCHAR(8000)
          DECLARE @ItemList       VARCHAR(8000)
          DECLARE @DelimIndex     INT

          SET @ItemList = @InputString
          SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
          WHILE (@DelimIndex != 0)
          BEGIN
                SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
                INSERT INTO @Items VALUES (@Item)

                -- Set @ItemList = @ItemList minus one less item
                SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
                SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
          END -- End WHILE

          IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
          BEGIN
                SET @Item = @ItemList
                INSERT INTO @Items VALUES (@Item)
          END

          -- No delimiters were encountered in @InputString, so just return @InputString
          ELSE INSERT INTO @Items VALUES (@InputString)

          RETURN

    END -- End Function

Final Query

 select EmailAddress from [dbo].[SomeTable1] where EmailAddress in
    (select item from [dbo].[SomeTable2] cross apply dbo.Split(Emails,','))

Upvotes: 0

DVT
DVT

Reputation: 3127

select EmailAddress 
from [dbo].[SomeTable1] tab1 
where exists 
    (select 1 
    from [dbo].[SomeTable2] tab2 
    where '%,'+tab1.EmailAddress+',%' LIKE ','+tab2.Emails+',');

Upvotes: 0

Related Questions