how to make BETWEEN QUERY for string variables
hi ,
i have to pick some value from given string variable using SQL statement,
the upper limit and lower limit is given by user and then i hav to pick all string variable name who fall between this range
ex:
ram josp tera mike sita atulramesh hira
these are 8 name , if user pick "josp"(as upper limit ) and "ramesh "(as lower) limit then , i have to pick all name who fall between these two name and show these name .
[459 byte] By [
smallkida] at [2007-11-27 10:55:52]
# 1
Here is one example:
SELECT C.CUST_ID, C.LAST_NAME
FROM CUST_TABLE C
WHERE UPPER(C.LAST_NAME) BETWEEN 'APPLEGREN' AND 'MILFORD'
UPPER is whatever function used by your database to convert column data from mixed case to all upper case. Without the upper case conversion a column with mixed case would not provide you the correct results.
In some databases the user of UPPER will not allow use of an index on the names and may slow down your query.
Written as a properly formed PreparedStatement for Java (which is what you should be using):
SELECT C.CUST_ID, C.LAST_NAME
FROM CUST_TABLE C
WHERE UPPER(C.LAST_NAME) BETWEEN ? AND ?
You can then use the ResultSet setString(n,val) functions to set the values:
rs.setString(1,'APPLEGREN');
rs.setString(2,'MILFORD');
Good Luck.
# 2
hi ,
thx for reply but do you think that this is apply for string variable.
exp:
{ abc , nameA , mike , tina , rox, katty , nameB , willy , tomy }
means how SQL statement know thatmike , tina , rox, katty all names fall betweenyour upper/start limit string nameAand last limit nameB.
how he compair between name/string variable .
# 3
>how he compair between name/string variable .
Hmmm... How was the following unclear?
> Without the upper case conversion a column
> with mixed case would not provide you the correct
> results.
My example was specific to a String. Why do you think I was talking about something other than a String comparison?
My only assumption is that you want a list of names between two names and that the order you are concerned about is alphabetical. If you are looking for a list based on insertion date or some other criteria you did not specify anything about that.
# 4
hi ,
now you are going in right direction ,
i want the name list who fall between any two name from given names .
how to pick name between name list "c " and "r "
{ a, b , c , d , g, t, r,z ,o}
think these are name , then how to choose between "c" name and "r " .i want that Sql Statement . i make sure all are different string name not alphabet as shown in example
# 5
>now you are going in right direction ,
But you are not.
> i want the name list who fall between any two name
> from given names .
>
> how to pick name between name list "c " and "r
> "
> { a, b , c , d , g, t, r,z ,o}
>
> think these are name , then how to choose between "c"
> name and "r " .i want that Sql Statement . i make
> sure all are different string name not alphabet as
> shown in example
Why don't you ask specific questions about the answers given?
Or explain specifically why you think that the answers given do not meet what you are trying to do.
Because right now your posts are making no sense.
# 6
hi ,
>"Why don't you ask specific questions about the
> answers given?
>
> Or explain specifically why you think that the
> answers given do not meet what you are trying to do."
nice to know that a person who post more then 27,000 ans , still not able to understand my problem .its compliment for me .
if he dont getting exect problem then how he can ans .
anyways my last try to explain the problem to some great mind who thinkthey are ............
i have some name in my table
think column name is "NAME" and he some value like {ram, sita, jok , marks, stepy , todd , ston , make , vijay , ....}
now i pass to name think "jok" and " ston" that means i want all rows who having NAME column value "jok , marks, stepy , todd , ston " cos these name fall between two passed name .
** in front view we fixed all name position , we set ruler on any two name
and those all name who fall under these 2 name ,we wants them .
in short we want to select all those row , who having these name , who fall
between two name given by front view .
* i repeat in front view we fixed all name position , and on the basis of position we have to pick al name .
in DB we hav just names , not there position value .
Actully now i m not ecpecting any answer from all of you , i just want to make you understand the problem . i guess soon i personally mail the solution , i guess thats my 6-7 post in this form with answer .
Message was edited by:
smallkid
Message was edited by:
smallkid
# 7
I'm sure this wasn't your intention, but your attempt at what I would call rye humor, written in what appears to be your 2nd language is only coming off as arrogance. I'll give you the benefit of the doubt and see if I can communicate what I think is a problem is. I dont believe you understand the base concepts of a database; specifically how rows are stored in a database.
1) A database row never has a fixed position. You can never know what position a row is in relative to the beginning or end of the table, nor can you know the position of a row relative to any other row. Without an ORDER by clause in your SQL there is no guaranteed order of rows when they are returned as a ResultSet.
2) If you throw your concern away about fixed position of rows, I am confident that my original answer is exactly what you need with one small change. I should have added an ORDER BY C.LAST_NAME as the last clause in the SQL statement. That would provide a consistent "fixed" order for display in your GUI if that is your need. Here is the updated SQL:
SELECT C.CUST_ID, C.LAST_NAME
FROM CUST_TABLE C
WHERE UPPER(C.LAST_NAME) BETWEEN 'APPLEGREN' AND 'MILFORD'
ORDER BY C.LAST_NAME
3) It's difficult to tell from you text, but you may be talking at one point about the GUI (I think you call it front view). The order of rows in a database has nothing to do with the order in which the rows are displayed. They are not connected in anyway.
This is way easier then you are thinking. Code the SQL as per my example and I believe your results will be what you want. If not, after you have tried something, (anything) and don't get the results you need, please post those results with your code. I believe that will clarify exactly what your problem is.
A little code contrasting your results with your expected results will be worht a 1000 words.
# 8
> nice to know that a person who post more then
> 27,000 ans , still not able to understand my problem
> .its compliment for me .
No, it isn't a compliment. It simply means that your attempts at explanation are very bad.
But this is most likely because your command of English is not very good. Perhaps you should consider asking somebody whose English is better to help you translate your problem.
# 9
>
>i have some name in my table
> n name is "NAME" and he some value like {ram, sita,
> jok , marks, stepy , todd , ston , make , vijay ,
> ....}
>
> now i pass to name think "jok" and " ston"
> that means i want all rows who having NAME column
> value "jok , marks, stepy , todd , ston " cos
> these name fall between two passed name .
>
So your requirements are
1. Pass in only those names
2. Only those names (only that column) exist in the database.
3. You don't want 'ram' returned.
The answer is simple - it is impossible.
So you need to modify your requirements or give up.
Now if your table has another column which represents an ordered value which presents the order above (ram first,...vijay last) then it is possible. Note that this column must be sequentially orderable.
Or you have some other datat that you can pass in like a creation date (which requires another column) or an exclusion list (which would include 'ram') then it is possible.
# 10
> So your requirements are
> 1. Pass in only those names
> 2. Only those names (only that column) exist in the
> database.
> 3. You don't want 'ram' returned.
>
> The answer is simple - it is impossible.
>
> So you need to modify your requirements or give up.
>
> Now if your table has another column which represents
> an ordered value which presents the order above (ram
> first,...vijay last) then it is possible. Note that
> this column must be sequentially orderable.
>
> Or you have some other datat that you can pass in
> like a creation date (which requires another column)
> or an exclusion list (which would include 'ram') then
> it is possible.
Wow, I'm not sure how you came up with requirements. If that's correct, nice job. Personally, I was going to have to wait for a bit more information from the OP to try and figure out what he really wants. If you are correct about his requirements then I guess I was correct in my assumption that he was thinking rows had fixed positions in the database. Also, if you are correct, your analysis of what to do seems good.
# 11
hi ,
thaks all
i dont wanna hurt anybody , may be i used some hard words , but i dont mean it .i was in work pressure .
requirements are
1. Pass in only those names ( any two name )
2. Only those names (only that column) exist in the UI between those name .
in UI name sequence is fix , but name is string type so no way to findwhich name come or after .( I KNOW ROW's POSITION R NOT FIX dont worry )
3. we want only all that name who comes between these two name in UI
(.......i guess soon we have add picture facility in form so we can better explain our problem ).
may be it is not possible , so i m thinking to change my req.
# 12
**** it. This is hopeless.
# 13
> F*** it. This is hopeless.
Agreed. Yet it shouldn't be...
# 14
>
> Wow, I'm not sure how you came up with requirements.
The context in my post provided that. There was a source list of names. And a result list. The result list did not include 'ram' but did include other values that were alphabetically between the two passed in value. And 'ram' would be between them as well.
Thus the requirements.
# 15
> The context in my post provided that. There was a
> source list of names. And a result list. The result
> list did not include 'ram' but did include other
> values that were alphabetically between the two
> passed in value. And 'ram' would be between them as
> well.
Ah, so my preconception of how a database works worked against me and allowed me to miss that as the requirement (and the fact that I could only kind of understand the OP's English); although I did mention that I was concerned that he he had a preconception that a database had "fixed" rows because he wasn't satisfied with my alphabetical "between" solution. Thanks for the clarification; it tells me that I need to loosen up a bit on my analysis and perhaps not make so many assumptions. The fact is I have no idea if the OP was helped as I have no idea what this meant:
"may be it is not possible , so i m thinking to change my req."
# 16
> Thanks for the clarification; it tells me that I
> need to loosen up a bit on my analysis and perhaps
> not make so many assumptions.
Probably best to keep making assumptions particularly on all vague questions. I am not sure I have ever seen a vague question that resolved to a specific question that didn't have some fundamental knowledge problems.
# 17
> > F*** it. This is hopeless.
>
> Agreed. Yet it shouldn't be...
I had originally posted something else. But then I realized I had basically said what you said in reply #1 which was apparently wrong though I still don't know how or why.
# 18
After another hour of pondering I finally figured it out. It's like one of those 3d pictures, you have to let your mind unfocus, think too much and you'll miss it.
Great job jschell in figuring that out.
