473,221 Members | 1,612 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,221 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 4437
"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...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.