By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,364 Members | 1,580 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,364 IT Pros & Developers. It's quick & easy.

Trying again: Need help with RegEx for SQL

P: n/a
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...

This regex stuff makes my head hurt...
Nov 15 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
If you start out right then you'll save yourself a lot of headache.

when you're building up the sql, fix it right then by escaping the single
quotes with DOUBLED single quotes, dont wait til afterwards to try to figure
out whats going on.

if you REALLY want to parse after the fact, I dont believe regex's will work
well, you'll have to actually parse the text but if you're inconsistent then
good luck trying to get that to work either...

The key is to fix it at the start, before it even has a chance to become a
problem, OR use named parameters:
cmd.CommandText = "UPDATE Table Set FIELD1=@Field1 WHERE FieldID=@FieldID"
cmd.Parameters.Add("@Field1",DbType.VarChar,50).Va lue = "Eric's sample"
cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1
--
Eric Newton
C#/ASP Application Developer
http://ensoft-software.com/
er**@cc.ensoft-software.com [remove the first "CC."]

"Chuck Haeberle" <ch******@contractor.nocienaspam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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...

This regex stuff makes my head hurt...

Nov 15 '05 #2

P: n/a
"Eric Newton" <er**@cc.ensoft-software.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
If you start out right then you'll save yourself a lot of headache.

when you're building up the sql, fix it right then by escaping the single
quotes with DOUBLED single quotes, dont wait til afterwards to try to figure out whats going on.
Generally I agree - but the dev's on the project I'm doing this for has a
history of failing to do just that and my thinking is, if we could remove
the need at a lower level then when someone who's less dilligent than they
should be writes sql which will blow up on the nested ', we can nip it in
the bud.
if you REALLY want to parse after the fact, I dont believe regex's will work well, you'll have to actually parse the text but if you're inconsistent then good luck trying to get that to work either...
Thats my fear - especially considering how many variations there are to the
rules when you consider the syntax of insert clauses vs. the syntax of where
clauses...
The key is to fix it at the start, before it even has a chance to become a
problem, OR use named parameters:
cmd.CommandText = "UPDATE Table Set FIELD1=@Field1 WHERE FieldID=@FieldID"
cmd.Parameters.Add("@Field1",DbType.VarChar,50).Va lue = "Eric's sample"
cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1


I forgot to mention the back end is a MySQL database, didn't I? I wonder if
named parms will work in that case. :)

Anyway if it's really not feasible I'll make do - but I'm hoping SOMEONE out
there is really gifted with these cryptic (to me) regexs and is intrigued by
the challenge. :)
Nov 15 '05 #3

P: n/a
Well it turns out that the parameterized query does work with MySQL,
shockingly enough.

I suppose either the CLR or the ODBC provider is doing the
translation...cause I don't think MySQL natively supports it but in any
event, unless someone has a magic RegEx to meet the need, I'm going to
change my DAL to support parameterized sql and tell the other devs to write
their queries that way. :)

Thanks for the nudge Eric - I'd never have tested them if you hadn't brought
it up.

"Chuck Haeberle" <ch******@contractor.nocienaspam.com> wrote in message
news:e7**************@tk2msftngp13.phx.gbl...
"Eric Newton" <er**@cc.ensoft-software.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
If you start out right then you'll save yourself a lot of headache.

when you're building up the sql, fix it right then by escaping the single quotes with DOUBLED single quotes, dont wait til afterwards to try to figure
out whats going on.


Generally I agree - but the dev's on the project I'm doing this for has a
history of failing to do just that and my thinking is, if we could remove
the need at a lower level then when someone who's less dilligent than they
should be writes sql which will blow up on the nested ', we can nip it in
the bud.
if you REALLY want to parse after the fact, I dont believe regex's will

work
well, you'll have to actually parse the text but if you're inconsistent

then
good luck trying to get that to work either...


Thats my fear - especially considering how many variations there are to

the rules when you consider the syntax of insert clauses vs. the syntax of where clauses...
The key is to fix it at the start, before it even has a chance to become a problem, OR use named parameters:
cmd.CommandText = "UPDATE Table Set FIELD1=@Field1 WHERE FieldID=@FieldID" cmd.Parameters.Add("@Field1",DbType.VarChar,50).Va lue = "Eric's sample"
cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1
I forgot to mention the back end is a MySQL database, didn't I? I wonder

if named parms will work in that case. :)

Anyway if it's really not feasible I'll make do - but I'm hoping SOMEONE out there is really gifted with these cryptic (to me) regexs and is intrigued by the challenge. :)

Nov 15 '05 #4

P: n/a
You're welcome!

Personally I prefer the named parameters because in SQL the SqlCommand
provider encourages the use of sp_executesql, which affords using a cached
execution plan, basically almost as good as writing a stored procedure!

Glad to be of help

--
Eric Newton
C#/ASP Application Developer
http://ensoft-software.com/
er**@cc.ensoft-software.com [remove the first "CC."]

"Chuck Haeberle" <ch******@contractor.nocienaspam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Well it turns out that the parameterized query does work with MySQL,
shockingly enough.

I suppose either the CLR or the ODBC provider is doing the
translation...cause I don't think MySQL natively supports it but in any
event, unless someone has a magic RegEx to meet the need, I'm going to
change my DAL to support parameterized sql and tell the other devs to write their queries that way. :)

Thanks for the nudge Eric - I'd never have tested them if you hadn't brought it up.

"Chuck Haeberle" <ch******@contractor.nocienaspam.com> wrote in message
news:e7**************@tk2msftngp13.phx.gbl...
"Eric Newton" <er**@cc.ensoft-software.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
If you start out right then you'll save yourself a lot of headache.

when you're building up the sql, fix it right then by escaping the single quotes with DOUBLED single quotes, dont wait til afterwards to try to figure
out whats going on.


Generally I agree - but the dev's on the project I'm doing this for has a
history of failing to do just that and my thinking is, if we could remove the need at a lower level then when someone who's less dilligent than they should be writes sql which will blow up on the nested ', we can nip it in the bud.
if you REALLY want to parse after the fact, I dont believe regex's will
work
well, you'll have to actually parse the text but if you're
inconsistent then
good luck trying to get that to work either...


Thats my fear - especially considering how many variations there are to

the
rules when you consider the syntax of insert clauses vs. the syntax of

where
clauses...
The key is to fix it at the start, before it even has a chance to

become a problem, OR use named parameters:
cmd.CommandText = "UPDATE Table Set FIELD1=@Field1 WHERE FieldID=@FieldID" cmd.Parameters.Add("@Field1",DbType.VarChar,50).Va lue = "Eric's
sample" cmd.Parameters.Add("@FieldID",DbType.Int).Value = 1


I forgot to mention the back end is a MySQL database, didn't I? I

wonder if
named parms will work in that case. :)

Anyway if it's really not feasible I'll make do - but I'm hoping SOMEONE out
there is really gifted with these cryptic (to me) regexs and is

intrigued by
the challenge. :)


Nov 15 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.