Finding data from specific value in an array in SQL

Question

Morning All,

I have an Allowed systems field in my record that can contain an array such as: 22, 18, 21

What I want to do is if system 18 is accessing the database and is pulling all the records that are allowed for system 18, how can I write that in sql.

+----------+----------------+------------+
| AdvertID | AllowedSystems | AdvertName |
+----------+----------------+------------+
| 47       | 22, 18, 21, 3  | GeorgesAd  |
+----------+----------------+------------+
| 49       | 2, 7, 9, 6     | StevesAd   |
+----------+----------------+------------+
| 47       | 18, 12, 32, 8  | PetesAd    |
+----------+----------------+------------+

Any assistance would be greatly appreciated.

Have a great day..!

Paul Jacobs

D'Arcy Rittich · Accepted Answer · 2010-11-04 14:01:35Z

2

This will test for the best performing cases first, in the hopes of getting some performance out of what is destined to be an inefficient query due to the denormalized structure.

The first two WHERE clauses can take advantage of an index on AllowedSystems, although this may be helpful in only a small number of cases, depending on what your data is like.

select AdvertID, AdvertName 
from MyTable
where AllowedSystems = '18'
    or AllowedSystems like '18,%'
    or AllowedSystems like '%, 18'
    or AllowedSystems like '%, 18,%'

answered Nov 4, 2010 at 14:01

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

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

Comments

Joe Stefanelli · Accepted Answer · 2010-11-04 14:07:10Z

1

Note: I add a space to the beginning of the searched column and a comma to the end to account for the cases where 18 is the first or last entry in the list. I then search for ' 18,' within the string.

select *
    from YourTable
    where charindex(' 18,', ' ' + AllowedSystems + ',') <> 0

edited Nov 4, 2010 at 14:07

answered Nov 4, 2010 at 14:01

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Comments

JNK · Accepted Answer · 2010-11-04 14:01:51Z

1

First, this is a bad idea for a data structure. You should probably normalize by adding an AllowedSystems table with a record for each pair of system and Advert.

Second, you would want to use this:

SELECT AdvertID, AdvertName
FROM MyTable
WHERE AllowedSystems LIKE '%18%'

You're going to have issues when you get over 100 systems, since there is no way to tell if the hit is for 18 or 118 or 181.

EDIT:

Or you can use RedFilter's multiple WHERE conditions to avoid the above issue with 18, 118, or 181.

answered Nov 4, 2010 at 14:01

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Comments

AdaTheDev · Accepted Answer · 2010-11-04 14:05:53Z

1

The poor performance way would be:

SELECT AdvertID, AdvertName
FROM YourTable
WHERE ', ' + AllowSystems + ', ' LIKE '%, 18, %'

However, ideally you should have an extra table that has a compound key:

Table: AdvertAllowedSystem
AdvertID
SystemID

so that you can do a more performant, SARGable query:

SELECT a.AdvertID, a.AdvertName
FROM AdvertAllowedSystem s
    INNER JOIN Advert a ON s.AdvertId = a.AdvertId
WHERE s.SystemId = 18

answered Nov 4, 2010 at 14:05

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Comments

Damian Leszczyński - Vash · Accepted Answer · 2010-11-04 14:30:13Z

0

In addition if you have possibility to changed the design of create a materialized view, for this you may use hierarchyid Data Type or Rank and Unrank

answered Nov 4, 2010 at 14:30

Damian Leszczyński - Vash

31k9 gold badges64 silver badges95 bronze badges

Collectives™ on Stack Overflow

Finding data from specific value in an array in SQL

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related