Solved

# approximate vlookup in sql

Posted on 2011-10-20
Medium Priority
447 Views
How does one do a function in sql like approximate vookup(end is true and not false). do you still use some type of join for this?

sql '05
0
Question by:k1ng87

LVL 26

Expert Comment

ID: 36999718
can you explain what you want more?
0

LVL 9

Expert Comment

ID: 36999720
yes we will have to use join
0

LVL 1

Author Comment

ID: 37000092
Range | Value
------------------
1-4           a
5-8           b
11-15       c

If I had another table with a value 3 and I joined it on column Range, I should get back value A from the value column. If i was joining on a value that was 9, I should get back since 9 falls out in any ranges.
0

LVL 1

Author Comment

ID: 37000096
i should get back a null for a value of 9
0

LVL 70

Expert Comment

ID: 37002872
Yes, it's a join, but it needs to be LEFT join, since you may not find a match:

SELECT ..., vt.value, ...
FROM main_table mt
...other join(s)...
LEFT OUTER JOIN vlookup_table vt ON
mt.search_value BETWEEN vt.range_start AND vt.range_end
0

LVL 1

Author Comment

ID: 37031504
you have a range_start and range_end..i'm assuming i'd have to breakout the range into columns?
0

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 37032014
That would be ideal.

But if you're always using the "-" to indicate the range, you could do this:

LEFT OUTER JOIN vlookup_table vt ON
mt.search_value BETWEEN LEFT(vt.range, CHARINDEX('-', vt.range) - 1) AND SUBSTRING(vt.range, CHARINDEX('-', vt.range) + 1, 100)

To simplify coding, if you have only a single value in the range, still specify it as a range, like so:

1-4,
5-5,
6-9,
...
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month16 days, 9 hours left to enroll

#### 862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.