Visual Basic&database, Substring & SQL Query

Substring & SQL Query

Author	Message
Carma Internation #1 / 6	Substring & SQL Query I'd like to run an SQL query where the pseudocode is: select * from <table> where <textfield> is a text field contained within S where S is a predefined string variable. Is that possible ? Thanks, John Field
Sat, 06 Feb 1999 03:00:00 GMT

David Fores #2 / 6	Substring & SQL Query Quote: > I'd like to run an SQL query where the pseudocode is: > select * from <table> where <textfield> is a text field > contained within S where S is a predefined string variable. > Is that possible ? > Thanks, > John Field I think what you're looking for is: "Select * from <table> where <textfield> = '" & S &"';" D.
Sat, 06 Feb 1999 03:00:00 GMT

Joe Fost #3 / 6	Substring & SQL Query Quote: > I'd like to run an SQL query where the pseudocode is: > select * from <table> where <textfield> is a text field > contained within S where S is a predefined string variable. > Is that possible ? Dim SQL As String: SQL = "select distinctrow [" & TableName & "].*" SQL = SQL & " from [" & TableName & "] where Instr('" & S & "'," SQL = SQL & " [" & TableName & "].[" & FieldName & "]) > 0;" This query is going to require a linear scan of the entire table. Quote: > Thanks, > John Field -- WARNING: I cannot be held responsible for the above They're coming to because my cats have apparently learned to type. take me away, ha ha!
Sat, 06 Feb 1999 03:00:00 GMT

Dave Verschleise #4 / 6	Substring & SQL Query Quote: > I'd like to run an SQL query where the pseudocode is: > select * from <table> where <textfield> is a text field > contained within S where S is a predefined string variable. > Is that possible ? > Thanks, > John Field John, SELECT * FROM table WHERE Instr(S,textfield) > 0 This should work if you're using an Access database. -- Thanks, Dave Verschleiser Q&D Software Development - http://www.q-d.com Creators of WebForms and WebMania! ======================================================= For the best SHAREWARE on the Net, check out: The Shareware Central Interactive Catalog http://www.q-d.com/swc.htm Shareware authors -- find out how to obtain a listing! =======================================================
Sun, 07 Feb 1999 03:00:00 GMT

Thomas Huge #5 / 6	Substring & SQL Query Quote: > > I'd like to run an SQL query where the pseudocode is: > > select * from <table> where <textfield> is a text field > > contained within S where S is a predefined string variable. > > Is that possible ? > Dim SQL As String: SQL = "select distinctrow [" & TableName & "]." > SQL = SQL & " from [" & TableName & "] where Instr('" & S & "'," > SQL = SQL & " [" & TableName & "].[" & FieldName & "]) > 0;" > This query is going to require a linear scan of the entire table. > > Thanks, > > John Field > -- > WARNING: I cannot be held responsible for the above They're coming to > because my cats have apparently learned to type. take me away, ha ha! How about... Select from tablename where fieldname LIKE '%mysubstring%' Different SQLs may use something besides "%" for multilength wildcard. -- Thomas Hugel Brown Distributing Co. (Budweiser) 1330 Allendale Rd. West Palm Beach, FL 33405
Sun, 07 Feb 1999 03:00:00 GMT

Joe Fost #6 / 6	Substring & SQL Query Quote: > > > I'd like to run an SQL query where the pseudocode is: > > > select * from <table> where <textfield> is a text field > > > contained within S where S is a predefined string variable. > > > Is that possible ? > > Dim SQL As String: SQL = "select distinctrow [" & TableName & "]." > > SQL = SQL & " from [" & TableName & "] where Instr('" & S & "'," > > SQL = SQL & " [" & TableName & "].[" & FieldName & "]) > 0;" > > This query is going to require a linear scan of the entire table. [chomp] Quote: > How about... > Select from tablename where fieldname LIKE '%mysubstring%' > Different SQLs may use something besides "%" for multilength wildcard. You may be right. I interpreted the question as, find all records where the string S contains the value of text field X. Your query, on the other hand, will find all records where the value of X contains S. My interpretation fits this query: Select * from tablename where 'mysubstring' LIKE '%' + fieldname + '%' I decided to use InStr in my original query because of the possibility of S containing characters special to the LIKE operator, but perhaps that made things less clear. [snip] -- WARNING: I cannot be held responsible for the above They're coming to because my cats have apparently learned to type. take me away, ha ha!
Sun, 07 Feb 1999 03:00:00 GMT

Page 1 of 1

[ 6 post ]