match optional special characters

Question

I have a question that has asked before in this link, but there is no right answer in the link. I have some sql query text and I want to get all function's names (the whole name, contain schema) that has created in these. my string may be like this:

 create function [SN].[FunctionName]   test1 test1 ...
 create function SN.FunctionName   test2 test2 ...
 create function functionName   test3 test3 ...

and I want to get both [SN].[FunctionName] and SN.FunctionName, I tried this regex :

create function (.*?\]\.\[.*?\])

but this returns only the first statement, how can I make those brackets optional in the regex expression?

Then, please check my answer. The one you accepted has at least 1 severe issue in the sample code, and does not allow the name without periods and square brackets. — Wiktor Stribiżew, Sep 07 '15 at 12:44
you'r right. but your answer return schema-less function too, and I dont want that(I mentioned my needed answers).in addition Taky's answer had some usefull description(although you add descriptions too , but after my check). but you helped me and I'm thanksfull. — Masoumeh Karvar, Sep 07 '15 at 12:51
So, you only want those function names that have a period in them? Ok, I see. — Wiktor Stribiżew, Sep 07 '15 at 12:53

Wiktor Stribiżew · Answer 1 · 2015-09-07T12:56:39.253

To make some subpattern optional, you need to use the ? quantifier that matches 1 or 0 occurrences of the preceding subpattern.

In your case, you can use

create[ ]function[ ](?<name>\[?[^\]\s.]*\]?\.\[?[^\]\s.]*\]?)
                              ^           ^    ^           ^

The regex matches a string starting with create function and then matching:

var rx = new Regex(@"create[ ]function[ ]
             (?<name>\[?       # optional opening square bracket
               [^\]\s.]*       # 0 or more characters other than `.`, whitespace, or `]`
               \]?             # optional closing square bracket
               \.              # a literal `.`
               \[?             # optional opening square bracket
               [^\]\s.]*       # 0 or more characters other than `.`, whitespace, or `]`
               \]?           # optional closing square bracket
             )", RegexOptions.IgnorePatternWhitespace);

See demo

score 1 · Accepted Answer · answered Sep 07 '15 at 12:10

This one works for me:

create function\s+\[?\w+\]?\.\[?\w+\]?

val regExp = "create function" + //required string literal
  "\s+" +  //allow to have several spaces before the function name
  "\[?" +  // '[' is special character, so we quote it and make it optional using - '?'
  "\w+" +  // only letters or digits for the function name
  "\]?" +  // optional close bracket
  "\." +  // require to have point, quote it with '\' because it is a special character
  "\[?" + //the same as before for the second function name
  "\w+" + 
  "\]?"

See test example: http://regexr.com/3bo0e

w.b · Answer 3 · 2015-09-07T12:19:51.017

1

You can use lookarounds:

(?<=create function )(\s*\S+\..*?)(?=\s)

Demo on regex101.com

It captures everything between create function literal followed by one or more spaces and another space assuming the matched string contains at least one dot char.

edited Sep 07 '15 at 12:19

answered Sep 07 '15 at 12:11

w.b

11,026
5
30
49

match optional special characters

3 Answers3