0

I'm trying to extract a string that located between 2 other strings in a file The strings are "USE" and "GO"

and this is what i came out with :

$importPath = "SOMEPATH"
$pattern = "(?<=\*\*USE\*\*)[\s\S]*(?=\*\*GO\*\*)"

Get-Content $importPath | Foreach {if ([Regex]::IsMatch($_, $pattern)) { $arr += [Regex]::Match($_, $pattern)}}

$arr

But when executing this I get nothing

Example SQL Script :

USE FIZNET
GO



DECLARE @Symbol_Type_Id SMALLINT

SELECT @Symbol_Type_Id = Symbol_Type_Id 
FROM dbo.SymbolTypes 
WHERE SymbolType = 'Indices - Asia'

UPDATE dbo.Currencies SET
Symbol_Type_Id = @Symbol_Type_Id
WHERE Currency = 'ASX'

GO

I would like the $arr to have the value "FIZNET"

Yonnatan Bar
  • 73
  • 1
  • 13
  • Do you have a sample string and expected output for comparison? Also in your example you have `$path` put use `Get-Content $importPath`. Are you sure the source file is being read and that it contains the lines that should match? – Matt Nov 17 '15 at 14:24
  • Thanks for the Edit ,it is $importPath is both places , and i verified the file is in the location , its a simple SQL Script i`m trying to extract the Database name from , the string will be from the Type : USE [DB NAME] GO And i like to extract the DB NAME so i know where to execute the script (different DB`s located in different servers) – Yonnatan Bar Nov 17 '15 at 14:29
  • You have no asterisks in your sample – Matt Nov 17 '15 at 14:37
  • Are USE and GO on the same line... do they repeat multiple times in the file.? Please include a scrubbed sample of the file. – Matt Nov 17 '15 at 15:01
  • Added Example , Thank You – Yonnatan Bar Nov 24 '15 at 13:37
  • Updated my answer with your sample and my code still works. Just the "***" you have are not required. – Matt Nov 24 '15 at 14:01

3 Answers3

1

You could use the .* regex to match all strings. For example:

$importPath = "PATHNAME"
$pattern = "USE(.*)GO"

$string = Get-Content $importPath
$result = [regex]::match($string, $pattern).Groups[1].Value
$result
Emmett Lin
  • 639
  • 4
  • 16
0

As an alternative approach, this might be of help:

$Result = 'SELECT USE SELECT * FROM MyTable GO OTHER STUFF' -split 'USE|GO' |
    Select-Object -Skip 1 -First 1

# Remove space before and after
$Result.Trim()

The output is:

SELECT * FROM MyTable
DarkLite1
  • 13,637
  • 40
  • 117
  • 214
0

There is room for improvement here since we don't have a sample file but your regex pattern appears to function for something like this. 

**USE**AdventureWorks2012**GO**

I suspect that is not how the lines look in your data file. Possibly that is split across lines? I would like to introduce you to Select-String to help mitigate this. Consider the following file of bad sql (for testing) I created from TechNet.

USE AdventureWorks2012
GO
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg is not valid after this GO ends the batch. Just because.

**USE** AdventureWorks2012;
**GO**
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'

Now, in that file we have an example that has asterisks and one that does not. Your regex is expecting ** so I have included solutions to cover both scenarios. 

# Test with one pattern and comment out the other
$pattern = "(?<=USE\*\*)([\s\S]*?)(?=\*\*GO)"
$pattern = "(?<=USE)([\s\S]*?)(?=GO)"

(Get-Content $path -Raw | Select-String -Pattern $pattern -AllMatches -CaseSensitive).Matches.Value | ForEach-Object{$_.Trim()}

Use the $pattern that actually matches your text. If it does spread across lines this will work as we read the file in as one string (Would be slow with large files). -CaseSensitive should help in case there is a comment in the text that had the word "because" for instance. Another important change is that the match was made non-greedy in case there are multiple matches in the file.

Patterns should return AdventureWorks2012; and AdventureWorks2012 respectively.

Edit with your sample

Using the following code based on my explanation above you get your expected results. 

$pattern = "(?<=USE)([\s\S]*?)(?=GO)"
$arr = (Get-Content $path -Raw | Select-String -Pattern $pattern -AllMatches -CaseSensitive).Matches.Value | ForEach-Object{$_.Trim()}
Matt
  • 45,022
  • 8
  • 78
  • 119