Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

String Compare

 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I'm trying to do a string compare using <> but its having less than the desired effect. In particular I'd like to do something like "WHERE x >= 'A' and X <= 'A'" to get all values that start with the letter A.

But, this kind of comparison considers 'A' < 'AB' so I end up with only those results exactly equal to 'A'. I thought of putting a wildcard in there, such as x >= 'A%' but no luck. Other than modifying the search string to be "WHERE x >= 'A' and X <= 'AZ'", I'm out of ideas.

-Scott
 
Ernest Friedman-Hill
author and iconoclast
Marshal
Pie
Posts: 24212
35
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't understand; what's wrong with "WHERE x LIKE 'A%'" ?
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I still need it to do range query such as "WHERE x >= 'A' and x <= 'Z'"

In the previous query, it would return me all records I want 'except' those starting with Z (other than those equal to Z)
 
Peer Reynders
Bartender
Posts: 2968
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Depending on the character set that you are using

might work.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, this is one of those things I probably oversimplified, the search strings don't need to be single characters. For example you can have "WHERE X >= 'pizza' and X <= 'soda'" I just used single characters for illustration.

I'm starting to think the best solution (although not pretty) is just to append ZZZZZ to the end of my second parameter.
 
Peer Reynders
Bartender
Posts: 2968
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It might be easier to simply write a package function that compares two strings and kicks out -1,0,1
(with the associated PRAGMA RESTRICT_REFERENCES(CmpStr,WNDS,WNPS,RNPS,RNDS) so that you can use it in SQL statements ).

Then you could write:

WHERE UTIL_PKG.CmpStr(x,lowerlimit) > -1 AND UTIL_PKG.CMPSTR(x,upperlimit) < 1

Though I'd be tempted to go further and create something like

WHERE UTIL_PKG.StrInRange(x,lowerlimit,upperlimit) = 1

that also reacts properly when either limit is NULL.
 
Paul Clapham
Sheriff
Posts: 21566
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Starting with anything between A and Y: WHERE x >= 'A' and x < 'Z'?
 
Jim Yingst
Wanderer
Sheriff
Posts: 18671
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
[EFH]: I don't understand; what's wrong with "WHERE x LIKE 'A%'" ?

[Scott]: I still need it to do range query such as "WHERE x >= 'A' and x <= 'Z'"


Hm, I'm still wondering about this. Why is a range query in that format preferable? EFH's answer still seems the most straightforward way to do this. Otherwise, Paul's solution of incrementing the last letter will work, and extends to longer substrings too. E.g. for words starting with FOO, look for WHERE x >= 'FOO' AND x < 'FOV'.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think there is something more to what you want to do that you just haven't quite told us yet. It seems like you want to build this query string dynamically, and the String values (What gets substituted for 'A' and 'Z')are only known at runtime.

So lets look at partial solutions that have been given. I still like the idea of a stored procedure on the Database side, where you pass in the String values, let the SP do the query and pass back a ResultSet. But that still doesn't determine what the SQL statement should look like.

Check out varchar2 comparison on this page
http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/sql_elements002.htm


I also highly recommend, if you can to use UPPER() to that case doesn't affect the query, unless you want it to.

And always use bind variables with Oracle.

Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic