473,324 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Trying again: Need help with RegEx for SQL

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
4 1720
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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

22
by: Justin Koivisto | last post by:
OK, I found a thread that help out from a while back (Oct 9, 2002) to give me this pattern: `(((f|ht)tp://)((+)(+)?@)?()+(:\d+)?(\/+)?)`i OK, all is well and good with this until the URL is...
0
by: Erik Blas | last post by:
Hello everyone, Basically what I'm trying to do is write an app that will read the lines from a text file, parse through each lean with a regex (if there is a better way please point me) for a...
16
by: Andrew Baker | last post by:
I am trying to write a function which provides my users with a file filter. The filter used to work just using the VB "Like" comparision, but I can't find the equivilant in C#. I looked at...
3
by: Joe | last post by:
Hi, I have been using a regular expression that I don’t uite understand to filter the valid email address. My regular expression is as follows: <asp:RegularExpressionValidator...
2
by: Michael R. Pierotti | last post by:
Dim reg As New Regex("^\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}$") Dim m As Match = reg.Match(txtIPAddress.Text) If m.Success Then 'No need to do anything here Else MessageBox.Show("You need to enter a...
8
by: erikcw | last post by:
Hi all, I'm trying to write a regex pattern to use in preg_replace. Basically I want to put around every line (\n) in this variable. However, I need to exclude lines that already have brackets...
3
by: konrad Krupa | last post by:
This message is a continuation of my previous post "Pattern Match" Doug - Thank you for your help. Doug Semler was able to solve my problem to some point but I still need some help. Doug's...
3
XtinaS
by: XtinaS | last post by:
I'm trying to write a script for Greasemonkey that will, in LiveJournal, replace a placeholdered embedded YouTube thing with a link to the video. In LiveJournal, you can set an option to have a...
4
by: Danny Ni | last post by:
Hi, The following code snippet is causing CPU to max out on my local machine and production servers. It looks fine on Expresso though. Regex rgxVideo = new...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.