CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
neojakey
neojakey

Reputation: 1663

Selecting a record based on integer being in an array field

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.

Upvotes: 0

Views: 101

Answers (4)

Anthony Faull
Anthony Faull

Reputation: 17957

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

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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

  1. A LIKE expression will be anything but fast, since it will always scan the entire table.
  2. 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)

Upvotes: 5

vol7ron
vol7ron

Reputation: 42099

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.

Upvotes: 0

Tim Mahy
Tim Mahy

Reputation: 1319

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....

Upvotes: 0

Related Questions