asked on

VB.NET regular expression to deal with single quotes

Hi all,

I am looking for a regular expression to be used in VB.NET to handle single quotes in SQL statements.

Obviously, this is not the best practice way of handling SQL statements but it is to deal with legacy code.

So, below are serveral SQL statement examples:

SELECT * FROM test_table WHERE col1 = ''
SELECT * FROM test_table WHERE col1 = ' '
SELECT * FROM test_table WHERE col1 = 'Test Val'
SELECT * FROM test_table WHERE col1 = ' Test V''al'
SELECT * FROM test_table WHERE col1 = ' Test V'al'
SELECT * FROM test_table WHERE col1 = ' Te'st V'al'

Only the last 2 statement are incorrect so I need a regular expression that would leave the correct ones and adjust the two incorrect ones by doubling up the single quotes within the value text.

Any help is appreciated.

Thanks.

resourcesys.

Mazdajai

Try -

SELECT * FROM test_table WHERE col1 = ' Test V''al'
SELECT * FROM test_table WHERE col1 = ' Te''st V''al'

Open in new window

resourcesys

ASKER

Hi Mazdajai,

I understand that the SQL statements you posted are correct, however, I'm looking for a regular expression that would correct them automatically.

Thanks.

resourcesys.

PrisonBroken

Hi resourcesys,

I use these functions attached all the time to rescue errant data.

Install them and then use them like this

-- TEST TO SEE IF IT FINDS
SELECT * FROM TABLE WHERE dbo.regexFind( COLUMN, '''', 1 ) = 1;

--REPLACE THE quotes
UPDATE TABLE
SET COLUMN = (SELECT dbo.regexReplace(COLUMN, '''', '', 1, 1 ) FROM TABLE TU WHERE TU.UNIQUEID = TABLE.UNIQUEID);

Hope that helps
RegexFunctions.txt

resourcesys

ASKER

Hi PrisonBroken,

This may prove useful in the future but for the moment the solution needs to be a VB.NET regular expression.

Thanks.

resourcesys.

PrisonBroken

Why would

SELECT * FROM test_table WHERE dbo.regexFind( Col1, 'Te['''']*st V['''']*al', 1 ) = 1;

not return what you want?

resourcesys

ASKER

Hi PrisonBroken,

It would return what I want, however this would mean changing individual SQL statements.

In our code, all SQL statements are executed through a single database object, so, to avoid changing hundreds if not thousands of statements, I need a regular expression to handle it in the database object.

If I where to change the SQL statements I could just use the VB.NET string.replace method without SQL Server having to do the extra work of calling the regexFind function.

Thanks.

Brett.

PrisonBroken

I'm obviously not understanding your set up mate, good luck!

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SAMIR BHOGAYTA

hi.. this is the function for replace single quote. Try this and let me know further more.

Function MakeSQLSafe(byval MyStrData as string) as string
MyStrData = Replace(MyStrData, "'", "''")
return MyStrData
End Function

resourcesys

ASKER

Hi robert_schutt,

As you said, this isn't perfect but it's a start and the closest answer to a possible solution.

Thanks.

resourcesys