Search Through All Between Values SQL

Question

I have data following data structure..

   _ID             _BEGIN           _END    
    7003            99210            99217        
    7003            10225            10324    
    7003            111111

I want to look through every _BEGIN and _END and return all rows where the input value is between the range of values including the values themselves (i.e. if 10324 is the input, row 2 would be returned)

I have tried this filter but it does not work..

where @theInput between a._BEGIN and a._END


--THIS WORKS 
where convert(char(7),'10400') >= convert(char(7),a._BEGIN) 

--BUT ADDING THIS BREAKS AND RETURNS NOTHING
AND convert(char(7),'10400') < convert(char(7),a._END)

Help us out a bit here - what is @theInput you're querying with? What does the rest of the query look like? — n8wrl, May 29 '14 at 20:34
possible duplicate of http://stackoverflow.com/questions/23742523/find-row-with-data-between-a-range-sql — mika, May 29 '14 at 20:35
To use `between` I believe `@theInput`, `a._BEGIN`, and `a._END` have to be the same type. Are they? You stated that `@theInput` is a `char` datatype. — Tony, May 29 '14 at 20:35
yes all fields used are char datatype. Do I have to cast to int or something? If so how? — Nick LaMarca, May 29 '14 at 20:42
@NickLaMarca I do not understand. How can it be char if they are greater than 255? — johnny, May 29 '14 at 20:44
the type of _BEGIN and _END are char(7). So technically a value in _BEGIN could be 'myName1'. The fields should be integer but their not — Nick LaMarca, May 29 '14 at 20:48
If the types are not integer then the comparison is chronological based on the first letter. It means `b...` is between `a...` and `c...` — revo, May 29 '14 at 20:52
So, your initial problem is one of design: you should not have char(7) columns holding values that will always be integers. If you can change the design, you should. — outis nihil, May 29 '14 at 20:53
@NickLaMarca What if you put the whole thing in parentheses after your where? — johnny, May 29 '14 at 21:20

revo · Answer 1 · 2014-05-30T15:05:50.933

1

Less than < and greater than > operators work on xCHAR data types without any syntactical error, but it may go semantically wrong. Look at examples:

1 - SELECT 'ab' BETWEEN 'aa' AND 'ac' # returns TRUE

2 - SELECT '2' BETWEEN '1' AND '10' # returns FALSE

Character 2 as being stored in a xCHAR type has greater value than 1xxxxx

So you should CAST types here. [Exampled on MySQL - For standard compatibility change UNSIGNED to INTEGER]

WHERE CAST(@theInput as UNSIGNED)
    BETWEEN CAST(a._BEGIN as UNSIGNED) AND CAST(a._END as UNSIGNED)

You'd better change the types of columns to avoid ambiguity for later use.

edited May 30 '14 at 15:05

answered May 29 '14 at 20:59

revo

47,783
14
74
117

...so what if it's based on the "first letter"? He's dealing with numbers here... how does the point you're making apply here? Note that the OP has asked a duplicate of his own question, so you should answer there. Also, the `UNSIGNED` type likely isn't present on all RDBMSs - you'd probably be better served using plain `INTEGER`. – Clockwork-Muse May 30 '14 at 14:14
@Clockwork-Muse I'm pointing out the collation sequence on xCHAR types - in which `SELECT 'a' < 'b'` returns `1`. This applies on `BETWEEN .. AND ..` operator as well. You're right on RDBMSs, but I replied to OP words in comments requesting about MySQL. – revo May 30 '14 at 14:39
Except you need to mention why that's important for **NUMBERS**. Just mentioning letters is rather opaque. You're not even making it clear that this means that `'2'` is thus greater than `'11'`. – Clockwork-Muse May 30 '14 at 14:52
...except you've explained the _results_ of the process, but not _why_. – Clockwork-Muse May 31 '14 at 03:18

Dog Ears · Accepted Answer · 2014-05-29T20:54:55.117

0

This would be the obvious answer...

SELECT * 
FROM <YOUR_TABLE_NAME> a
WHERE @theInput between a._BEGIN and a._END

If the data is string (assuming here as we don't know what DB) You could add this.

Declare @searchArg VARCHAR(30) = CAST(@theInput as VARCHAR(30));
SELECT * 
FROM <YOUR_TABLE_NAME> a
WHERE @searchArg between a._BEGIN and a._END

If you care about performance and you've got a lot of data and indexes you won't want to include function calls on the column values.. you could in-line this conversion but this assures that your predicates are Sargable.

edited May 29 '14 at 20:54

answered May 29 '14 at 20:37

Dog Ears

9,637
5
37
54

what software are you using? what version of sql is it? – Dog Ears May 29 '14 at 20:39
I think my problem is the field _BEGIN, _END is of type char. How can I cast it to int or something maybe thats the issue – Nick LaMarca May 29 '14 at 20:41
@NickLaMarca what SQL are you using? Microsoft? DB2? Oracle? MySQL? – Dog Ears May 29 '14 at 20:50
can you show me a convert for mySQL as well as Microsoft? I am not sure the flavor. I am using WinSQL ide if that helps – Nick LaMarca May 29 '14 at 20:55
convert(integer,_BEGIN) is not working and convert is not highlighted as a keyword – Nick LaMarca May 29 '14 at 20:56
A quick search indicates that the above should work in MySQL - YOu can execute `SELECT @@VERSION` or `SELECT VERSION()` one should work? – Dog Ears May 29 '14 at 20:57
I am using sql server 2008 – Nick LaMarca May 30 '14 at 14:17

score 0 · Answer 3 · edited May 23 '17 at 10:32

0

    SELECT * FROM myTable
    WHERE 
    (CAST(@theInput AS char) >= a._BEGIN AND @theInput < a.END);

I also saw several of the same type of questions:

SQL "between" not inclusive

MySQL "between" clause not inclusive?

When I do queries like this, I usually try one side with the greater/less than on either side and work from there. Maybe that can help. I'm very slow, but I do lots of trial and error.

Or, use Tony's convert.

I supposed you can convert them to anything appropriate for your program, numeric or text.

Also, see here, http://technet.microsoft.com/en-us/library/aa226054%28v=sql.80%29.aspx. I am not convinced you cannot do your CAST in the SELECT.

Nick, here is a MySQL version from SO, MySQL "between" clause not inclusive?

edited May 23 '17 at 10:32

Community

1
1

answered May 29 '14 at 20:37

johnny

19,272
52
157
259

I think my problem is the field _BEGIN, _END is of type char. How can I cast it to int or something maybe thats the issue – Nick LaMarca May 29 '14 at 20:40
@NickLaMarca You need to tell us what database you are using. Is it SQL Server with the @? – johnny May 29 '14 at 20:41
convert(integer, a._BEGIN) – Tony May 29 '14 at 20:41
@Tony beat me to it. I guess I can edit, but would you not cast @theInput? – johnny May 29 '14 at 20:42
Can you please show @Input casted as well as the 2 fields? – Nick LaMarca May 29 '14 at 20:43
@NickLaMarca I would if I understood better. I don't understand why you are saying all the values are char when they are above 255. – johnny May 29 '14 at 20:45
the type of _BEGIN and _END are char(7). So technically a value in _BEGIN could be 'myName1'. The fields should be integer but their not – Nick LaMarca May 29 '14 at 20:46
`convert(integer, @theInput)`, `convert(integer, a._BEGIN)`, `convert(integer, a._END)` but I would make sure that they are all numeric. If they are all char I would probably not convert to int but instead I would convert to varchar(10) or some other number that will not cause truncation, just in case there is a non-numeric character in the string. – Tony May 29 '14 at 20:49
After reading your last comment I would say to cast `@theInput` to `char(7)` by using `convert(char(7), @theInput)`. You might need to `trim` too. – Tony May 29 '14 at 20:51
I added code to the question only part of the filter is working. the < part of the clause is returning nothing when it should return a row – Nick LaMarca May 29 '14 at 21:12

Search Through All Between Values SQL

3 Answers3