In a query on a string column with an index what will be the better approach when looking for non empty entries:

where length(string)>0 or where string>''

asked 11 May '12, 11:09

Martin's gravatar image

Martin
8.6k118151237
accept rate: 14%

edited 21 Mar '14, 11:12

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274


Even if the column is indexed, it would be somewhat astonishing to use an index to satisfy a matching predicate like "string > '' " unless the vast majority of the rows have the empty string. NULL might work better for you, as in SQL Anywhere IS NULL is a sargable predicate.

Reimer is correct, length(string) <> 0, or alternatively length(string) > 0, is not a sargable predicate. The predicate string > '' is sargable. Also, the selectivity of length(string) > 0 will be a guess, while the selectivity of string > '' can be estimated by sampling the index.

permanent link

answered 11 May '12, 14:14

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

1

Even if the predicate was sargable, would an index be rejected if a large percentage of the values were non-empty? (i.e., not a very selective predicate).

Assuming the predicate was highly selective (e.g., 1% or fewer of the values were non-empty), then could a computed column be used to create a sargable predicate?

(12 May '12, 09:30) Breck Carter
Replies hidden

So the real story is to store NULL instead of empty strings, methinks...

(12 May '12, 18:07) Volker Barth

Even if the predicate was sargable, would an index be rejected if a large percentage of the values were non-empty? (i.e., not a very selective predicate).

  • yes - the optimizer won't use the index if the selectivity is poor.

Assuming the predicate was highly selective (e.g., 1% or fewer of the values were non-empty), then could a computed column be used to create a sargable predicate?

  • Do you mean use the NULL value to replace the empty string in the computed column, Breck? Not sure that's going to make a difference.
(14 May '12, 09:13) Glenn Paulley

I guess Breck implies to use "LENGTH(string)" as computed column expression...

(14 May '12, 09:29) Volker Barth

Yes, using length(string) as the computed column expression will permit sargable predicates involving length().

(14 May '12, 09:49) Glenn Paulley

IIRC a where clause containing a function is not sargable, so your other alternative looks better to me. But probably one the Sybase experts will elaborate on that.

permanent link

answered 11 May '12, 11:39

Reimer%20Pods's gravatar image

Reimer Pods
4.2k344583
accept rate: 11%

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:

×246
×19

question asked: 11 May '12, 11:09

question was seen: 1,560 times

last updated: 21 Mar '14, 11:12