As soon as I sent this, it occurred to me that perhaps linebreaks
inside quoted strings were not ignorable whitespace; here is a
version of the loop that handles this.
for i as Integer = 0 to UBound(characters)
If characters(i) = delimiter then
if not IsQuoted then
sqlStatements.Append Join(buffer, "")
redim buffer(-1)
else
buffer.Append characters(i)
end if
elseif characters(i) = quote then
isQuoted = not isQuoted
buffer.Append characters(i)
else
if InStr(linebreaks, characters(i)) = 0 or isQuoted then
buffer.Append characters(i)
else
continue
end if
end if
next
On Mar 29, 2007, at 10:49 AM, Charles Yeomans wrote:
> 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,L
>> o
>> 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,L
>> o
>> 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,L
>> o
>> 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>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>
Search the archives:
<http://support.realsoftware.com/listarchives/lists.html>
