Selecting a record based on integer being in an array field

Question

I have a database of houses. Within the houses mssql database record is a field called areaID. A house could be in multiple areas so an entry could be as follows in the database:

+---------+----------------------+-----------+-------------+-------+
| HouseID | AreaID               | HouseType | Description | Title |
+---------+----------------------+-----------+-------------+-------+
| 21      | 17, 32, 53           | B         | data        | data  |
+---------+----------------------+-----------+-------------+-------+
| 23      | 23, 73               | B         | data        | data  |
+---------+----------------------+-----------+-------------+-------+
| 24      | 53, 12, 153, 72, 153 | B         | data        | data  |
+---------+----------------------+-----------+-------------+-------+
| 23      | 23, 53               | B         | data        | data  |
+---------+----------------------+-----------+-------------+-------+

If I open a page that called for houses only in area 53 how would I search for it. I know in MySQL you can use find_in_SET but I am using Microsoft SQL Server 2005.

Is normalizing the data into a many-to-many relationship with the areas in a second table an option? — Jaymz
– Jaymz, Commented Feb 6, 2011 at 18:50

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

If your formatting is EXACTLY

N1, N2 (e.g.) one comma and space between each N

Then use this WHERE clause

WHERE ', ' + AreaID + ',' LIKE '%, 53,%'

The addition of the prefix and suffix makes every number, anywhere in the list, consistently wrapped by comma-space and suffixed by comma. Otherwise, you may get false positives with 53 appearing in part of another number.

Note

A LIKE expression will be anything but fast, since it will always scan the entire table.
You should consider normalizing the data into two tables:

Tables become

House
+---------+----------------------+----------+
| HouseID | HouseType | Description | Title |
+---------+----------------------+----------+
| 21      | B         | data        | data  |
| 23      | B         | data        | data  |
| 24      | B         | data        | data  |
| 23      | B         | data        | data  |
+---------+----------------------+----------+

HouseArea
+---------+-------
| HouseID | AreaID
+---------+-------
| 21      | 17
| 21      | 32
| 21      | 53
| 23      | 23
| 23      | 73
..etc

Then you can use

select * from house h
where exists (
    select *
    from housearea a
    where h.houseid=a.houseid and a.areaid=53)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 6, 2011 at 18:41

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

neojakey Over a year ago

Many thanks, creating a second table, although laborious was exactly what I needed to do. Many thanks for the advice... much appreciated..

Tim Mahy · Accepted Answer · 2011-02-06 17:30:07Z

0

2 options, change the id's of AreaId so that you can use the & operator OR create a table that links the House and Area's....

answered Feb 6, 2011 at 17:30

Tim Mahy

1,31913 silver badges28 bronze badges

1 Comment

neojakey Over a year ago

Could you explain the use of the & operator further..?

vol7ron · Accepted Answer · 2011-02-06 17:54:31Z

What datatype is AreaID?

If it's a text field you could something like

WHERE ( 
         AreaID LIKE '53,%'     -- Covers: multi number seq w/ 53 at beginning  
      OR AreaID LIKE '% 53,%'   -- Covers: multi number seq w/ 53 in middle
      OR AreaID LIKE '% 53'     -- Covers: multi number seq w/ 53 at end
      OR AreaID = '53'          -- Covers: single number seq w/ only 53
      )

Note: I haven't used SQL-Server in some time, so I'm not sure about the operators. PostgreSQL has a regex function, which would be better at condensing that WHERE statement. Also, I'm not sure if the above example would include numbers like 253 or 531; it shouldn't but you still need to verify.

Furthermore, there are a bunch of functions that iterate through arrays, so storing it as an array vs text might be better. Finally, this might be a good example to use a stored procedure, so you can call your homebrewed function instead of cluttering your SQL.

Anthony Faull · Accepted Answer · 2011-02-06 20:10:18Z

Use a Split function to convert comma-separated values into rows.

CREATE TABLE Areas (AreaID int PRIMARY KEY);
CREATE TABLE Houses (HouseID int PRIMARY KEY, AreaIDList varchar(max));
GO

INSERT INTO Areas VALUES (84);
INSERT INTO Areas VALUES (24);
INSERT INTO Areas VALUES (66);
INSERT INTO Houses VALUES (1, '84,24,66');
INSERT INTO Houses VALUES (2, '24');
GO

CREATE FUNCTION dbo.Split (@values varchar(512)) RETURNS table
AS
RETURN
    WITH Items (Num, Start, [Stop]) AS (
      SELECT 1, 1, CHARINDEX(',', @values)
      UNION ALL
      SELECT Num + 1, [Stop] + 1, CHARINDEX(',', @values, [Stop] + 1)
      FROM Items
      WHERE [Stop] > 0
    )
    SELECT Num, SUBSTRING(@values, Start, 
        CASE WHEN [Stop] > 0 THEN [Stop] - Start ELSE LEN(@values) END) Value
    FROM Items;
GO

CREATE VIEW dbo.HouseAreas
AS
    SELECT h.HouseID, s.Num HouseAreaNum,
        CASE WHEN s.Value NOT LIKE '%[^0-9]%'
               THEN CAST(s.Value AS int)
            END AreaID
    FROM Houses h
    CROSS APPLY dbo.Split(h.AreaIDList) s
GO

SELECT DISTINCT h.HouseID, ha.AreaID
FROM Houses h
INNER JOIN HouseAreas ha ON ha.HouseID = h.HouseID
WHERE ha.AreaID = 24

Collectives™ on Stack Overflow

Selecting a record based on integer being in an array field

4 Answers 4

Note

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Note

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related