It looks like the condition you're looking for is to split lines on
semicolons not contained in ' '. Perhaps you could do this with a
regular expression, but it might be simpler to write some parsing
code to do it. The following was tested on your example, and
appeared to work.
Charles Yeomans
Function SplitSQL(input as String) As String()
dim characters() as String = Split(input, "")
dim sqlStatements(-1) as String
dim lineStart as Integer = 0
dim isQuoted as Boolean = false
dim buffer(-1) as String
const delimiter = ";"
const quote = "'"
dim linebreaks as String = Encodings.UTF8.Chr(10) +
Encodings.UTF8.Chr(13)
for i as Integer = 0 to UBound(characters)
if InStr(linebreaks, characters(i)) > 0 then
continue
end if
If characters(i) = delimiter then
if not IsQuoted then
sqlStatements.Append Join(buffer, "")
redim buffer(-1)
else
buffer.Append characters(i)
end if
else
if characters(i) = quote then
isQuoted = not isQuoted
end if
buffer.Append characters(i)
end if
next
if UBound(buffer) > -1 then
sqlStatements.Append Join(buffer, "")
end if
return sqlStatements
End Function
On Mar 28, 2007, at 7:25 PM, Dr Gerard Hammond wrote:
> Hi,
>
> I have a long file full of INSERT, UPDATE and SELECT statements.
> Does anybody have a piece of RegEx code that'll split the lines
> reliably into individual SQL statements.
> RegEx and I don't normally get on.
>
> Files typically look like this.
>
> delete from Attribute;
> delete from CodeSet;
> INSERT INTO Attribute (
> AttributeName,TableName,AttributeDisplayName,Type,ImageID,ShortHelp,Lo
> ngHelp
> ) VALUES ( 'UserID','Users','User ID','integer','0','User''s unique
> numerical User ID','This is the User''s unique numerical User ID. It
> is used through out the system.' );
> INSERT INTO Attribute (
> AttributeName,TableName,AttributeDisplayName,Type,ImageID,ShortHelp,Lo
> ngHelp
> ) VALUES ( 'UserName','Users','User Name','varchar','0','User''s
> login name','This is the User''s unique login name. It is used
> through out the system.' );
> INSERT INTO Attribute (
> AttributeName,TableName,AttributeDisplayName,Type,ImageID,ShortHelp,Lo
> ngHelp
> ) VALUES ( 'ShowWizardDialogs','Users','Show Wizard
> Dialogs','integer','0','Show Wizard Dialogs when creating new
> SOPs','A flag that determines if the System should show the Wizard
> Dialogs when creating new SOPs' );
> INSERT INTO CodeSet ( DisplayOrder,
> CodeValue,CodeDescription,CodeSetID, isVisible , CodeShortHelp)
> VALUES ( NULL, '8','Engineering','ControlsID','1', 'Engineering
> Controls
> 1. DESIGN. Try to ensure that hazards are -designed out- when
> new materials, equipment and work systems are being planned for the
> workplace.
> 2. REMOVE the hazard or SUBSTITUTE less hazardous materials,
> equipment or substances.
> 3. ADOPT A SAFER PROCESS. Alterations to tools, equipment or
> work systems can often make them much safer.
> 4. ENCLOSE OR ISOLATE THE HAZARD through the use of guards or
> remote handling techniques.
> 5. PROVIDE EFFECTIVE VENTILATION through local or general
> exhaust ventilation systems. Administrative Controls
> 6. ESTABLISH appropriate ADMINISTRATIVE PROCEDURES such as:
> job rotation to reduce exposure or boredom, or timing the
> job so that fewer workers are exposed routine maintenance and
> housekeeping procedures training on hazards and correct work
> procedures');
> INSERT INTO CodeSet ( DisplayOrder,
> CodeValue,CodeDescription,CodeSetID, isVisible ) VALUES ( NULL,
> '16','Safe Work Practices','ControlsID','0' );
>
>
> --
>
> Cheers,
>
> Dr Gerard Hammond
> MacSOS Solutions
> http://www.macsos.com.au
> _______________________________________________
> Unsubscribe or switch delivery mode:
> <http://www.realsoftware.com/support/listmanager/>
>
> Search the archives:
> <http://support.realsoftware.com/listarchives/lists.html>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
|