on 2011 Feb 07 4:25 PM
My problem is that I have a column with file names including paths. I want to select something from the table based on a Like 'very_long_path%'. The problem is that I receive the error "the pattern is to long"
Any advice how to get around this limitation?
Request clarification before answering.
LEFT ( x, 14 ) = 'very_long_path'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
...and yet ANOTHER limit (of mine) led me to misunderstand your comment: that perhaps you were saying LEFT has a limit of its own. So I went and tested with the entire text of Jabberwocky. Not an entire loss, that effort... got to read a great poem again 🙂
While Breck's solution is a good one, there are two subtleties. First, Breck's rewrite is not sargable (the rewrite can't use an index whereas the LIKE pattern can). You can adjust the rewrite to make it sargable but the rewrite is complex depending on the collation used. The other subtlety is that Breck's rewrite doesn't precisely match LIKE. LIKE is processed by comparing character-by-character; full-strings are compared with '='. This can give different semantics with some collations (see http://dcx.sybase.com/index.html#1201en/dbreference/colc.html comparison with 'Æ'='AE' vs LIKE ).
Other options are "Similar To" or "Regexp", they can use longer patterns and they will use the index
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.