VIP

[Solved] StringRegExp to get string in SQL file

2 posts in this topic

#1 ·  Posted (edited)

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



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

1 person likes this

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

    • ISI360
      By ISI360
      Hi!

      I need a little bit help from some RegEx experts please:
      I would make my ISN AutoIt Studio faster when generating the scripttree. And what would be better to do this via regex?
      Problem is i am not really good at this regex stuff. So maybe someone could help me here.
       
      The challange is to get all Global Variables from a script via RegEx in a Array.
      Here is a example script with some tests:
      Global $Var1 = 1234 Local $Local_Var = 1234 $Ignore_me_too = 1234 Global $Var2 = 1234, $var3 = 1242 Global $ahIcons[30], $ahLabels[30] Global Const $Var4 = iniread($inivar1,"jj","jj","") , $var5= iniread($inivar2,"jj","jj","") Global $Var_String = "was" Global $Array_Test[16] = [1,15,16,0,31,15,25,15,25,30,8,30,8,15,1,15] Global Enum $MARGIN_SCRIPT_NUMBER = 0, $MARGIN_SCRIPT_ICON, $MARGIN_SCRIPT_FOLD Global Const $Delim = '\', $Delim1 = '|' Global $hard1 = "a", _ $hard2 = "b", _ $hard3 = "c"  
      The returning array should look like this:
      $Var1 $Var2 $var3 $Var4 $var5 $Var_String $Array_Test $MARGIN_SCRIPT_NUMBER $MARGIN_SCRIPT_ICON $MARGIN_SCRIPT_FOLD $Delim $Delim1 $hard1 $hard2 $hard3  
      I already made some success with a expression i found in the SciTE Jump Tool:  (\$\w+)(?:[\h\[.=+*/^,)\-])?
      This nearly returns the perfect results. But it does not check if it´s a global variable (with the const and enum options) and also returns variables in commands (for example $inivar1)
      I also found this regex: (?im:^(?=Global|Const|Enum|Static)(?:Global)?\h*(?:Const|Enum|Static)?(?:(?<=Enum)\h+Step\h+[+*-]\d+)?\h*)([^\r\n .\=]+)
      This returns also usefull results...but trying to understand this explodes my head

      Maybe someone can help me here?
      Thanks in advance!
    • TheAutomator
      By TheAutomator
      Can anyone tell me why this isn't working?..
      #include <array.au3> $regexp = StringRegExp("test 'a b c'", "'([^']|'')*'|\S+", 3) _ArrayDisplay($regexp) trying to split this "test 'a b c'  'some other '' test'' ...'" into:
      0: test
      1: 'a b c'
      2: ...
      but it gives me:
      0: test
      1: c
    • anthonyjr2
      By anthonyjr2
      Hi guys,
      I am pretty bad with regex, and am having some trouble trying to come up with an expression for a certain type of string. Basically I want to be able to tell if a string is of the format:
      AA#####A
      Where the A's are any letter from A-Z and the #'s are any digit from 0-9.
      I've been playing around with a regex tester online for a while but I can't really seem to grasp the concept very well. Could anyone give me any tips?
      This isn't exactly an AutoIt specific question which is why I didn't post it in General Help & Support.
    • Robinson1
      By Robinson1
      Well the plan is to use the power of regular expressions engine of AutoIT for patching binary data.
      Something like this: StringRegExp( $BinaryData,  "(?s)\x55\x8B.."
       
      <cut> ... Okay straight to question/problem
      ... certain bytes that are in the range from 0x80 to 0xA0 won't match.
      Hmm seem to be a char encoding problem. In detail these are 27 chars: 0x80, 0x82~8C, 0x8E, 0x91~9C, 0x9E,0x9F
      Here's a small code snippet to explore / explain this problem:
      #include "StringConstants.au3" $TestData = BinaryToString("0x7E7F808182") ;Okay $match = StringRegExp( $TestData ,'\x7E' ,$STR_REGEXPARRAYFULLMATCH) ConsoleWrite('@extended = ' & @extended & ' $match = ' & $match & @CRLF) ;Okay $match = StringRegExp( $TestData ,'\x7F' ,$STR_REGEXPARRAYFULLMATCH) ConsoleWrite('@extended = ' & @extended & ' $match = ' & $match & @CRLF) ;Error no match $match = StringRegExp( $TestData ,'\x80' ,$STR_REGEXPARRAYFULLMATCH) ConsoleWrite('@extended = ' & @extended & ' $match = ' & $match & @CRLF) ;Okay $match = StringRegExp( $TestData ,'\x81' ,$STR_REGEXPARRAYFULLMATCH) ConsoleWrite('@extended = ' & @extended & ' $match = ' & $match & @CRLF) ;Error no match $match = StringRegExp( $TestData ,'\x82' ,$STR_REGEXPARRAYFULLMATCH) ConsoleWrite('@extended = ' & @extended & ' $match = ' & $match & @CRLF) ;~ output: ;~ @extended = 2 $match = ;~ @extended = 3 $match = ;~ @extended = 0 $match = 1 ;~ @extended = 5 $match = ;~ @extended = 0 $match = 1 Hmm what to do? Go back and use the 'numberstring monster' implementation or just omit that range of 'unsafe bytes'. What is the root of this problem?
      Any idea how to fix this?
       
      Update: Okay I know a byte is not a character.
      But StringRegExp operates on String and so character level.
      Okay as long as you stay at Ansi encoding and only use /x00 - /X7F in the search pattern using  StringRegExp works well to search for binary data.
      What bytes can be matched that are in the range from /X7F - /xFF is also depending on the code page.
      So this avoid to search for bytes in the range from 0x80-0xa0 only applies to Germany.
      I just change this country setting:

      to Thai and now near all bytes from /X7F - /xFF fails to match.
    • Carm01
      By Carm01
      Hello,
      I have spent the past day fooling with StringRegExp to no avail attempting to get what would be a simple solution to an issue using StringRegExp.
      I will post the code in a sec. The string 'Java x Update y' where x and y are numeric values ONLY if a letter is mixed in anywhere then it should fail. I have been able to successfully deal with the x value so if x = 1234 or a1234 or 1a234 or 1234a would result in a fail if 'a' was in the string. However, when y = 1a234 then I get an output of 1 and when y = 1234a then the output = 1234 when both should fail. I am probably overlooking something simple and in looking through all the material and experimenting I am unable to figure it out and my experience with stringregexp and trying to find examples of this proved difficult. If someone could assist or point me to a thread ? Here is my code ; prob a simple fix. I am also trying to avoid white spaces.
      Thanks in advance
      #include <array.au3> $aArray = StringRegExp('Java 3009 Update 1a21', '(?i)Java (\d+) Update (\d+)', $STR_REGEXPARRAYGLOBALMATCH) If @error Then Exit _ArrayDisplay($aArray)