Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-10 : 07:59:31
|
| Eric writes "Hello Guru,I am setting up a "simple" database search for a 911 database. Knowing that the user will not always get the spelling correct for a street name I want a SQL to search a database within the field "911StreetName" for a user input variable (from a form) that is named varStreetSearch. Is this possible to have a SQL statement search the 911 number which has to be exact (a portion I have working)and then a user string (a street name) that doesn't have to be a exact match? Is the SQL keyword LIKE used? My code is a follows (below). I have it working when the user search the database for an exact match for a 911 number but am having a hard time with the second portion of the search (adding the street name into the mix). I just can't seem to get the syntax correct if I do use LIKE in the "sqltext" string I hope you might have some insight for a newbie on this subject. Thanks for your time I appreciate it. Sincerely,Eric KeefauverJeffersonville, VT var911ID= request.form("911Number") varStreetSrch= request.form("StreetName") dim oRSpSet oRSp=server.createobject("ADODB.recordset") 'working portion of code...this workssqltext = "Select * FROM 911 "sqltext = sqltext & " WHERE fld911Address= " & var911ID & ";" 'having trouble here concatenating the sqltext with the next portion of my search Have been'trying to use the LIKE keyword and having no luckoRSp.open sqltext, "dsn=911" 'display column info for the record selected by sql query The Address has the following flood information:Street Name is: =oRSp("fldStreetAddress")NFIP Community is: =oRSp("fldNFIPComm")NFIP Community Number: =oRSp("fldNFIPNumber")NFIP Panel Number: =oRSp("fldNFIPPanel")NFIP Map Date: =oRSp("fldMapDate")FEMA Zone: =oRSp("fldFloodStatus")Is property in a flood zone?: =oRSp("fldFloodYN") </body></html>" |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-02-10 : 12:51:36
|
| The syntax of the LIKE clause would be:sqltext = "Select * FROM 911 "sqltext = sqltext + " WHERE fld911Address LIKE " & CHR(39) & "%" & "some value" & "%" & CHR(39)The CHR(39) will wrap single quotes around your string and the % are wildcard delimiters. You also might want to investigate using SOUNDEX instead of LIKE. I've only used SOUNDEX on one or two occasions myself. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-02-10 : 13:04:29
|
| I agree, SOUNDEX() would probably work well for street names.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
ASP & SQL