Jump to content
VIP

[Solved] StringRegExp to get string in SQL file

Recommended Posts

VIP

Hi,

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

#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 by Trong

Regards,
 

Share this post


Link to post
Share on other sites
mikell

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

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • Deye
      By Deye
      Hi,
      I want to add any needed conditions to the StringRegExp command so it can pull out only  "File.au3", "WinAPIFiles.au3", "Test.bmp" into the array
      #include <FileConstants.au3> #include <MsgBoxConstants.au3> #include 'WinAPIFiles.au3' #include "File.au3" ; Script Start - Add your code below here Local $bFileInstall = False ; Change to True and ammend the file paths accordingly. ; This will install the file C:\Test.bmp to the script location. If $bFileInstall Then FileInstall("C:\Test.bmp", @ScriptDir & "\Test.bmp") $sFile = FileRead(@ScriptFullPath) $aResults = StringRegExp($sFile, "(?i)(FileInstall\s*|include\s*)(.*)", 3) _ArrayDisplay($aResults) Thanks In Advance
      Deye
    • FroVN
      By FroVN
      i have a text : <Name>Jonh</Name>.<Age>15</Age>
      how i can get Jonh and 15 in one stringregexp? pls give me example
    • WoodGrain
      By WoodGrain
      Hi All,
      I'd like to replace 'COMMA' with ',' for example:
      $myString = "COMMA" StringRegExpReplace($myString, 'COMMA', ',') Now I've tried escaping the ',' in various ways unsuccessfully, such as:
      '[,]'
      "[,]"
      '\,'
      [,] seems to work in the pattern, I just can't figure out how to use it in the replace, and it seems everyone online is only interested in removing/replacing commas lol.
      I also tried creating and using a variable as the replacement but also didn't work:
      $myComma = "," $myString = "COMMA" StringRegExpReplace($myString, 'COMMA', $myComma) I'm sure it's super simple if someone could point me in the right direction - thanks.
    • therks
      By therks
      I'm looking for a regex genius, cus I'm stumped when it comes to assertions.
      So what I have now, is this regular expression: ([^|=]+)=([^|]+)
      It takes a string (user input) of keys=values separated by pipes (ie: "param=value|param=value") and splits them into an array.
      Example:
      $vParamData = 'example=value|fruit=apple|phrase=Hello world' $aRegEx = StringRegExp($vParamData, '([^|=]+)=([^|]+)', 3) ; Result ; [0] => example ; [1] => value ; [2] => fruit ; [3] => apple ; [4] => phrase ; [5] => Hello world So that's working fine, but I'm wondering if there's also a way I could have this capture escaped pipes instead of splitting by them.
      ie:
      $vParamData = 'pipe test=this \| is a pipe|example=value' $aRegEx = StringRegExp($vParamData, '([^|=]+)=([^|]+)', 3) ; I'm getting this: ; [0] => pipe test ; [1] => this \ ; [2] => example ; [3] => value ; But I'd like a result like this: ; [0] => pipe test ; [1] => this \| is a pipe ; [2] => example ; [3] => value Is there some pattern that would accomplish this, or am I better off parsing it some other way?
    • rcmaehl
      By rcmaehl
      Hi all,
      I still suck at regex as always and I need some help. According to the regex tester I normally use this should be working fine but it doesn't....
      StringRegExp($sString, "\A[1-9]+[0-9]*(\-[1-9]+[0-9]*)?,*\Z") I basically want to match:
      all numbers EXCEPT 0, but including 10, 20, etc with each number separated by a comma and allowing a "-" separated range as a value For example:
      1-5,7,10-12 I've spent a couple hours modifying it but I'm not sure where I've gone wrong. Any help would be appreciated!
×