1

I want a regular expression that can uniquely identify the user defined function in sql queries.

For example:

with someExp(parameter)
as
(
select parameter = convert(varchar(8000),'welcome')
union all
select parameter + 'user' from someExp where lengthOfPara(parameter) < 100
)
select parameter from someExp
order by parameter

Regular expression should uniquely identify both the function convert and lengthOfPara.

Thanks in advance.

user628083
  • 353
  • 1
  • 5
  • 15
  • I think that's a bit too much too ask from a regex. Also, in most SQL flavours that I'm familiar with, `convert` isn't a user-defined function (it's a built-in one). – NPE Sep 15 '11 at 11:11
  • You may want to consider an SQL parser like those mentioned here: http://stackoverflow.com/questions/660609/sql-parser-library-for-java – Asaf Sep 15 '11 at 12:37

3 Answers3

3

This is not possible. SQL queries are not described by a regular langauge, so you cannot use a regex to isolate arbitrary constructs in this manner.

Oliver Charlesworth
  • 267,707
  • 33
  • 569
  • 680
0

Regex is not context aware, it does not know it's working on a sql query. As far as regex is concerned, it's just plain text.

And in this text there is nothing that uniquely identifies functions as separate from other constructs that consist of a word and a brackets-pair, like someExp(parameter) and varchar(8000) in your example.

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111
0

Edit: sorry, I misread the question. removing the irrelevant parts

As others have said, a regular expression won't work.

You could use a language parser (like Bison) with the full SQL grammar to detect it.

Henrik Paul
  • 66,919
  • 31
  • 85
  • 96