Jump to content
VIP

[Solved] StringRegExp to get string in SQL file

Recommended Posts

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

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

    • By genius257
      Inspired by PHP's preg_split.
      Split string by a regular expression.
      Also supports the same flags as the PHP equivalent.
      v1.0.1
       
      Example:
      #include "StringRegExpSplit.au3" StringRegExpSplit('splitCamelCaseWords', '(?<=\w)(?=[A-Z])') ; ['split', 'Camel', 'Case', 'Words']  
    • By jmp
      i am trying to get number from string using this code :
      #include <IE.au3> $oIE = _IEAttach ("Edu.corner") Local $aName = "Student name & Code:", $iaName = "0" Local $oTds = _IETagNameGetCollection($oIE, "td") For $oTd In $oTds If $oTd.InnerText = $aName Then $iaName = $oTd.NextElementSibling.InnerText $iGet = StringRegExpReplace($iaName, "\D", "") EndIf Next MsgBox(0, "", $iGet) it was get number like 52503058
      But, I want to get only student code 5250. (Different student have different code, sometime its 3 digits, Sometime 4)

       
    • By BlueBandana
      Is there a way to output the regex matches into a file?
      I have a script to compare two files and check for regex matches.
      I want to output the matching regex of 'testexample.txt' to another file.
      #include <MsgBoxConstants.au3> #include <Array.au3> $Read = FileReadToArray("C:\Users\admin\Documents\testexample.txt") $Dictionary = FileReadToArray("C:\Users\admin\Documents\example.txt") For $p = 0 To UBound($Dictionary) - 1 Step 1 $pattern = $Dictionary[$p] For $i = 0 To UBound($Read) - 1 Step 1 $regex = $Read[$i] If StringRegExp($regex, $pattern, 0) Then MsgBox(0, "ResultsPass", "The string is in the file, highlighted strings: " ) Else MsgBox(0, "ResultsFail", "The string isn't in the file.") EndIf Next Next  
    • By guner7
      Hello,
      I need some help to parse the Green highlighted value with from below text:
      RESISTOR  THICK FILM 4.64K ±1% 1/4W ±100PPM/°C 1206 SMT
      RESISTOR  THICK FILM 3.83K ±1% 1/4W ±100PPM/°C 1206 SMT
      RESISTOR CARBON FILM 22K ±10% 1/2W AXIAL THT
      RESISTOR  WIREWOUND  22 ±5% 3W ±30PPM/°C AXIAL THT
      RESISTOR  METAL OXIDE 4.7K ±5% 2 W ±300PPM/°C AXIAL THT
      RESISTOR  THICK FILM 0   1/8W  0805 SMT
      I am using positive look behind.:
      (?<=FILM|WOUND|OXIDE).+ Can only pull this off:
      4.64K ±1% 1/4W ±100PPM/°C 1206 SMT 3.83K ±1% 1/4W ±100PPM/°C 1206 SMT 22K ±10% 1/2W AXIAL THT  22 ±5% 3W ±30PPM/°C AXIAL THT 4.7K ±5% 2 W ±300PPM/°C AXIAL THT 0   1/8W  0805 SMT I'm trying the \b word boundary to no avail at this point. Appreciate if anyone would guide me on this?
×
×
  • Create New...