Sounds a bit strange, however, if we could put some calculation in
stored procedure it would be quite convenient, just... where can I find
a REGEXP library for matching checking? thanks.
yours,
athos"athos" <athos.liu@.gmail.com> wrote in message
news:1130877928.278288.62290@.g44g2000cwa.googlegro ups.com...
> Hi guys,
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.
> yours,
> athos
Take a look at the LIKE topic in Books Online to see if it meets your
requirements. Not regex but it does support some simple pattern matching.
--
David Portas
SQL Server MVP
--|||LIKE is not powerful enough. btw, COM is prohibited. thanks.|||athos (athos.liu@.gmail.com) writes:
> Sounds a bit strange, however, if we could put some calculation in
> stored procedure it would be quite convenient, just... where can I find
> a REGEXP library for matching checking? thanks.
It does not sound strange at all. Some DB Engines have SIMILAR TO, and
this might even be in ANSI. I believe this uses some form of regexps.
I've been longing for it myself at times.
But for SQL2000 there is only LIKE which is far from whole covering.
You can use patindex or charindex for some stuff, but in essence it's
all very primitive.
In SQL 2005, there is no better support in T-SQL, but you can call a CLR
routine that uses the RegEx classes in .Net.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
> routine that uses the RegEx classes in .Net.
I guess for SQL 2000, you could use a non-COM library as an "extended
procedure"?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> In SQL 2005, there is no better support in T-SQL, but you can call a CLR
>> routine that uses the RegEx classes in .Net.
> I guess for SQL 2000, you could use a non-COM library as an "extended
> procedure"?
But performance would be awful and the code would be messy.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> >> In SQL 2005, there is no better support in T-SQL, but you can call a
CLR
> >> routine that uses the RegEx classes in .Net.
> > I guess for SQL 2000, you could use a non-COM library as an "extended
> > procedure"?
> But performance would be awful and the code would be messy.
I've never written any extended procedures, so perhaps you could
explain why this would give awful performance?
I imagine the call could be as:
select ...
from ...
where myregexp_match(mycolumn, myexpression, myvalue)
Why would this be any slower than COM or .NET? Isn't this partly
what extended procedures were meant for?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:11mhdiekuhe26e9@.corp.supernews.com...
> > >> In SQL 2005, there is no better support in T-SQL, but you can call a
> CLR
> > >> routine that uses the RegEx classes in .Net.
> > > > I guess for SQL 2000, you could use a non-COM library as an "extended
> > > procedure"?
> > But performance would be awful and the code would be messy.
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)
> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?
I'm guessing the main reason is that in SQL 2000, it executes outside of SQL
Server, which means for every call there's delay as it has to call out of
its address space. SQL 2005 CLR code executes within the same memory space
as SQL Server.
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
> I've never written any extended procedures, so perhaps you could
> explain why this would give awful performance?
> I imagine the call could be as:
> select ...
> from ...
> where myregexp_match(mycolumn, myexpression, myvalue)
That's not really how you call extended stored procedure. But you could
encapsulate the XP in a user-defined function to get this syntax. However,
there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
(this overhead has been reduced in SQL 2005). If you then add a call to
extended stored procedure that gives you context switches and all, it's
getting really bad.
Then add to this that if you have a bug in your XP that causes an
access violation or similar, it's not only the XP that crashes. You
blow away the entire SQL Server.
> Why would this be any slower than COM or .NET? Isn't this partly
> what extended procedures were meant for?
The CLR stuff in SQL 2005 is a lot more integrated in SQL Server and there
is far less overhead for invoking CLR. In fact, say that you have a decently
complex operation like some string manipulation that you can perform in
T-SQL, it is very likely to perform better in a CLR UDF. (But if you
start do data access from the CLR, it's a different picture.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > I've never written any extended procedures, so perhaps you could
> > explain why this would give awful performance?
> > I imagine the call could be as:
> > select ...
> > from ...
> > where myregexp_match(mycolumn, myexpression, myvalue)
> That's not really how you call extended stored procedure. But you could
> encapsulate the XP in a user-defined function to get this syntax. However,
> there is a big overhead for calling a UDF in a WHERE clause in SQL 2000
> (this overhead has been reduced in SQL 2005). If you then add a call to
> extended stored procedure that gives you context switches and all, it's
> getting really bad.
Then when are XPs actually useful?
> Then add to this that if you have a bug in your XP that causes an
> access violation or similar, it's not only the XP that crashes. You
> blow away the entire SQL Server.
I understand this part, seems to be the case with pretty much all
extending to DB engines (unless managed or Java or whatever).|||Martijn Tonies (m.tonies@.upscene-removethis.nospam.com) writes:
>> That's not really how you call extended stored procedure. But you could
>> encapsulate the XP in a user-defined function to get this syntax.
>> However, there is a big overhead for calling a UDF in a WHERE clause in
>> SQL 2000 (this overhead has been reduced in SQL 2005). If you then add
>> a call to extended stored procedure that gives you context switches and
>> all, it's getting really bad.
> Then when are XPs actually useful?
When the stuff you want to do with them are not used to evaluate queries.
For instance, we have an extended stored procedure that performs a loopback
and writes messages to a log table when an error is detected. (The point
with the loopback is that we want the log records to persist even if there
is a rollback.)
Another possible application is some sort of signaling, to inform some
external process "Hey, I've just inserted 10000 rows, you might be
interested in those".
But it is correct that XP:s, as well as sp_OAcreate & co for calling
OLE objects, have limited use, and something you only use for special
cases.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> But it is correct that XP:s, as well as sp_OAcreate & co for calling
> OLE objects, have limited use, and something you only use for special
> cases.
Thanks for the explanation.
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
No comments:
Post a Comment