Regular Expression Help

[Chuck Haeberle]

I have an interesting regular expression challenge for someone more
experienced with them than I for a data layer class...

I need an expression to search a SQL statement (any type, SELECT INSERT
UPDATE OR DELETE) and find all single apostrophes which should be replaced
with a double apostrophe without affecting the apostrophes used to delimit
the string values:

Example:
SELECT field FROM table WHERE stringfield = 'This is one of Jerry's worst
examples. Who's going to fix it?' or stringfield2='Where's Wendy's Beef
Hamburger'
or
INSERT INTO table (field1, field2) VALUES ('Wings'n'things', 'Who's Fancy
Restaurant')

I can get a pattern which matches a single ' within two other ', but get
hung up when I start trying to cope with multiple ' within the enclosing ',
and I can't even fathom how to handle the potential for multiple matches...

Anyone feel like giving it a shot? I really would not want to have to
figure this out manually nor do I want to force the users of my data layer
to pre process their strings manually...

 

[Paul E Collins]

I need an expression to search a SQL statement
(any type, SELECT INSERT UPDATE OR
DELETE) and find all single apostrophes which
should be replaced with a double apostrophe
without affecting the apostrophes used to delimit
the string values:

You can't do this unambiguously. Here, for example ...

SELECT x FROM y WHERE text='something' OR text='other'

.... OR might be part of one long string with apostrophes in it.

A better idea is to pass a set of parameters to your data layer class and
use them to replace simple placeholders for their locations in the query
string. That way, you know which parts are parameters and can deal with them
accordingly.

void ExecuteSQL(string strQuery, params string[] strParameters);

My example would then be written:

MyDataLayer.ExecuteSQL("SELECT x FROM y WHERE text='?' OR text='?'",
"something", "other");
.... or ...
MyDataLayer.ExecuteSQL("SELECT x FROM y WHERE text='?'", "something' OR
text='other");

P.

 

[amby]

in vb i used to replace using the replace function
i guess you could do the same here

public string Quotesafe(string sourcestring )
{
return regex.replace(sqlstring, "'", "''")
}

but you gotta do it before placing the value inside the single quotes for
the sql string

eg. varString = "d'costa"
"update employee set name = '"+ quotesafe(varstring) + "'"


hth

I need an expression to search a SQL statement
(any type, SELECT INSERT UPDATE OR
DELETE) and find all single apostrophes which
should be replaced with a double apostrophe
without affecting the apostrophes used to delimit
the string values:

You can't do this unambiguously. Here, for example ...

SELECT x FROM y WHERE text='something' OR text='other'

... OR might be part of one long string with apostrophes in it.

A better idea is to pass a set of parameters to your data layer class and
use them to replace simple placeholders for their locations in the query
string. That way, you know which parts are parameters and can deal with them
accordingly.

void ExecuteSQL(string strQuery, params string[] strParameters);

My example would then be written:

MyDataLayer.ExecuteSQL("SELECT x FROM y WHERE text='?' OR text='?'",
"something", "other");
... or ...
MyDataLayer.ExecuteSQL("SELECT x FROM y WHERE text='?'", "something' OR
text='other");

P.