Need help creating RegEx expression... 1

[sqlsamurai]

These are my examples:

SELECT *
INTO #TEMP
FROM TABLE

SELECT * INTO #TEMP FROM TABLE

Could somebody please show me how to create a regex pattern that would find the bolded area in the texts above. The portion in italics, TEMP, can vary as that will be the name of the temp table.

Thanks

 

[BobRodes]

It's not clear whether you're asking if you can use Regex to find text that's bolded, or whether you're looking for an expression that will find the word "into". Can you clarify, please?

 

[sqlsamurai]

I'm sorry if i was unclear. I'm looking for an expression that will find the word INTO.

Basically i would like the pattern to find the word INTO followed by a space then a pound sign (#) then a wildcard for the temp table name then another space.

Thanks

 

[earthandfire]

Assuming that the table name is always all letters then:

[tt] "INTO #[A-Z]{1,}" [/tt]

should do what you need.

The pattern means:

Starting with INTO #
and ending with 1 or more of the characters in the range A-Z

Also experiment with the following replacements for [tt][A-Z][/tt]

[tt][A-Z\d][/tt] letters and numbers
[tt][\w][/tt] (note this is a lowercase "w") word characters (i.e. letter, numbers and underscore)


Hope this helps.

Hope this helps

 

[earthandfire]

On reflection I think this may be a better pattern:

[tt](?<=\W?INTO #)[\w]{1,}[/tt]

This means:
Look for (but don't include in the result)
0 or more Non-Word characters followed by INTO #
Look for and return a string of 1 or more consecutive word characters

This will therefore return just the temp table's name (without the leading #) and is a bit more flexible than the sugggestion I posted last night.

As an example:

  Private rxPattern As String = "(?<=\W?INTO #)[\w]{1,}"

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim rx As New System.Text.RegularExpressions.Regex(rxPattern)
    Dim mc As System.Text.RegularExpressions.MatchCollection

    mc = rx.Matches(textboxTestString.Text)
    For Each m As System.Text.RegularExpressions.Match In mc
      MessageBox.Show(m.ToString)
    Next

  End Sub

Hope this helps.

 

[MacLeod72]

I don't know if you need the table name, but if you do you can use the following:

Dim re As System.Text.RegularExpressions.Regex
Dim s1 As String = "SELECT * INTO #tbl FROM TABLE WHERE X = 5"
Dim var As String

var = re.Replace(s1, "^.*INTO #([^ ]*).*$", "$1")
MsgBox(var) 'Displays "tbl"

This will return all non-space characters after the pound sign.

--Rob