Home > Database > Fun with substring (as replacement for IN clause)

Fun with substring (as replacement for IN clause)

Different approach I was shown by a colleague to replace an IN clause in SQL in some cases.

Suppose you have  a limited set of small values (I wouldn’t go beyond 255 for this) and want to do have an IN clause. You can substitute it for a well crafted substring.

Create a string (in your language of choice) as long as required (as long as the max value in your field). Make it all zeroes (or character of choice). At indexes of the values desired, change for 1’s. Then, what you’re going to want to do is use a substring function (not two are identical across the many implementations of SQL, it seems), and make the search target your prepared string, the start index will be your database value, and length 1. All this equal to ‘1’ for IN or different for NOT IN.

For example, suppose:

WHERE SmallValue IN (3,4,8)

would then become (in Access):

WHERE Mid('00110001',SmallValue,1) = '1'

In some cases (long table, values not unique) this will work faster than IN/NOT IN. Your mileage may vary.

Please mind index start (1 for Access, other databases might start at 0).

Advertisements
Categories: Database Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: