SUMMARY: SQL Wild Card Question

["Cook, Jenise" <jenise -dot- cook-crabbe -at- pacificlife -dot- com>]

After surviving this morning's 4.4 temblor at 11:04 a.m. seven miles SE off
the coast of San Clemente, CA.... 

I'd like to thank these list members for responding to my HELP ASAP plea
yesterday. First, I'll give you the happy news. Second, I'll include their
replies.

_Query_
Do you recognize the * as a universally-used search wild card symbol? (Yes.)
With SQL behind our Web application, can we change the current % search wild
card to the *? (Yes. see below)

_Happy News_
The Program Manager definitely wants our Web application to use the * as a
search wild card, because that's what our users know. I had proposed this,
and am grateful he sees things the same way. So, our users will be able to
enter in the Company Name field [ *& Sons ] instead of [ %& Sons ].

_Responses About SQL_
<<Peter Neuman>>
Peter passed on a URL for those, like me, who need to learn SQL. The URL is:
http://www.raffiudeenillahideen.org/SQL/SQLByExamples/

<<Christine Anameier>>
There is almost certainly a way to convert all the *s to %s after the user
submits the search. If this is an HTML form sending the input to a CGI
script, the script can look for asterisks and translate them to what SQL
wants to see. Javascript can do that too, I think. (Wish I had my Javascript
book handy.)  Whatever is handling the input right after the user hits
Submit should be able to do this.

<<Alan Oslick>>
Now that I think I understand the direction of the data flow, a bind
variable in SQL could convert an "*" in the input stream to a "%", again
with the caveat that one would have to take care of possible "%" in the
input stream (as a bind variable making them blank or "pct" with allowance
for the extra spaces if critical in printing.)

<<David Castro>>
Not only is the * the universal wild card in web searches, it is also the
wild card for SQL. (I'm sure that the former is *because* of the latter.) If
you want to say "give me all of the values for all of the columns in
tableA," you would issue the SQL query: SELECT * FROM tableA

In fact, the % symbol means something entirely different in SQL. This is
used in LIKE matching in SQL. If you want to find the string "*David*" in
the values in a column, you would issue the SQL query: SELECT columnname
FROM tablename WHERE columname LIKE '%David%'

<<Samantha Alper>>
You have a web app, yes?  That means something is sitting between the
interface where users enter stuff, and the DB. That something can take the
user's input, check for "*" and present it to the database with a "%".  Even
if the string has just been being literally passed in up to now (which seems
unlikely, given that SOMETHING is forming SQL statements) it should be a
little 3-line change in the code.

I'd say make it usable for users -- if they expect "*" to mean wildcard
within a field as well as all fields, then do it that way.

<<Danette Thompson>>
I work on an application written in PowerBuilder and based on a SQL database
here. I've written many select statements, and I checked with my developers
to confirm this:

In SQL, "*" can be used to represent field names. For instance, "SELECT *
FROM Table_Name". On the other hand, if you want to use a wild card
character within the WHERE clause, you need to use the % sign, not the
asterisk. SQL will recognize the asterisk as an actual character, not a wild
card. I don't believe this is something that your developers can change, as
it is part of the actual language. However, there might be workarounds, such
as using a left outer join to set all characters equal to an asterisk. But
that would add a lot of complication for your users.

Again, thanks everyone!

Jenise Cook-Crabbe
Senior Technical Writer
Pacific Life Insurance Company
www.PacificLife.com
jcook -at- pacificlife -dot- com

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

*** Deva(tm) Tools for Dreamweaver and Deva(tm) Search ***
Build Contents, Indexes, and Search for Web Sites and Help Systems
Available now at http://www.devahelp.com or info -at- devahelp -dot- com

A landmark hotel, one of America's most beautiful cities, and 
three and a half days of immersion in the state of the art:
IPCC 01, Oct. 24-27 in Santa Fe.  http://ieeepcs.org/2001/

---
You are currently subscribed to techwr-l as: archive -at- raycomm -dot- com
To unsubscribe send a blank email to leave-techwr-l-obscured -at- lists -dot- raycomm -dot- com
Send administrative questions to ejray -at- raycomm -dot- com -dot-  Visit 
http://www.raycomm.com/techwhirl/ for more resources and info.