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

    • therks
      By therks
      So I have this pattern: 
      ^(?:(\d+)|(\d+):(\d+)|(\d+):(\d+):(\d+))$ And I'm expecting (depending on input) to get a 1, 2 or 3 index array (or @error for invalid input).
      But instead I get this:
      #include <Debug.au3> Func Test($String) _DebugArrayDisplay(StringRegExp($String, '^(?:(\d+)|(\d+):(\d+)|(\d+):(\d+):(\d+))$', 1)) EndFunc Test('10') ; Results (normal, expected): ; Row 0|10 Test('10:20') ; Results (extra blank index): ; Row 0| ; Row 1|10 ; Row 2|20 Test('10:20:30') ; Results (three blank indices): ; Row 0| ; Row 1| ; Row 2| ; Row 3|10 ; Row 4|20 ; Row 5|30 Is this normal? Should I just code around it, or is there a better way to do what I'm looking for?
      I also tried reversing my regex, but it was even uglier results:
      #include <Debug.au3> Func Test($String) _DebugArrayDisplay(StringRegExp($String, '^(?:(\d+):(\d+):(\d+))|(\d+):(\d+)|(\d+)$', 1)) EndFunc Test('10') ; Results (yuck): ; Row 0| ; Row 1| ; Row 2| ; Row 3| ; Row 4| ; Row 5|10 Test('10:20') ; Results (slightly better): ; Row 0| ; Row 1| ; Row 2| ; Row 3|10 ; Row 4|20 Test('10:20:30') ; Results (nice): ; Row 0|10 ; Row 1|20 ; Row 2|30  
    • milkmoron
      By milkmoron
      I am trying to search in a web browser dates XX/XX/XXXX that are also links. I want to click them after and remove them from the array. This is all I have so far. Nothing shows up. What am I doing wrong?
      ControlFocus ("Customer Center", "", "")
      Local $aArray = StringRegExp('(..)/(..)/(....)', '(..)/(..)/(....)', $STR_REGEXPARRAYFULLMATCH)
      For $i = 0 To UBound($aArray) - 1
          MsgBox($MB_SYSTEMMODAL, "RegExp Test with Option 2 - " & $i, $aArray[$i])
      Next
       
    • 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.
×