The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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?

asked 07 Feb '11, 16:25

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%


LEFT ( x, 14 ) = 'very_long_path'

permanent link

answered 07 Feb '11, 17:27

Breck%20Carter's gravatar image

Breck Carter
26.7k418577825
accept rate: 20%

That answer took me some time to understand...but that seems to be due to another limit:)

(08 Feb '11, 09:16) Volker Barth
1

...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 :)

(08 Feb '11, 11:22) Breck Carter

Guess the limit I mentioned is more besides the LEFT and RIGHT sides of my brain. Reading both SQLA and poetry may help to extend here. Sometimes it's a very_long_path to understand:)

(08 Feb '11, 11:49) Volker Barth
3

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

(08 Feb '11, 15:28) Ivan T. Bowman

Other options are "Similar To" or "Regexp", they can use longer patterns and they will use the index

permanent link

answered 08 Feb '11, 15:56

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5
×5

question asked: 07 Feb '11, 16:25

question was seen: 754 times

last updated: 08 Feb '11, 15:56