(RegExp in Access):VBA (RegExp in SQLServer):?

[Darryl Kerkeslager]

Currently I am using the RegExp object to parse a large dataset in an Access
table - but this table was exported from SQL Server, and the very correct
question was asked - why not just do it in SQL Server.

What would be the best way to convert the VBA code I use in Access to SQL
Server - being only marginally familiar with T-SQL syntax and not at all
familiar with what can or cannot be done?
--
Darryl Kerkeslager

[Tom van Stiphout]

On Fri, 8 Dec 2006 17:05:36 -0500, "Darryl Kerkeslager"
<ke*********@comcast.netwrote:

RegEx is not inherently supported in T-SQL.
In the larger picture, parsing is a middle-tier activity, and does not
really belong in the data layer.

-Tom.

>Currently I am using the RegExp object to parse a large dataset in an Access
table - but this table was exported from SQL Server, and the very correct
question was asked - why not just do it in SQL Server.

What would be the best way to convert the VBA code I use in Access to SQL
Server - being only marginally familiar with T-SQL syntax and not at all
familiar with what can or cannot be done?

[Lyle Fairfield]

"Darryl Kerkeslager" <ke*********@comcast.netwrote in
news:FP******************************@comcast.com:

Currently I am using the RegExp object to parse a large dataset in an
Access table - but this table was exported from SQL Server, and the
very correct question was asked - why not just do it in SQL Server.

If it's done on SQL Server and multiple users are using it, or any
function, procedure whatever on SQL Server, then RegExp will share CPU
cycles, memory and bus with the demands of all those users on one
machine, the server machine.

If it's done locally then RegExp uses the client machine's CPU which is
very likely to have many fewer demands than the server machine, and
often is idle while the server is struggling.

The notion that we should "do" things on the server by preference is
counter-productive. We should "do" things on the server when the server
has special capabilities the local machine does not, and, more often,
when those things will reduce significantly the amount of data that
flows across our connection with the server.

--
Lyle Fairfield

http://www.ffdba.com/toyota/BurlingtonToyotaLease.htm

(just a sad story - read if bored)

[Darryl Kerkeslager]

"Tom van Stiphout" <no*************@cox.netwrote

RegEx is not inherently supported in T-SQL.

Is there any type of regular expression parsing that can be used?

In the larger picture, parsing is a middle-tier activity, and does not
really belong in the data layer.

Perhaps I should have framed my question differently. What I am trying to
accomplish is a one-time data cleanup, some 8 months hence. Heck, it may
take that long to perfect the code. I am testing the process by getting an
export of 49K distinct rows from SQL Server -Excel ->Access -Excel, just
so that the spreadsheet can serve as a lookup table for the conversion
process. My process will run a day before the conversion, to provide the
lookup of Distinct value; conversion will actually use the lookup table on
3M+ rows.

Obviously, this is a kluge which would be far better if the data lookup
table could just be created in SQL Server - but I'm not the SQL Server
person, I'm just writing the regular expressions. So the question is, how
would the SQL Server person run my VBA code (or convert it to another
runnable format, like T-SQL) so that all this exporting to Access could be
bypassed?
--
Darryl Kerkeslager

[Darryl Kerkeslager]

Lyle,

See my reply to Tom. I think I did not explain my issue clearly, and I have
attempted to do so again. Any help greatly appreciated.

--
Darryl Kerkeslager

[Tom van Stiphout]

On Sun, 10 Dec 2006 18:28:55 -0500, "Darryl Kerkeslager"
<ke*********@comcast.netwrote:

Good luck putting 3M rows in Excel. It's still not clear to me what
you want.
Since this is a one-time shot, I would likely use Access to link to
the SQL Server table, and then run RegEx from VBA.
If you MUST run the procedure on SQL Server, you may need version
2005, and write a DotNet assembly which is callable from an sproc.

-Tom.

>"Tom van Stiphout" <no*************@cox.netwrote

>RegEx is not inherently supported in T-SQL.

Is there any type of regular expression parsing that can be used?

>In the larger picture, parsing is a middle-tier activity, and does not
really belong in the data layer.

Perhaps I should have framed my question differently. What I am trying to
accomplish is a one-time data cleanup, some 8 months hence. Heck, it may
take that long to perfect the code. I am testing the process by getting an
export of 49K distinct rows from SQL Server -Excel ->Access -Excel, just
so that the spreadsheet can serve as a lookup table for the conversion
process. My process will run a day before the conversion, to provide the
lookup of Distinct value; conversion will actually use the lookup table on
3M+ rows.

Obviously, this is a kluge which would be far better if the data lookup
table could just be created in SQL Server - but I'm not the SQL Server
person, I'm just writing the regular expressions. So the question is, how
would the SQL Server person run my VBA code (or convert it to another
runnable format, like T-SQL) so that all this exporting to Access could be
bypassed?

[Lyle Fairfield]

"Darryl Kerkeslager" <ke*********@comcast.netwrote in
news:9r******************************@comcast.com:

"Tom van Stiphout" <no*************@cox.netwrote

>RegEx is not inherently supported in T-SQL.

Is there any type of regular expression parsing that can be used?

>In the larger picture, parsing is a middle-tier activity, and does
not really belong in the data layer.

Perhaps I should have framed my question differently. What I am
trying to accomplish is a one-time data cleanup, some 8 months hence.
Heck, it may take that long to perfect the code. I am testing the
process by getting an export of 49K distinct rows from SQL Server ->
Excel ->Access -Excel, just so that the spreadsheet can serve as a
lookup table for the conversion process. My process will run a day
before the conversion, to provide the lookup of Distinct value;
conversion will actually use the lookup table on 3M+ rows.

Obviously, this is a kluge which would be far better if the data
lookup table could just be created in SQL Server - but I'm not the SQL
Server person, I'm just writing the regular expressions. So the
question is, how would the SQL Server person run my VBA code (or
convert it to another runnable format, like T-SQL) so that all this
exporting to Access could be bypassed?

SQl 2000 and later can use User Defined Functions written within the T-SQL
framework. They look and behave very much like VBA functions. At the bottom
of this message is the first one I wrote; this may explain its ugliness.
Regardless it works and can be used just (almost just; it must be
preferenced by its owner, that is something like SELECT dbo.ProperCase
(Name) FROM ...,) the same as one can use an Access/VBA UDF in Jet in an
Access application.

SQL 2005 can host Net 2.0 Common Language Runtime.

http://msdn.microsoft.com/library/de...l=/library/en-
us/dnsql90/html/sqlclrguidance.asp

This means that it can use Regular Expressions.

http://blogs.msdn.com/sqlclr/archive.../29/regex.aspx

I would use these two things together to accomplish what you want to
accomplish.

------

CREATE FUNCTION [dbo].[ProperCase]
(
@VarString varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @NewString varchar(8000)
DECLARE @Length int
DECLARE @Position int
DECLARE @CharAtPosition varchar(1)
DECLARE @ASCIIOfChar tinyint
DECLARE @WordStart bit

SET @NewString = ''
SET @Length = LEN (@VarString)
SET @Position = 1
SET @WordStart = 1

WHILE (@Position <= @Length)
BEGIN
SET @CharAtPosition = LOWER(SUBSTRING (@VarString, @Position, 1))
IF (@WordStart = 1)
BEGIN
SET @CharAtPosition = UPPER (@CharAtPosition)
END

SET @ASCIIOfChar = ASCII(@CharAtPosition)
IF ((@ASCIIOfChar>64 AND @ASCIIOfChar<92) OR (@ASCIIOfChar>96 AND
@ASCIIOfChar<123))
SET @WordStart = 0
ELSE
SET @WordStart = 1

SET @NewString = @NewString + @CharAtPosition

SET @Position = @Position + 1
END

RETURN @NewString
END

--
Lyle Fairfield

http://www.ffdba.com/toyota/BurlingtonToyotaLease.htm

(just a sad story - read if bored)

[Darryl Kerkeslager]

"Tom van Stiphout" <no*************@cox.netwrote

Good luck putting 3M rows in Excel. It's still not clear to me what
you want.

No, just the 49K rows.

If you MUST run the procedure on SQL Server, you may need version
2005, and write a DotNet assembly which is callable from an sproc.

Not an option, but I now have an option that I can eliminate, so thanks for
mentioning it.

Since this is a one-time shot, I would likely use Access to link to
the SQL Server table, and then run RegEx from VBA.

Okay, I guess that is an option. My only one, so far.
--
Darryl Kerkeslager

[Darryl Kerkeslager]

"Lyle Fairfield" <ly***********@aim.comwrote

SQl 2000 and later can use User Defined Functions written within the T-SQL
framework. They look and behave very much like VBA functions. At the
bottom
of this message is the first one I wrote; this may explain its ugliness.

Thanks, Lyle. At least it is an option to look at and study. Plenty of
time, really, but I needed some direction to even begin looking.
--
Darryl Kerkeslager