1

See the below example 

where '1|2|||1|' like '%%|%%|%%|%%|[^3]|' /* it will return true if the string between last two pipe symbols is not like '3'*/

The above code just works fine.

In the same way I need to build a expression for the string between last two pipes not like 10 or 11. Or at least not like 10. So tried like below.

where '1|2|||8|' like '%%|%%|%%|%%|[^10]|'

The above statement return false for 1,0 and 10 instead of just 10. so the above query considers 1 as a separate string and 0 as a separate string. I'm expecting a expression which will return false only if the string between last two pipe is 10. not for 1 or 0.

I also tried using curly braces. But it behaving differently.

  where '1|2|||8|' like '%%|%%|%%|%%|[{^10}]|

Also if you can derive a expression for - string between last two pipe should not be 10 or 11.

Note: I cannot use 'OR' or 'NOT LIKE' in the query.

Any ideas?

Raphael Schweikert
  • 18,244
  • 6
  • 55
  • 75
Maximus
  • 792
  • 9
  • 19

1 Answers1

2

You need to check each characters separately

Like "...Not 1 followed by not (0 or 1)"

where '1|2|||8|' like '%%|%%|%%|%%|[^1][^01]|'
gbn
  • 422,506
  • 82
  • 585
  • 676
  • thanks for the answer. But I need an expression which should return true for 1 and 0 and false for 10. – Maximus May 15 '13 at 12:28
  • @Sivakumar: LIKE won't allow this, it isn't that powerful. You said you can't have NOT or OR, so I would suggest a CLR function to allow regex – gbn May 15 '13 at 12:31
  • Yes, I'm also using regex, as you can see in my question. It's not working only for more than one digit numbers. Thing is need to know the way for grouping characters as a single string in the regex. – Maximus May 15 '13 at 12:33
  • There should be a way to do this. But couldn't find it. I surfed a lot. – Maximus May 15 '13 at 12:35
  • 1
    @Sivakumar: no, there isn't using LIKE if you can't use NOT/OR. LIKE is not regex. – gbn May 15 '13 at 12:44
  • Thanks, and I have created my own scalar function which compares the input strings and return true or false. And it is working like a charm. :) – Maximus May 24 '13 at 11:39