How select an aggregated row in SQL?-CodePudding

I have an table with aggregated columns like this in a SQL Server. The goal is: Show me any row that contain Street A or B.

Name	Adresses
Mike	Street A, Street B, Street C
Smith	Street B
Jimmy	Street C

Declare @street table (col nvarchar (50) )
INSERT INTO @street Values ('Street A'), ('Street B' )

SELECT *
FROM Table 
WHERE Adresses like '%'   @street   '%'

SELECT *
FROM Table 
WHERE Adresses = ( SELECT * FROM  @street )

SELECT *
FROM Table 
WHERE STRING_SPLIT(Adresses,',') in ( SELECT * FROM  @street )

It does not work. I do not get results. Results should be like:

Name	Adresses
Mike	Street A, Street B, Street C
Smith	Street B

CodePudding user response：

You should get rid of this bad structure and store the data in a better form in future.

Anyway, following your intention to use STRING_SPLIT for that, this would do:

SELECT name, adresses  
FROM yourtable  
WHERE EXISTS 
  (SELECT *  
    FROM STRING_SPLIT(adresses, ',')  
    WHERE value IN ('Street A', 'Street B'));

You should read the documentation, that's explained there.

Testing with your sample data succeeds: db<>fiddle

CodePudding user response：

I am sure you can use some "contains" functionality on column adresses. Depends if the column is a varchar/text like column, or a array like column.

However, i would instead try to solve this in a more conseptual level.

In relational databases, this would be referred as a "many to one" relationship. One adress may have only one Persons related to them, while the Person may have listed many Adresses. Therefore i would make seperate tables. Then you can easily Search the Adresses table for A and B and find related foreign keys to Persons table.

Otherwise, you can insist on using "contains" functionality, but this might get computationally expensive.