Quotes in SQL (Those Darn Things!)

By ServerWatch Staff (Send Email)
Posted Aug 9, 1999


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'"

Page 1 of 2


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.