0

I have a table with a column ancestry holding a list of ancestors formatted like this "1/12/45". 1 is the root, 12 is children of 1, etc...

I need to find all the records having a specific node/number in their ancestry list. To do so, I wrote this sql statement:

select * from nodes where 1 in (nodes.ancestry)

I get following error statement: operator does not exist: integer = text

I tried this as well: 

select * from nodes where '1' in (nodes.ancestry)

but it only returns the records having 1 in their ancestry field. Not the one having for instance 1/12/45

What's wrong?

Thanks!

ndemoreau
  • 3,849
  • 4
  • 43
  • 55
  • 2
    What made you think that `in` was the correct tool for this job? Or that it would magically know how to interpret your `ancestry` column? – Damien_The_Unbeliever Apr 10 '12 at 07:15
  • If you have the possibility to redesign your tables you should. Keeping structured information in strings like that is not recommended and will only get you into all kinds of problems. – barsju Apr 10 '12 at 07:18

3 Answers3

2

This sounds like a job for LIKE, not IN.

If we assume you want to search for this value in any position, and then we might try:

 select * from nodes where '/' + nodes.ancestry + '/' like '%/1/%'

Note that exact syntax for string concatenation varies between SQL products. Note that I'm prepending and appending to the ancestry column so that we don't have to treat the first/last items in the list differently than middle items. Note also that we surround the 1 with /s, so that we don't get false matches for e.g. with /51/ or /12/.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • If he needs to match `1/` as well he might need some regexp match. – barsju Apr 10 '12 at 07:19
  • Hi Damien, your solution is what I used until know but I wanted to have something cleaner. IN seemed obvious to me but it is not that obvious apparently... – ndemoreau Apr 10 '12 at 07:35
  • For the completeness: in Postgres, this becomes: select * from nodes where '/' || nodes.ancestry || '/' like '%/1/%' – ndemoreau Apr 10 '12 at 11:51
0

In MySQL you could write:

SELECT * FROM nodes
WHERE ancestry = '1' 
   OR LEFT(ancestry, 2) = '1/'
   OR RIGHT(ancestry, 2) = '/1'
   OR INSTR(ancestry, '/1/') > 0
Marco
  • 56,740
  • 14
  • 129
  • 152
0

The in operator expects a comma separated list of values, or a query result, i.e.:

... in (1,2,3,4,5)

or:

... in (select id from SomeOtherTable)

What you need to do is to create a string from the number, so that you can look for it in the other string.

Just looking for the string '1' in the ancestry list would give false positives, as it would find it in the string '2/12/45'. You need to add the separator to the beginning and the end of both strings, so that you look for a string like '/1/' in a string like '/1/12/45/':

select * from nodes
where charindex('/' + convert(varchar(50), 1) + '/', '/' + nodes.ancestry + '/') <> 0
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • I tried this as well: select * from nodes where '1' in (replace(nodes.ancestry,'/',',')) but I get the same result – ndemoreau Apr 10 '12 at 07:31
  • @ndemoreau: You can't use the `in` operator to look for a string in another string. If you want to use the `in` operator, you would have to split the string `'1/12/45'` into three records in a temporary table, which would be a really slow solution. – Guffa Apr 10 '12 at 07:41
  • I understand that but why can't I use the IN operator to look for an integer into a comma separated list. This should work!? Shouldn't it? – ndemoreau Apr 10 '12 at 07:50
  • @ndemoreau - how would your mythical `in` operator's magical handling of a *single* string containing commas also deal with the possibility that strings can legitimately contain commas, and that (because of code generation) an `IN` list can legitimately contain a single item? It doesn't perform deep inspection of strings and say "you know what, I can see commas inside this string, I'm going to magically pull it apart and treat it as separate parameters". – Damien_The_Unbeliever Apr 10 '12 at 07:58
  • @ndemoreau: No, it shouldn't work. A string is not a comma separated list, even if the string contains characters that could be interpreted as a comma separated list. – Guffa Apr 10 '12 at 08:08
  • @damien: I know sql is not magical but I'm sure you are! Let's forget about strings and focus on integers instead: In my understanding I can use the IN clause to look for an integer into a comma-separated list of integers: select * from users where id in (1,2,3). I (and I'm sure you) do that all the time. So why my variation of this doesn't work as I do exactly the same? Sorry to insist on this but I'm a believer and I need to understand... ;-) – ndemoreau Apr 10 '12 at 08:13
  • @ndemoreau - It doesn't work for exactly the same reason that (in just about any programming language where `"` is the string delimiter), `"A,B"` is different from `"A","B"` - the first is a single string that (happens to) contain a comma, the second is two separate strings, and the comma has meaning to the language (be it as a parameter separator, or whatever). – Damien_The_Unbeliever Apr 10 '12 at 11:34