Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Query Strings

SQL String queries

How to Query Strings

by  cdukes  Posted    (Edited  )
I have answered many questions about syntax errors when trying to query for strings in SQL and VB.
The following is my suggestion if you are having problems:

1) The string you are searching for must always be enclosed in single quotes :

SQL:

SELECT Name from MyTable where Surname = 'Fred'

VB:

mystr = "SELECT Name from MyTable where Surname = 'Fred'"

2) If the string you are searching for Contains a single quote then the single quote must be doubled

SQL:

SELECT Name from MyTable where Surname = 'Fred's'

VB:

mystr = "SELECT Name from MyTable where Surname = 'Fred's'"


3) If you are using variables in VB and the string you are searching for Contains a single quote then the single quote must STILL be doubled. This can be accomplished by writting a VB function that replaces any single quote with two single quotes. The SQL string must still be enclosed in single quotes:

VB:
mySearchstring = "Fred's"
NewString = ConvertQuotes(mySearchstring)

' Converts NewString into "Fred's"

mystr = "SELECT Name from MyTable where Surname = '" & NewString & "'"

4) The above is also true when calling SQL stored procedures:

SQL:

CREATE PROC GetName( @SearchString VARCHAR(50) )
AS
SELECT Name from MyTable where Surname = @SearchString

GO

CALL GetName ('Fred')
CALL GetName ('Fred's')


Hope this is usefull,


Chris Dukes
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top