regarding like query operator

Question

Hi

For the below data (well..there are many more nodes in the team foundation server table which i need to refer to..below is just a sample)

Nodes
------------------------
\node1\node2\node3\
\node1\node2\node5\
\node1\node2\node3\node4\
\node1\node2\node3\node4\node5\

I was wondering if i can apply something like (below query does not give the required results)

select * from table_a where nodes like '\node1\node2\%\'

to get the below data

\node1\node2\node3\
\node1\node2\node5\

and something like (below does not give the required results)

select * from table_a where nodes like '\node1\node2\%\%\'

to get

\node1\node2\node3\
\node1\node2\node5\
\node1\node2\node3\node4\

Can the above be done with like operator? Pls. suggest.

Thanks

Answer 1

It seems like this would work (basically just eliminating the last backslash):

select * from table_a where nodes like '\node1\node2\%\%'

EDIT

You could also try this:

select * from table_a where 
      nodes like '\node1\node2\%\' or
      nodes like '\node1\node2\%\%\'

Answer 2

maybe use the delimiter to get the resutls. it is unclear what you are actually trying to get, but you could use the

substr

function to either count or find the position of the delimiter '/' character.

Answer 3

A little late to the party, but it appears that the problem is still open. Could it be that the backslashes are escaping the wildcard meaning of the percent signs? And the backslash n could be getting interpreted as well.

Answer 4

You'll need to combine two terms, LIKE and NOT LIKE:

select * from table_a where 
   nodes like '\node1\node2\%\' AND
   nodes NOT like '\node1\node2\%\%\'

for the first query, and a similar solution for the second. That's with "plain SQL". There are probably SQL Server specific functions which will count the number of "\" characters in the column, for instance.

Answer 5

Doesn't sql-server know a wildcard for a single character?

 select * from table_a 
   where nodes LIKE '#node1#node2#node_#';
        nodes        
---------------------
 #node1#node2#node5#
 #node1#node2#node3#

I testet this on postgresql, where it is hard to insert a backslash, which is the reason why I replaced them with #.

Here is another possibility - negate more than one backslash (# used for my convenience):

SELECT * FROM table_a 
WHERE (nodes LIKE '#node1#node2#%#' 
  AND NOT nodes LIKE '#node1#node2#%#%#');

On postgresql there is too the possibility to match against patterns, with SIMILAR TO, or ~:

SELECT * FROM table_a 
WHERE nodes SIMILAR TO '#node1#node2#[^#]*#';
        nodes        
---------------------
 #node1#node2#node5#
 #node1#node2#node3#

[] encapsulates a group of alternatively allowed characters, for example [aeiou] would be a lowercase vocal. But when the caret is the first sign in the brackets, the sign(s) are negated so [^aeiou] would mean anything but a lowercase vocal, and [^#] means anything but a #. The asterix behind that expression means that the preceding sign can occur as often as you like, 0 to million times. (+ would mean at least one times, ? would mean 0 or 1 times).

So '#node1#node2#[^#]*#' means '#node1#node2#', followed by anything but a hash, 0 or single or multiple times, and then, finally a hash.