[Solved] StringRegExp to get string in SQL file

[Trong]

Hi,

I need help string RegEx to get string from CREATE to GO

expandcollapse popup

#include <StringConstants.au3>
;~ Global $fileSQL1 = @ScriptDir & "\fileSQL1.sql"
;~ Global $fileSQL2 = @ScriptDir & "\fileSQL2.sql"
Global $tmpSQLfile = @TempDir & "\tmpFile.sql"

OnAutoItExitRegister("_OnExit")

_SetTMPsql()
If Not FileExists($tmpSQLfile) Then
    OnAutoItExitUnRegister("_OnExit")
    Exit MsgBox(48, "/!\", "File: " & $tmpSQLfile & @CRLF & " is not Exists!", 3)
EndIf

Global $ContentSQLfile = FileRead($tmpSQLfile)

_Start()

Func _Start()
    Local $aArray, $iOffset = 1, $stringRegExp = '(?i)CREATE(.*?)GO'
    While 1
        $aArray = StringRegExp($ContentSQLfile, $stringRegExp, $STR_REGEXPARRAYMATCH, $iOffset)
        If @error Then
            MsgBox(48, "StringRegExp Error " & @error, "+> StringRegExp: " & $stringRegExp & @CRLF & @CRLF & "=> With STRING:" & @CRLF & @CRLF & $ContentSQLfile)
            ExitLoop
        EndIf
        $iOffset = @extended
        For $i = 0 To UBound($aArray) - 1
            MsgBox(0, "RegExp Test with Option 1 - " & $i, $aArray[$i])
        Next
    WEnd

EndFunc   ;==>_Start

Func _SetTMPsql()
    Local $tmpSQLContent = ""
    $tmpSQLContent &= "USE [Master]" & @CRLF
    $tmpSQLContent &= "GO" & @CRLF
    $tmpSQLContent &= "" & @CRLF
    $tmpSQLContent &= "CREATE DATABASE [Sales] ON  PRIMARY " & @CRLF
    $tmpSQLContent &= "( NAME = N’Sales’, FILENAME = N’\FSASQLDBSales.mdf’ , " & @CRLF
    $tmpSQLContent &= "  SIZE = 2GB , MAXSIZE = 8GB, FILEGROWTH = 1GB )" & @CRLF
    $tmpSQLContent &= "LOG ON " & @CRLF
    $tmpSQLContent &= "( NAME = N’Sales_log’, FILENAME = N’\FSASQLDBSales_log.ldf’ , " & @CRLF
    $tmpSQLContent &= "  SIZE = 1GB , MAXSIZE = 2GB , FILEGROWTH = 10%)" & @CRLF
    $tmpSQLContent &= "GO" & @CRLF
    $tmpSQLContent &= "" & @CRLF
    $tmpSQLContent &= "USE [Sales]" & @CRLF
    $tmpSQLContent &= "GO" & @CRLF
    $tmpSQLContent &= "" & @CRLF
    $tmpSQLContent &= "-- Table Product" & @CRLF
    $tmpSQLContent &= "CREATE TABLE [dbo].[Product]" & @CRLF
    $tmpSQLContent &= "(" & @CRLF
    $tmpSQLContent &= " [ProductId] [uniqueidentifier] DEFAULT NEWID() NOT NULL," & @CRLF
    $tmpSQLContent &= " [ProductName] [nchar](50) NULL," & @CRLF
    $tmpSQLContent &= " [ProductDescription] [nchar](3000) NULL," & @CRLF
    $tmpSQLContent &= " [ProductPrice] MONEY NULL" & @CRLF
    $tmpSQLContent &= ") ON [PRIMARY]" & @CRLF
    $tmpSQLContent &= "GO" & @CRLF
    $tmpSQLContent &= "" & @CRLF
    $tmpSQLContent &= "-- Table Sales" & @CRLF
    $tmpSQLContent &= "CREATE TABLE [dbo].[Sales]" & @CRLF
    $tmpSQLContent &= "(   " & @CRLF
    $tmpSQLContent &= " [SaleId] [uniqueidentifier] DEFAULT NEWID() NOT NULL," & @CRLF
    $tmpSQLContent &= " [SaleName] [nchar](50) NULL," & @CRLF
    $tmpSQLContent &= " [SaleInfo] [nchar](3000) NULL," & @CRLF
    $tmpSQLContent &= " [SaleMoney] MONEY NULL" & @CRLF
    $tmpSQLContent &= ") ON [PRIMARY]" & @CRLF
    $tmpSQLContent &= "GO" & @CRLF
    $tmpSQLContent &= "" & @CRLF
    $tmpSQLContent &= "SET ANSI_NULLS ON" & @CRLF
    $tmpSQLContent &= "GO" & @CRLF
    $tmpSQLContent &= "SET QUOTED_IDENTIFIER ON" & @CRLF
    $tmpSQLContent &= "GO" & @CRLF
    $tmpSQLContent &= "" & @CRLF
    $tmpSQLContent &= "-- The End" & @CRLF
    Local $hOpen = FileOpen($tmpSQLfile, 2 + 8 + 128)
    FileWrite($hOpen, $tmpSQLContent)
    Return FileClose($hOpen)
EndFunc   ;==>_SetTMPsql

Func _OnExit()
    Exit FileDelete($tmpSQLfile)
EndFunc   ;==>_OnExit

 

mikell

Quote

$stringRegExp = '(?is)CREATE(.*?)GO'   

 

Edited January 20, 2017 by Trong

[mikell]

$stringRegExp = '(?is)CREATE(.*?)GO'