Take the 2-minute tour ×

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?


share|improve this question
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 7: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 7:18

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer
If he needs to match 1/ as well he might need some regexp match. –  barsju Apr 10 '12 at 7: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 7:35
For the completeness: in Postgres, this becomes: select * from nodes where '/' || nodes.ancestry || '/' like '%/1/%' –  ndemoreau Apr 10 '12 at 11:51

In MySQL you could write:

WHERE ancestry = '1' 
   OR LEFT(ancestry, 2) = '1/'
   OR RIGHT(ancestry, 2) = '/1'
   OR INSTR(ancestry, '/1/') > 0
share|improve this answer

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

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


... 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
share|improve this answer
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 7: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 7: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 7: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 7: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 8:08

Your Answer


