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.

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.6k114149237
accept rate: 14%

edited 21 Mar '14, 11:12

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


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.7k568104
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.1k324278
accept rate: 12%

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:

×238
×18

question asked: 11 May '12, 11:09

question was seen: 1,406 times

last updated: 21 Mar '14, 11:12