473,320 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Is there any REGEXP library for TSQL?

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

Nov 1 '05 #1
11 4443
"athos" <at*******@gmail.com> wrote in message
news:11*********************@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
--
Nov 1 '05 #2
LIKE is not powerful enough. btw, COM is prohibited. thanks.

Nov 1 '05 #3
athos (at*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 1 '05 #4
> 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
Nov 2 '05 #5
Martijn Tonies (m.******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 2 '05 #6
> >> 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
Nov 2 '05 #7

"Martijn Tonies" <m.******@upscene-removethis.nospam.com> wrote in message
news:11*************@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

Nov 2 '05 #8
Martijn Tonies (m.******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 2 '05 #9
> > 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).

Nov 3 '05 #10
Martijn Tonies (m.******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Nov 3 '05 #11
> 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
Nov 3 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Grant Ord | last post by:
On Windows 2000 Inetinfo.exe verson 4.02.0622 GetScriptEngineInfo= VBScript Version 5.6.8515 In an ASP page I get the following error Error Type: (0x8002801D) Library not registered....
2
by: Steve | last post by:
Hi; I'm brand spanking new to sqlserver ( nice so far ). I need to make a simple data change across a list of tables. Basically replace an old date with a new date. However, the people I am...
2
by: Steve | last post by:
Hi; I have been writing a lot of short tsql scripts to fix a lot of tiny database issues. I was wondering if a could make an array of strings in tsql that I could process in a loop, something...
18
by: mountain man | last post by:
Greetings to all database professionals and laymen, Let us make a bold assumption that we have developed a software tool for the SQL Server environment which simply acts as an interface between...
2
by: dynoweb | last post by:
I have several *.sql files with schema/data changes to be applied to our current database. Is there a way to create a TSQL script that could be run from the SQL Query Analyzer that would...
4
by: McKirahan | last post by:
How would I use a regular expression to remove all trailing Carriage Returns and Line Feeds (%0D%0A) from a textarea's value? Thanks in advance. Also, are they any great references for learning...
6
by: Mark Findlay | last post by:
I am trying to figure out how to set up my reg exp search so that the search will only match on the exact word. Here is the current problem code: Word1 = "RealPlayer.exe" Word2 = "Player.exe"...
9
by: =?ISO-8859-1?Q?BJ=F6rn_Lindqvist?= | last post by:
With regexps you can search for strings matching it. For example, given the regexp: "foobar\d\d\d". "foobar123" would match. I want to do the reverse, from a regexp generate all strings that could...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.