dcsimg

Quotes in SQL (Those Darn Things!)


Watkins

corner.jpg (739 bytes) Quotes in SQL (Those Darn Things!) corner2.jpg (786 bytes)

See this script in action: click here

About two months ago, I got done writing a large application used to look up listings for different towns. The thing was, I never actually thought of the fact that some towns have ' ' ' (quotes) in their names. This was not good, considering the site was live, and I had an error. For example:

About two months ago, I got done writing a large application used to look up listings for different towns. The thing was, I never actually thought of the fact that some towns have " ' " (quotes) in their names.   This was not good, considering the site was live, and I had an error. For example:

I would have a town, and its name would be: John's Town (not real). What ended up happening was when the query string was created, the single quote was added to the string and it looked like this:

"SELECT * FROM table WHERE town='John's Town';" An error will occur when you execute this SQL statement, saying unexpected ending.

I needed to solve this quick, and quick I did. I used the following function to search through the string and put an extra quote in where needed.

<SCRIPT LANGUAGE="VBScript" RUNAT="SERVER">
Function convertToSQL(strStmt)
    For i = 1 to len(strStmt)
        If Mid( strStmt, i, 1 ) = "'" Then strOut = strOut & "'"
        strOut = strOut & Mid(strStmt, i,1)
    Next
    convertToSQL = strOut
End Function
</SCRIPT>

In an SQL statement you do not need to enclose the value in quotes if it is a number. However, when you are using a string, you need to enclose the value.

Example:

Dim town = "John's Town"
strQuery = "SELECT * FROM table WHERE town='" & convertToSQL(town) & "';"


The resulting string would be as followed.

"SELECT * FROM table WHERe town='John''s Town'"

This article was originally published on Aug 10, 1999
Page 1 of 2

Thanks for your registration, follow us on our social networks to keep up-to-date