Firebird SQL return rows only if a sub-string exists within a specific column

[Yaron 53]

I prepared to post a question on how I can do this, but I actually reached a solution on my own while contemplating on how to best phrase the question and code.

 

Since I searched and couldn't find good documentation on how to do this, I decided to post my solution here:

 

Imagine a table with columns: NAME , TAGS , etc ...

The TAGS column contains a string with multiple unique identifiers, for example "tag0001|tag0002|tag0003|tag0004".

 

I'm only getting rows with a TAGS column that contains a sub-string I'm looking for (a specific tag) :

   sTAGUID := 'tag0002';
   dbQuery.SQL.Text := 'SELECT * FROM CARDS_TABLE WHERE (POSITION(:taguid,TAGS) > 0) ORDER BY LOWER(NAME) ASC;';
   Try
     dbQuery.Prepare;
     dbQuery.ParamByName('taguid').AsString := sTAGUID;
     dbQuery.Open;
     If dbQuery.RecordCount > 0 then While dbQuery.Eof = False do
     Begin
       New(nEntry);
       ExtractCardQuery(dbQuery,nEntry^);
       cardList.Add(nEntry);
       dbQuery.Next;
     End;
   finally
     dbQuery.free
   end;

 

[Markus Kinzler 174]

SELECT 
  * 
FROM 
  CARDS_TABLE 
WHERE 
  TAGS containing :taguid 
ORDER BY 
LOWER(NAME) ASC;

[Guest]

As long as you are absolutely sure that your separator will not occur in any "value". No problems.

I have some cases where i push over 1000 integer values as a long string to a stored proc instead of trying to parameter:ize it.

The sp parses the input and can load a GTT for an efficient join. Works the other way too, i.e receive a "whatever-separated" string up to the client. I i know there will be a limited number of "sub-values" list. 

So yes, this is not bad design IMHO.