String Validation

[mcm103]

I am trying to replace all quotation marks in a given string and replace it with a descriptive value so I can submit the string into a database without having part of the string cut off. I am using the replace function.

replace(string, FindString, ReplaceWithstring)

When the FindString equals " I get a syntax error.

What is the proper way to search for a quotation mark?

 

[onpnt]

syntax
Replace(expression, find, replacewith[, start[, count[, compare]]])

example
Dim str, Nstr
str = "Tek-Tips helps all"
Nstr = Replace(str, "all", "you&quot
' output Tek-Tips helps you"

---------------------------------------
{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }
---------------------------------------
for the best results to your questions: FAQ333-2924

http://www.onpntwebdesigns.com

 

[mcm103]

My problem is not with the syntax of the replace function. It is using the replace function to replace a quotation mark.

 

[DeRoe]

If you are trying to Replace a ' you should use

FindString = "'"
Replace(string, FindString, ReplaceWithstring)

If you are looking for the " you use

FindString = """"
'1st " is to open the string
'2nd en 3th " is to get a " in the string
'4th " ends the string
Replace(string, FindString, ReplaceWithstring)

Greetz

 

[onpnt]

sorry, use regex for finding and replacing a "
pattern for you would be [\"]

Dim regEx, str1, replStr
replStr = " dash was here "
str1 = "Tek-Tips"
Set regEx = New RegExp
regEx.Pattern = "-"
regEx.IgnoreCase = True
ReplaceTest = regEx.Replace(str1, replStr)
alert ReplaceTest ---------------------------------------
{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }
---------------------------------------
for the best results to your questions: FAQ333-2924

http://www.onpntwebdesigns.com

 

[mcm103]

Did that. Still get an error.

 

[onpnt]

did which? the regular expression will and should work fine for any special character as a quote. what is the error. ---------------------------------------
{ str = "sleep is good for you. sleep gives you the energy you need to function";
ptr = /sleep/gi;Nstr = str.replace(ptr,"coffee"alert(Nstr); }
---------------------------------------
for the best results to your questions: FAQ333-2924

http://www.onpntwebdesigns.com

 

[dilettante]

Hmmm.

The usual trouble is with apostrophes (') in a value to be stored into a database, generally because people are using SQL to do their stores rather than doing it in ADO (setting fields in the current or a new record in a recordset, calling the Update method).

In any case... could it be that you are having trouble with the syntax for quotes in literals in VBScript?

For example:

strClean = Replace(strRaw, "'", "''")

Or if you really are having trouble with quotes:

strClean = Replace(strRaw, """", """""")

I guess it depends on your database engine and what you're trying to do. Most SQL parsers treat an apostrophe as the string delimiter (legacy of IBM's development of SQL), and will handle two-in-a-row as meaning one.

This is basically a repeat of what DeRoe already said.

VBScript regular expressions can be powerful, but for simple replaces like this they tend to be quite a bit slower than the intrinsic Replace(). If you do use the RegExp object for this be sure to set the Global property True. You don't need to IgnoreCase looking for a quote.

 

[RobSchultz]

Const QuoteReplacement = "%34"
oldstring = "This string has a "" in it."
newstring = Replace(oldstring, chr(34), QuoteReplacement)
MsgBox "old:" & oldstring & vbcrlf & "new:" & newstring

HtH,
Rob
robschultz@yahoo.com
-Focus on the solution to the problem, not the obstacles in the way.-

 

[dilettante]

Not sure what pseudo URI-encoding does to help here. Anything trying to read this stuff from the database would find a bunch of %34s and have to convert them back.

This is why you normally double-up the quotes, to get the value you have stored into the database. This is just a SQL syntax issue.

 

[RobSchultz]

dilettante,

You're right. Since we're in the VBScript forum I only read "replace", "quotation marks" and "descriptive value". It is definitely better to use an SQL method to solve an SQL problem.

regards, Rob
robschultz@yahoo.com
-Focus on the solution to the problem, not the obstacles in the way.-

 

[Tarwn]

mcm103: DeRoe's example should have pretty much cleared you up for both single quotes and double quotes.

The reason his replace string for double quotes has two quotes in it is so that VBScript will not think your attempting to end the string, but will instead include a single duoble quote in the string(this is known as an escape character), this was shown in a couple examples above but never actually explicitly stated.
Therefore:
Dim myString
myString = "there is a quote "" here"

will be stored as a string:
there is a quote " here

So in your replace if you use 4 quotes like DeRoe suggested, ie replace str = """"
Then the outer two are your string delimiters, and the inner two resolve to a single double quote inside the string.

Hope that was what you were looking for,
-Tarwn ________________________________________________________________________________
Want to get great answers to your Tek-Tips questions? Have a look at faq333-2924