Tuesday, July 31, 2007

SQL Server 2005 - Technical Articles

SQL Server 2005

Accessing External Data Sources with Analysis Services 2005
Add Custom Data Mining Algorithms to SQL Server 2005
Analysis Services 2005 Processing Architecture
Building a Localized Report on a SQL Server 2005 Analysis Service Cube Data Source
Building a Rule Engine with SQL Server
Building Ad-hoc Reporting Solutions with SQL Server 2005 Report Builder and Analysis Services OLAP
Building Reliable, Asynchronous Database Applications Using Service Broker
Connectivity and SQL Server 2005 Integration Services
Data Access Tracing in SQL Server 2005
Data Integration Solutions for Master Data Management
Data Quality Solutions
Data Transformation Services for SQL Server 2005 Frequently Asked Design Questions
Deployments and Tests in an iSCSI SAN
Designing and Delivering Rich Office Reports with SQL Server Reporting Services 2005 and SoftArtisans OfficeWriter
Developing Report Navigation and Drilldown
Editing Transact-SQL Code in SQL Server 2005 Beta 2
Enabling Drillthrough in Analysis Services 2005
Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions
Fuzzy Lookup and Fuzzy Grouping in Data Transformation Services for SQL Server 2005
Get More Out of SQL Server Reporting Services Charts
Handling Data Integrity Issues in Analysis Services 2005
How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005
Integrating Reporting Services into Your Application
International Features in Microsoft SQL Server 2005
Introduction to MDX Scripting in Microsoft SQL Server 2005
Introduction to SQL Server 2005 Data Mining
An Introduction to SQL Server Service Broker
Introduction to the Unified Dimensional Model (UDM)
Introduction to XQuery in SQL Server 2005
Managed Data Access Inside SQL Server with ADO.NET and SQLCLR
Many-to-Many Dimensions in Analysis Services 2005
Microsoft Jet 4.0 Sorting APIs: DBCompareStringW and DBLCMapStringW
Migrating from Business Objects Crystal Reports to SQL Server 2005 Reporting Services
Monitoring Report Execution Performance with Execution Logs
Multi-User Considerations in Data Synchronization for SQL Server 2005 Mobile Edition 3.0
Multiple Active Result Sets (MARS) in SQL Server 2005
Optimizing Microsoft Office Access Applications Linked to SQL Server
Overview of Native XML Web Services for Microsoft SQL Server 2005
An Overview of SQL Server 2005 for the Database Developer
Overview of the Analysis Services Development and Management Environments
Partitioned Tables and Indexes in SQL Server 2005
Performance Optimizations for the XML Data Type in SQL Server 2005
Processing XML Showplans Using SQLCLR in SQL Server 2005
Reducing Database Size by Using Vardecimal Storage Format
Report Design Tips and Tricks
Reporting Services: Using XML and Web Service Data Sources
SQL Server 2005 Beta 2 Transact-SQL Enhancements
SQL Server 2005 Full-Text Search: Internals and Enhancements
SQL Server 2005 Row Versioning-Based Transaction Isolation
SQL Server Data Mining: Plug-In Algorithms
SQL Server Data Mining Programmability
SQL Server Optimization
A Technical Comparison of Replication and Remote Data Access Features in SQL Server 2005 Mobile Edition 3.0
Trace and Replay Objects: A New API for SQL Server Tracing and Replay
A Tutorial for Constructing a Plug-in Algorithm
A Tutorial for Constructing a Plug-In Viewer
Usage Scenarios for SQL Server 2005 Native Web Services
Using CLR Integration in SQL Server 2005
Using SQL Server 2005 Reporting Services with SAP NetWeaver Business Intelligence
Using Table Valued Functions in SQL Server 2005 to Implement a Spatial Data Library
Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server
What's New in FOR XML in Microsoft SQL Server 2005
XML Best Practices for Microsoft SQL Server 2005
XML Indexes in SQL Server 2005
XML Options in Microsoft SQL Server 2005
XML Support in Microsoft SQL Server 2005

SQL 2005 Symmetric Encryption

read more at: SQL 2005 Symmetric Encryption

Selective Defrag/Reindex and Log

SQLServerCentral.com Script Library: Selective Defrag/Reindex and Log

USE master
GO

----------------------------------------------------------
-- This creates a table to log all activity and results --
----------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fragreport]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[fragreport]
GO

CREATE TABLE [dbo].[fragreport] (
[fid] [int] IDENTITY (1, 1) NOT NULL ,
[timestamp] [datetime] NULL ,
[ObjectName] [sysname] NOT NULL ,
[ObjectId] [int] NULL ,
[IndexName] [sysname] NOT NULL ,
[IndexId] [int] NULL ,
[Level] [int] NULL ,
[Pages] [int] NULL ,
[Rows] [int] NULL ,
[MinimumRecordSize] [int] NULL ,
[MaximumRecordSize] [int] NULL ,
[AverageRecordSize] [float] NULL ,
[ForwardedRecords] [int] NULL ,
[Extents] [int] NULL ,
[ExtentSwitches] [int] NULL ,
[AverageFreeBytes] [float] NULL ,
[AveragePageDensity] [float] NULL ,
[ScanDensity] [float] NULL ,
[BestCount] [int] NULL ,
[ActualCount] [int] NULL ,
[LogicalFragmentation] [float] NULL ,
[ExtentFragmentation] [float] NULL ,
[DBName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrePost] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[fragreport] WITH NOCHECK ADD
CONSTRAINT [PK_fragreport] PRIMARY KEY CLUSTERED
(
[fid]
) ON [PRIMARY]
GO


-------------------------------------------------------
-- Run this from master and supply any database name --
-------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ap_IndexDefragAndRebuild]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ap_IndexDefragAndRebuild]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE ap_IndexDefragAndRebuild
@dbname varchar(100)
AS

SET NOCOUNT ON

CREATE TABLE #tables(
rid int identity (1,1),
tabid int,
[name] varchar(100)
)

CREATE TABLE #indexes(
rid int identity (1,1),
indid int,
[name] varchar(100)
)

CREATE TABLE #fragreport(
fid int identity (1,1),
[timestamp] datetime default getdate(),
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId int,
[Level] int,
Pages int,
[Rows] int,
MinimumRecordSize int,
MaximumRecordSize int,
AverageRecordSize float,
ForwardedRecords int,
Extents int,
ExtentSwitches int,
AverageFreeBytes float,
AveragePageDensity float,
ScanDensity float,
BestCount int,
ActualCount int,
LogicalFragmentation float,
ExtentFragmentation float,
DBName varchar(100) NULL,
PrePost varchar(20) NULL
)

CREATE TABLE #reindex(
rid int identity (1,1),
ObjectName sysname,
IndexName sysname
)

DECLARE @numtables int,
@numindexes int,
@numreindexes int,
@tabcount int,
@indcount int,
@recount int,
@currtable int,
@tabname varchar(100),
@currind int,
@indname varchar(100)


SET @tabcount = 1

INSERT INTO #tables([tabid], [name])
EXEC ('SELECT [id], ltrim(rtrim(su.[name] + ''.'' + so.[name])) FROM ' + @dbname + '.dbo.sysobjects so INNER JOIN ' + @dbname + '.dbo.sysusers su ON su.uid = so.uid WHERE so.xtype = ''U'' AND so.[name] <> ''dtproperties''')

SELECT @numtables = count(*) FROM #tables

WHILE @tabcount <= @numtables
BEGIN
SET @indcount = 1

SELECT @currtable = tabid,
@tabname = ltrim(rtrim([name]))
FROM #tables
WHERE rid = @tabcount

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH ALL_INDEXES, TABLERESULTS')

DELETE FROM #fragreport
WHERE IndexID IN (0,255)

UPDATE #fragreport
SET PrePost = 'PRE'
WHERE PrePost is NULL

INSERT #indexes([indid], [name])
EXEC ('SELECT indid, [name] FROM ' + @dbname + '.dbo.sysindexes WHERE [id] = ' + @currtable + ' AND [name] not like ''_WA%'' AND indid NOT IN (0, 255)')

SELECT @numindexes = count(*) FROM #indexes

WHILE @indcount <= @numindexes
BEGIN
SELECT @currind = indid,
@indname = ltrim(rtrim([name]))
FROM #indexes
WHERE rid = @indcount
EXEC ('DBCC INDEXDEFRAG (''' + @dbname + ''',''' + @tabname + ''',''' + @indname + ''')')
SET @indcount = @indcount + 1
END

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH ALL_INDEXES, TABLERESULTS')

DELETE FROM #fragreport
WHERE IndexID IN (0,255)

UPDATE #fragreport
SET PrePost = 'POST'
WHERE PrePost is NULL

SET @tabcount = @tabcount + 1
TRUNCATE TABLE #indexes
END

INSERT INTO #reindex([ObjectName],[IndexName])
SELECT #tables.[name], #fragreport.[IndexName]
FROM #fragreport
INNER JOIN #tables on #tables.tabid = #fragreport.objectid
WHERE #fragreport.IndexId NOT IN (0, 255)
AND (#fragreport.ScanDensity < 90 OR #fragreport.LogicalFragmentation > 10)
AND #fragreport.PrePost = 'POST'

SELECT @numreindexes = count(*) FROM #reindex

SET @recount = 1

WHILE @recount <= @numreindexes
BEGIN
SELECT @tabname = ObjectName,
@indname = IndexName
FROM #reindex
WHERE rid = @recount

EXEC('DBCC DBREINDEX([' + @dbname + '.' + @tabname + '],[' + @indname + '])')

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + '],[' + @indname + ']) WITH TABLERESULTS')

SET @recount = @recount + 1
END

UPDATE #fragreport
SET PrePost = 'REINDEXED'
WHERE PrePost is NULL

UPDATE #fragreport
SET DBName = @dbname

INSERT INTO [master].[dbo].[fragreport]([timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost])
SELECT [timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost] FROM #fragreport

DROP TABLE #tables
DROP TABLE #indexes
DROP TABLE #fragreport
DROP TABLE #reindex


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO






Fast Text Processing in SQL Server

Fast Text Processing in SQL Server

Processing text or long strings usually reduces SQL to a prosaic procedural language.

Processing text or long strings in SQL has never been an easy task. SQL is a powerful language for performing fast operations on data sets, but when it comes to processing text or long strings, it's usually reduced to a prosaic procedural language. This article shows a few techniques for facilitating speedy text processing in SQL. Although demonstrated in SQL Server, you can apply the underlying ideas to any RDBMS with only small adjustments. Also, no third-party tools, extended stored procedures, or user-defined functions or objects written in any programming language other than SQL (or Transact-SQL) are used.

Using these techniques you will be able to do the following and more without any loops:

  • Determine the number of the words in the text
  • Determine the length and position of each word in the text
  • Determine the number of occurrences of a letter (pattern) and their positions in the text
  • Determine the frequency of each distinct word or letter in the text
  • Eliminate a letter's duplicates
  • Eliminate extra spaces between the words or between lines of text
  • Convert text according to a given format (e.g., define the length of lines in the text or implement more sophisticated formatting of the text)
Preliminary Transformations
SQL is a language dedicated to set-based processing. Text by nature requires one-by-one sequential processing, which is not the strongest feature of SQL. Hence, you can't expect an improvement in text processing if you don't change the layout of the text. In other words, you need to convert text into a structure that allows set-based manipulations.

Traditionally in relational databases, such structures were and continue to be tables. Therefore, to be able to process text using SQL, you need to put it into a table, where each word (or letter) will have a row value in a specific column.

The sections to follow show a few techniques that you can use for text conversion.

SQL Server 2005 Recursive Techniques
I'm a big fan of recursion and I always try to use it in my projects (see my MSDN article, "Recursion in T–SQL"). However, implementing recursion in SQL Server 2000 (SS2000) is not an easy, intuitive task. SS2000 allows only 32 nesting levels (calls), which makes the recursion implementation very tricky or even impossible in some situations.

This is why I was very glad to see SQL Server 2005 (SS2005) introduce a recursive query that uses common table expression (CTE). I consider that feature a great enhancement to Transact-SQL. As an example, consider how easily you can convert and load the following text (the poem "True Beauty" written by my daughter Anat Kozak) into a table using SS2005 recursion. It is a recursive query using CTE:


Listing 1. How to Convert by Words and Load Text into the Table



SET QUOTED_IDENTIFIER OFF
GO
DECLARE @str varchar(2000);
SELECT @str =
"A lonely moon about the sky,
A gentle flower in the breeze,
A giant cloud of smoky stars,
Some sticky honey made by bees.

The soft sweet voice of melody
And moist warm air, which we all breathe
Tremendous power of our minds
Which can make everyone believe..."

1 SELECT @str = REPLACE(@str, CHAR(10), ' ');
2 SELECT @str = REPLACE(@str, CHAR(13), ' ' );

3 WITH ProcessText AS
4 (SELECT 1 as startPos, CHARINDEX (' ',@str, 1) as spacePos
5 UNION ALL
6 SELECT spacePos + 1, CHARINDEX (' ',@str, spacePos + 1)
7 FROM ProcessText WHERE spacePos > 0)

8 SELECT startPos,
9 spacePos,
10 SUBSTRING(@str, startPos, spacePos - startPos) AS singleWord
11 INTO #words
12 FROM ProcessText WHERE spacePos <> 0
13 UNION ALL
14 SELECT MAX(spacePos) + 1, 0, RIGHT(@str, LEN(@str) - MAX(spacePos))
15 FROM ProcessText
16 OPTION(MAXRECURSION 0);

The CTE query definition starts in line 4, where an anchor member is defined. The recursive member, referencing ProcessText (the CTE name) is defined in lines 6 and 7. The statement, located in lines 8-16, executes CTE, inserting the result into the temporary table.

The query recursively looks for an empty space between the words and extracts substring (word), located between the current and previous empty spaces. You should take special care of some control characters, such as line feed (Char(10)) and carriage return (Char(13)), unless you want them to participate in the processing and be counted as the characters. You can find an example of such control characters processing in lines 1 and 2 of the code.

Running SELECT * FROM #words will produce the following result:


startPos spacePos sepWord
----------- ----------- --------
1 2 A
3 9 lonely
10 14 moon
15 20 about
21 24 the
25 29 sky,
30 30
31 32 A
33 39 gentle
40 46 flower
. . . . . . . . . . . . . . . . .

112 116 made
117 119 by
120 125 bees.

126 126
127 127
128 128
129 132 The
. . . . . . . . . . . . . . .

244 248 make
249 257 everyone
258 0 believe...

As you can see, the code from Listing 1 did additional work: it calculated the starting position of each word and the position of each empty space from the very beginning of the text. And that's not all. You can get a lot of useful information about the original text. For example, you can determine the number of words in the text as follows:


SELECT COUNT(*) AS numWords
FROM #words
WHERE singleWord <>'';

Result:

numWords
-----------
48

You can determine the length of each word as follows:


SELECT LEN(singleWord) as wordLength,*
FROM #words;

-- Result:

wordLength startPos spacePos singleWord
1 1 2 A
6 3 9 lonely
4 10 14 moon
5 15 20 about
3 21 24 the
4 25 29 sky,
0 30 30
1 31 32 A
6 33 39 gentle
6 40 46 flower
. . . . . . . . . . . . . . . . . . . . . .

You can determine how often each distinct word has been used as follows:


SELECT COUNT(*) AS wordFrequency, singleWord
FROM #words
GROUP BY singleWord;

-- Result:

wordFrequency singleWord
9
3 A
1 about
1 air,
1 all
1 And
1 bees.

1 believe...

1 breathe
1 breeze,
1 by
1 can
. . . . . . . . . . . .

1 sticky
1 sweet
3 The
1 Tremendous
1 voice
1 warm
1 we
2 Which

The result this last query produced includes a couple of interesting details:


  • The result has been implicitly sorted in alphabetical order on column singleWord, because the GROUP BY clause was using a SORT operation internally.
  • The number 9 in the first line of the result indicates the number of lines in the text, but not the number of spaces between the words.

SQL Server 2005 Recursive Techniques (cont'd)
Now, go back to the Listing 1. Replace lines 4 and 6 of the code with the following lines:

4 (SELECT 1 as startPos, CHARINDEX (' A',@str, 1) as spacePos
. . . . .

6 SELECT spacePos + 1, CHARINDEX (' A',@str, spacePos + 1)

Running this updated script, Listing 1 will produce the following result:


startPos spacePos singleWord
1 14 A lonely moon
15 30 about the sky,
31 62 A gentle flower in the breeze,
63 160 A giant cloud of smoky stars,. . .sweet voice of melody
161 175 And moist warm
176 189 air, which we
190 0 all breathe Tremendous power. . . everyone believe...

At first glance, the result doesn't make any sense; you just got all the phrases that start with a specific letter (A). But as you will see later, this option can be very useful when you want to format the text. Thus, when you split and load text into a table, you get easy access to each separate word, and that gives you a lot of flexibility and processing power.

But what if you need even more granularity? What if you need to work with each individual letter, instead of the whole word? Well, you can do that using the following script:


Listing 2. How to Convert by Letters and Load Text into the Table



SET QUOTED_IDENTIFIER OFF
GO
DECLARE @str varchar(2000);
SELECT @str =
"A lonely moon about the sky,
A gentle flower in the breeze,
A giant cloud of smoky stars,
Some sticky honey made by bees.

The soft sweet voice of melody
And moist warm air, which we all breathe
Tremendous power of our minds
Which can make everyone believe...";

1 SELECT @str = REPLACE(@str, CHAR(10), ' ');
2 SELECT @str = REPLACE(@str, CHAR(13), '');

3 WITH ProcessText AS
4 (SELECT 1 as startPos, SUBSTRING (@str,1,1) as letter
5 UNION ALL
6 SELECT startPos + 1, SUBSTRING (@str,startPos+1, 1)
7 FROM ProcessText WHERE startPos <= len(@str))
8 SELECT * INTO #letters
9 FROM ProcessText
10 OPTION(MAXRECURSION 0);

SELECT * FROM #letters;

Result:

startPos letter
----------- ------
1 A
2
3 l
4 o
5 n
6 e
7 l
8 y
9
10 m
11 o
12 o
13 n
. . . . . . .

249
250 b
251 e
252 l
253 i
254 e
255 v
256 e
257 .

258 .

259 .

260

The solution in Listing 2 is based on the same idea as the solution in Listing 1. The query recursively applies the SUBSTRING() function to the original string, incrementing the starting position by one on each recursion's step, and stops processing when the starting position reaches the end of the string (text).

The solution from Listing 2 gives you direct access to each letter in the text, which gives you additional flexibility and processing power. Now, you can easily determine the number of letters in the text:


SELECT COUNT(*) AS NumLetters
FROM #letters
WHERE letter <> '';

Result:

NumLetters
-----------
219

You can determine how often each distinct letter has been used:


SELECT COUNT(*) AS NumLetters,letter
FROM #letters
WHERE letter <> ''
GROUP BY letter;

Result:

NumLetters letter
----------- ------
4 ,
4 .

14 a
6 b
6 c
6 d
32 e
. . . . . . .

13 t
4 u
3 v
7 w
8 y
1 z

If you change Listing 2 slightly by replacing lines 4 and 6 with the following lines:


4 (SELECT 1 as startPos, SUBSTRING (@str,1,2) as letter
. . . . . . . . . . . .

6 SELECT startPos + 2, SUBSTRING (@str,startPos+2, 2)

You will get the consequent pairs of letters in the result:


startPos letter
----------- ------
1 A
3 lo
5 ne
7 ly
9 m
11 oo
13 n
15 a
17 bo
. . . . . . . .

If you replace lines 4 and 6 with the following lines:


4 (SELECT 1 as startPos, SUBSTRING (@str,1,2) as letter
. . . . . . . . . . . .

6 SELECT startPos + 2, SUBSTRING (@str,startPos+2, 2)

You will get the consequent pairs of letters with overlapping:


startPos letter
----------- ------
1 A
2 lo
3 on
4 ne
5 el
6 ly
7 y
. . . . . . .

The preceding two cases allow text splitting by two letters (it actually can be any number of letters) and potentially are very useful for text formatting or finding duplicates or extra spaces in the text (more on that later).

SQL Server 2000 and SQL Server 2005 Techniques
The recursive queries using CTE are available only in SS2005, so to produce the same results in SS2000 as the techniques in the previous section did, you need to apply dynamic SQL. The following script (see Listing 3) demonstrates that technique:

Listing 3. How to Convert by Words and Load Text into the Table, Using Dynamic SQL



SET QUOTED_IDENTIFIER OFF

GO

SET NOCOUNT ON

DECLARE @str varchar(8000)

SELECT @str = "James Joyce (1882-1941) was an Irish writer and poet, and is

widely considered one of the most significant writers of the 20th century."



SELECT @str = REPLACE(@str, CHAR(13),'')

SELECT @str = REPLACE(@str, CHAR(10), '')

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'dynWords' AND type = 'U')

DROP TABLE dynWords

CREATE TABLE dynWords(

wordID int identity(1,1) not null,

word varchar(30) not null)



SELECT @Str = 'INSERT INTO dynWords(word) SELECT A="' +

REPLACE(@str, ' ', '"UNION ALL SELECT"') + '"'

EXECUTE(@str);

SELECT * FROM dynWords;



Result:



wordID word

------ -------------

1 James

2 Joyce

3 (1882-1941)

4 was

. . . . . . . . . . . .



20 of

21 the

22 20th

23 century.


The logic in this example is very simple. You need to add the header "INSERT INTO #dynWords(word) SELECT A=" to the original text and then replace the spaces between the words with the phrase "UNION ALL SELECT". You will get the following string:


INSERT INTO #dynWords(word) SELECT A="James"UNION ALL SELECT"Joyce"… UNION ALL SELECT"century."

When you execute this string dynamically, the result will be loaded into the #dynWords table.

Now, let's try to determine:


  • The number of words in the text
  • The length of each word
  • The position of each empty space between the words

Listing 4 is the solution to accomplish this.


Listing 4. Processing Converted Text, Using SQL (Part 1)



-- 1. The number of words in the text
SELECT COUNT(*) AS 'Total words in the text:'
FROM dynWords
WHERE word NOT IN ('', ' ', CHAR(13), CHAR(10));

Result:

Total words in the text:
------------------------
23

-- 2. The length of each word
SELECT *, LEN(word) wordLength FROM dynWords;

Result:

wordID word wordLength
----------- ------------------------------ -----------
1 James 5
2 Joyce 4
3 (1882-1941) 11
. . . . . . . . . . . . . . . . . . . . . . . . . . .

21 the 3
22 20th 4
23 century. 8

-- 3. The numeric position of each empty space between the words
PRINT 'The numeric position of each empty space between the words:'
SELECT tbl2.*, tbl1.c1 as spacePos
FROM (SELECT c1 = SUM(LEN(t1.word)+1), c2 = t2.wordID
FROM dynWords AS t1 INNER JOIN dynWords AS t2
ON t1.wordID<=t2.wordID
GROUP BY t2.wordID) tbl1
INNER JOIN (SELECT * FROM dynWords) tbl2
ON tbl1.c2 = tbl2.wordID

Result:

The numeric position of each empty space between the words:
wordID word spacePos
----------- ------------------------------ -----------
1 James 6
2 Joyce 11
3 (1882-1941) 23
4 was 27
. . . . . . . . . . . . . . . . . . . . . . . . . . .

20 of 117
21 the 121
22 20th 126
23 century. 135

Queries 1 and 2 are self-explanatory. Query 3 is much more interesting, though it uses a pretty well known technique with self-joins. The aggregate function SUM(LEN(t1.word)+1) accumulates the number of letters (including the spaces) in the words preceding the current one. Using COUNT() instead of SUM(), you could get the number of words preceding the current one--in other words, the order number of each word in the text, which already is represented by the identity column wordID.

Now, let's proceed and find:


  • The word "and" and words that start with a letter "w"
  • The position of each phrase that starts with a letter "w"

Listing 5 is the solution to accomplish this.


Listing 5. Processing Converted Text, Using SQL (Part 2)



-- 1) Find words "and" and words, starting with a letter "w".

SELECT wordID, word
FROM dynWords
WHERE word LIKE 'w%'OR word LIKE 'AND';

Result:

wordID word
----------- --------------
4 was
7 writer
8 and
10 and
12 widely
19 writers

-- 2) The position of each phrase, starting with a letter "w".

SET QUOTED_IDENTIFIER OFF
GO
SET NOCOUNT ON
DECLARE @str varchar(8000)
SELECT @str = "James Joyce (1882-1941) was an Irish writer and poet, and is
widely considered one of the most significant writers of the 20th century."

SELECT @str = REPLACE(@str, CHAR(13),' ')
SELECT @str = REPLACE(@str, CHAR(10), '')
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'dynWords' AND type = 'U')
DROP TABLE dynWords
CREATE TABLE dynWords(wordID int identity(1,1) not null,
word varchar(100) not null)

SELECT @str = 'INSERT INTO dynWords(word) SELECT A="' +
REPLACE(@str, ' w', '"UNION ALL SELECT"w') + '"';
EXECUTE(@str)

PRINT 'The position of each phrase starting with "w":'
SELECT tbl2.*, tbl1.c1 as startPos FROM
(SELECT (SUM(LEN(t1.word)+1)+1) as c1, c2 = t2.wordID
FROM dynWords AS t1 INNER JOIN dynWords AS t2
ON t1.wordID < t2.wordID
GROUP BY t2.wordID) tbl1
INNER JOIN (SELECT * FROM dynWords) tbl2
ON tbl1.c2 = tbl2.wordID

Result:

The position of each phrase starting with "w":
wordID word startPos
----------- ---------------------------------------------- ---------
2 was an Irish 24
3 writer and poet, and is 37
4 widely considered one of the most significant 61
5 writers of the 20th century. 107

SQL Server Speed Gap: 2005 vs. 2000

Text conversion and processing techniques are very powerful and flexible; you can use them in many projects. The techniques with dynamic SQL are more restrictive (a 8,000-character limit for the varchar data type or 4,000 for nvarchar) than the recursive techniques, which are faster, but you can use them only in SS2005.


 


Microsoft SQL Server Professional

Sunday, July 29, 2007

Ultimate Website Snapshot Service

A free web service - very easy to integrate on your own website. It automates the process of capturing a snapshot of any website and displaying it as a normal JPEG-image on your own website.

read more | digg story

Snap Shots Add-On

Extend Snap Shots to every web site for free with the Snap Shots (TM) Add-On for Firefox.

read more | digg story

Friday, July 27, 2007

vbscript to convert as-ms-prefixes.txt to CSV/TSV files (for importing into database)

as-ms-prefixes.txt can be obtain from
http://www.cidr-report.org/as2.0/as-ms-prefixes.txt

Option Explicit

Sub as_ms_prefixes()
Dim fn, tx As TextStream, tmp, i1, i2, net, rir, asn, n, r, asname
Dim tx1 As TextStream, tx2 As TextStream
fn = "S:\JUNK\New Folder (2)\as-ms-prefixes.txt"
If Not fso.FileExists(fn) Then Exit Sub
Set tx = fso.OpenTextFile(fn, ForReading, False)
Set tx1 = fso.CreateTextFile(fso.BuildPath(fso.GetParentFolderName(fn), fso.GetBaseName(fn) & "_1.out"), True)
Set tx2 = fso.CreateTextFile(fso.BuildPath(fso.GetParentFolderName(fn), fso.GetBaseName(fn) & "_2.out"), True)
Do While Not tx.AtEndOfStream
tmp = tx.ReadLine
If Left(tmp, 4) = " " Then
i1 = InStr(1, tmp, vbTab & vbTab)
i2 = InStr(i1 + 1, tmp, ")")
net = Mid(tmp, 5, i1 - 5)
rir = Mid(tmp, i1 + 19, i2 - i1 - 19)
'Debug.Print net, rir
tx2.WriteLine Join(Array(Mid(asn, 3), net, rir), "")
ElseIf Left(tmp, 2) = "AS" Then
asn = Mid(tmp, 1, InStr(1, tmp, " ") - 1)
n = LTrim(Mid(tmp, 8, 9))
r = LTrim(Mid(tmp, 18, 26 - 18))
asname = Mid(tmp, 27)
tx1.WriteLine Join(Array(asn, n, r, asname), "")
Else
'invalid
End If

Loop
Out tx.Line
tx.Close
tx1.Close
tx2.Close
'assigned
'allocated
End Sub


results:

as-ms-prefixes_1.out
AS475512171234VSNL-AS Videsh Sanchar Nigam Ltd. Autonomous System
AS701811961460ATT-INTERNET4 - AT&T WorldNet Services
AS647811181118ATT-INTERNET3 - AT&T WorldNet Services
AS432311131303TWTC - Time Warner Telecom, Inc.
AS238611091203INS-AS - AT&T Data Communications Services
AS958311001109SIFY-AS-IN Sify Limited
AS1149210871091CABLEONE - CABLE ONE
AS619710031024BATI-ATL - BellSouth Network Solutions, Inc
AS1856610021011COVAD - Covad Communications Co.
AS41349911265CHINANET-BACKBONE No.31,Jin-rong Street


as-ms-prefixes_2.out
475559.151.144.0/2259.151.128.0/18
475559.160.0.0/2259.160.0.0/14
475559.160.4.0/2259.160.0.0/14
475559.160.5.0/2459.160.0.0/14
475559.160.8.0/2259.160.0.0/14
475559.160.12.0/2259.160.0.0/14
475559.160.16.0/2159.160.0.0/14
475559.160.24.0/2159.160.0.0/14
475559.160.24.0/2459.160.0.0/14
475559.160.28.0/2459.160.0.0/14

Thursday, July 26, 2007

SQLite COM Wrappers / Visual Basic DLLs

SQLite CVSTrac

COM Wrappers / Visual Basic DLLs

  • SQLitePlus is a commercial COM Wrapper DLL and Database Manager that extend the functionality of SQLite to add important features such as stored Procedures, on-the-fly Encryption and Compression, and an ADO-like object model. Also includes an excellent Database Manager/Query Analyzer and drop-in ActiveX Grid for displaying Datasets." Note that it requires the SQLite DLL in addition to the SQLitePlus COM DLL.
  • dhSQLite, free ADO-like COM Wrapper. Current Version 1.2 (engine-version 3.4.0)... great performance, builtin encryption, optimized for disconnected Recordset-scenarios, super fast serialization/deserialization with 'UpdateBatch', Connection-, Recordset-, Field- and Command-Objects, DB-Schema-Obj-Model, builtin VB-Function-Set (DateDiff, DatePart, Format$, etc.), userdefinable Functions/Collations, FTS2-support, Overridden Like, Upper, Lower and NoCase means UTF8-awareness without ICU ... combinable with a free COM-RPC-Server for usage in real AppServer-scenarios in your LAN or over the Internet (due to builtin protocol-compression and -encryption).
  • SQLiteDB is an ActiveX DLL built on top of SQLite (i.e. you only need to distribute SQLiteDb.dll) and is designed as an ADO replacement. Available in free and commercial versions. Although the site has not been updated recently, the developer does release frequent updates via the message board. The current SQLiteDB Professional build is based off of SQLite 3.4.0. The demo (built on SQLite 3.3.6) comes with plenty of examples and sample applications.
  • SQLite3VB - Making SQLite VB Compatible. Teaches how to modify the SQLite C source code to make it VB-friendly. You end up with a non-COM DLL that you can call from a VB Classic project
  • LiteX is another COM DLL wrapper for SQLite3 (ATL) (2005-Nov-29 [mpot] sqlite 3.x compatible, wrapper source code provided, useful sample code, works very well)
  • Pivotal Solutions' non-COM DLL dynamically binds to SQLite DLL at runtime and so doesn't need recompiling everytime a new version of SQLite is released, ie. you'll need to distribute both PS' DLL and SQLite's DLL.
  • AGS_SQLite SQLite wrapper 1.1.0 for Visual Basic is based on SQLite 2.8.13, and includes source + sample program. The AGS_SQLite.DLL file contains all of the functions that SQLite.DLL has, so all you need is AGS_SQLite.DLL. Geckoware was formerly known as AGSoftware.
  • SqLite2X : Free ActiveX wrapper with sources (2005-Nov-29 [mpot] sqlite 2.x only)
  • SleepSleep sqlite wrapper for vb6 - wrapper dll coded using fasm. Come with wrapper dll source code, a vb6 "sqlite browser" project files to show how to use the dll and a .bas file for you to attach on your project to use the dll instantly. (sqlite 2.x only)
  • {http://perso.wanadoo.fr/jveritecontribs/DAOSqlite.zip DAO like Sqlite Wrapper for VB6} (2005-Nov-29 [mpot] sqlite 2.x only) (2005-Nov-14, [Michael B. Johnson] No sample code on usage that I could see.)
  • qrfSQL2 Database Engine (July 2007: dead link)
  • SQLITEDB by Javier S. Bermudez (July 2007: dead link)
  • ADOSQLite VB6 wrapper requires AGS_SQLite.dll from ag-software (2005-Nov-29 [mpot] sqlite 2.x only) (July 2007: dead link)

Drivers

Javascript

.NET Framework

SQLite CVSTrac

Russian ATM runs on unactivated copy of Windows

Windows running on an ATM? Unactivated? What is the world coming to?

read more | digg story

Tuesday, July 24, 2007

ADO: AddNew Method

ADO: AddNew Method


rs.AddNew "ProductName","Chang"

or

varfields=Array("ProductName","Prize","Quantity")
varvalues=Array("Chang","19","24-12 oz bottles")
rs.AddNew varfields,varvalues



Code (VBScript):
MyObject.AddNew "FirstName", "Sasha"

Or

MyObject.AddNew Array("FirstName", "LastName"), Array("Luke", "Skywalker")

Or

varFieldList = Array("FirstName", "LastName")
varValues = Array("Luke", "Skywalker")
MyObject.AddNew varFieldList, varValues

Or

strFirstName = "The"
strLastName = "Guru"
intAge = 826
...
rsGuruData.AddNew
rsGuruData.Fields("FirstName") = strFirstName
rsGuruData.Fields("LastName") = strLastName
rsGuruData.Fields("Age") = intAge
rsGuruData.Update









Complete Recordset Object Reference

Monday, July 23, 2007

Automate Reindexing In SQL Server 2000

Automate Reindexing In SQL Server 2000

It is run by calling it in a user database, and passing it a parameter (MAXFRAG). This is a percentage value. What this means is to defragment any indexes whose scan density fall below this value. For example, if you want to defragment any indexes who scan density is less than 95%.

>> DBCC SHOWCONTIG

usage:

USE pubs
GO
EXEC sp_deframent_indexes 95.00

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL

AS

/*
This stored procedure checks index fragmentation in a database and defragments
indexes whose scan densities fall below a specified threshold, @magfrag, which
is passed to the SP. This SP was initially based on a code sample in SQL Server 2000
Books Online.
Must be run in the database to be defragmented.

*/


-- Declare variables

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @objectowner VARCHAR(255)
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @indexname CHAR(255)
DECLARE @dbname sysname
DECLARE @tableid INT
DECLARE @tableidchar VARCHAR(255)

--check this is being run in a user database
SELECT @dbname = db_name()
IF @dbname IN ('master', 'msdb', 'model', 'tempdb')
BEGIN
PRINT 'This procedure should not be run in system databases.'
RETURN
END

--begin Stage 1: checking fragmentation
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT convert(varchar,so.id)
FROM sysobjects so
JOIN sysindexes si
ON so.id = si.id
WHERE so.type ='U'
AND si.indid < 2
AND si.rows > 0

-- Create the temporary table to hold fragmentation information
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database running dbcc showcontig on each one
FETCH NEXT
FROM tables
INTO @tableidchar

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tableidchar
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Report the ouput of showcontig for results checking
SELECT * FROM #fraglist

-- Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity
FROM #fraglist f
JOIN sysobjects so ON f.ObjectId=so.id
WHERE ScanDensity <= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Write to output start time for information purposes
SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- Open the cursor
OPEN indexes

-- Loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
SET QUOTED_IDENTIFIER ON

SELECT @execstr = 'DBCC DBREINDEX (' + "'" +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + "'" +
', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'
SELECT 'Now executing: '
SELECT(@execstr)
EXEC (@execstr)

SET QUOTED_IDENTIFIER OFF

FETCH NEXT
FROM indexes
INTO @tablename, @objectowner, @objectid, @indexname, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Report on finish time for information purposes
SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- Delete the temporary table
DROP TABLE #fraglist
GO


see also:

Reducing SQL Server Index Fragmentation
If you want to determine the level of fragmentation, you can use the DBCC SHOWCONTIG statement. The DBCC SHOWCONTIG statement displays fragmentation...

Wednesday, July 11, 2007

asnumber.js

asnumber
http://www.networx.ch

http://eu.asnumber.networx.ch/asnumber/asnum?ip=207.46.248.109 
http://ws.arin.net/cgi-bin/whois.pl?queryinput=
http://ws.arin.net/cgi-bin/whois.pl?queryinput=8070
http://www.ripe.net/fcgi-bin/whois?form_type=advanced&full_query_string=&searchtext="+ ASNCurrentASNumber.getAS() 
http://www.networx.ch
eu.asnumber.networx.ch   # ASNumber European Server
us.asnumber.networx.ch   # ASNumber USA Server
ap.asnumber.networx.ch   # ASNumber Asia-Pacific Server





var server = ASNGetCharPref("server", "eu.asnumber.networx.ch");

var ASNCheckIPv4 = /[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}/
/* This entry will expire after 8 hours */
this.valid_until = (new Date()).getTime() + (60 * 60 * 8 * 1000);


this.isResolveable = function() {
/* Ignore martian IP space. */

// if (0.0.0.0/8) RFC1700
if (this.ib[0] == 0)
return false;

// if (10.0.0.0/8) RFC1918
if (this.ib[0] == 10)
return false;

// if (127.0.0.0/8) RFC1700
if (this.ib[0] == 127)
return false;

// if (169.254.0.0/16) RFC3330
if (this.ib[0] == 169 && this.ib[1] == 254)
return false;

//if (172.16.0.0/12) RFC1918
if (this.ib[0] == 172 && this.ib[1] >= 16 && this.ib[1] <= 32)
return false;

// if (192.0.2.0/24) RFC3330
if (this.ib[0] == 192 && this.ib[1] == 0 && this.ib[2] == 2)
return false;

//if (192.88.99.0/24) RFC3068
if (this.ib[0] == 192 && this.ib[1] == 88 && this.ib[2] == 99)
return false;

// if (192.168.0.0/16) RFC1918
if (this.ib[0] == 192 && this.ib[1] == 168)
return false;

//if (198.18.0.0/15) RFC2544
if (this.ib[0] == 198 && this.ib[1] >= 18 && this.ib[1] <= 19)
return false;

//if (224.0.0.0/4) RFC3171
if (this.ib[0] >= 224 && this.ib[0] <= 239)
return false;

//if (240.0.0.0/4) RFC1700
if (this.ib[0] >= 240 && this.ib[0] <= 255)
return false;

return true;
}
}



/* This entry will expire in 8 hours */
this.valid_until = (new Date()).getTime() + (60 * 60 * 8 * 1000);


this.getRIR = function() {
var m = this.doc.textContent.match(/RIR[\s]*\:[\s]*([A-Z]+)/);
if (m == null)
return "";

var db = m[1];

if (db == 'RIPENCC')
db = 'RIPE';

return db;
}



ASNXMLHttpRequest = new XMLHttpRequest();
ASNXMLHttpRequest.open("GET", "http://" + ASNASServer + "/asnumber/asnum?ip=" + ip.getFullIP(), true);
ASNXMLHttpRequest.overrideMimeType("text/plain");
ASNXMLHttpRequest.myData = ip;
ASNXMLHttpRequest.onerror = function(event) {
var self = event.target;

ASNDisplay("AS n/a", "Unable to contact ASN server or no response", "text");
}
ASNXMLHttpRequest.onload = function(event) {
var self = event.target;

if (self.status == 200) {
var doc = "" + self.responseText + "";
var xulobject = (new DOMParser()).parseFromString(doc, "application/xml");

var ip = self.myData.getIP();
var as = new ASNASCacheObject(xulobject);

if (as.isValid()) {
ASNASCache[ip] = as;
ASNDisplayASNumber(ASNASCache[ip]);
} else
ASNDisplayIP(self.myData);
} else
ASNDisplayIP(ip);
}

ASNXMLHttpRequest.send(null);

return;



function ASNLoadWhois(s) {
if (ASNCurrentASNumber == null)
return;

if (s == 'click' && ASNFastAccess == false)
return;

var db = ASNCurrentASNumber.getRIR();

if (db == '')
return;

var url = '';

if (db == 'ARIN')
url = "http://ws.arin.net/cgi-bin/whois.pl?queryinput=" + ASNCurrentASNumber.getAS();
else
url = "http://www.ripe.net/fcgi-bin/whois?form_type=advanced&full_query_string=&searchtext=" + ASNCurrentASNumber.getAS() + "&inverse_attributes=None&ip_search_lvl=Default%28nearest+match%29&alt_database=" + db + "&object_type=aut-num&filter_mail=ON";

if (ASNOpenTabs == true) {
var oldtab = getBrowser().selectedTab;

try {
ASNTab.linkedBrowser.loadURI(url, null, null);
} catch(e) {
ASNTab = getBrowser().addTab(url, null, null, null);
}

if (ASNOpenForeground == true)
getBrowser().selectedTab = ASNTab;
else
getBrowser().selectedTab = oldtab;
} else
window.open(url);
}








hostipfox - tooltip.js

try
{
var api_key = oPrefs.getCharPref("hostipfox.options.api_key")
var self = this;
var xmlHttp = new XMLHttpRequest();
var xmlUrl = 'http://api.hostip.info/?ip=' + ipaddr;

xmlHttp.open("GET", xmlUrl, true);
xmlHttp.setRequestHeader("X-Hostip-API-Version", "1.1");
xmlHttp.setRequestHeader("X-Hostip-API-Key", api_key);

xmlHttp.onreadystatechange=function()
{
if (xmlHttp.readyState==4)
{
xmlDoc = xmlHttp.responseXML;
self.location = '(unknown)';
self.coords = '(unknown)';

var gmlNS = "http://www.opengis.net/gml";
var hostipEl = xmlDoc.getElementsByTagName("Hostip").item(0);
var countryName = '';
var cityName = '';
for(i=0; i < hostipEl.childNodes.length; i++)
{
node = hostipEl.childNodes.item(i);
switch(node.nodeName)
{
case "countryAbbrev":
countryName = node.firstChild.nodeValue;
break;
case "gml:name":
cityName = node.firstChild.nodeValue;
break;
case "ipLocation":
self.coords = node.childNodes.item(1).childNodes.item(1).childNodes.item(1).firstChild.nodeValue;
break;
}
}

if(cityName != '')
self.location = cityName;

if(countryName != '')
self.location += ', ' + countryName;

self.update();
}
}
xmlHttp.send(null);
}
catch(e)
{
alert(e);
}

snap.com


snap.com

Tuesday, July 3, 2007

MS SQL Server 2005 and Regular Expressions

MS SQL Server 2005 and Regular Expressions



Sunday, November 12th, 2006
http://www.takereal.com/blog/index.php/2006/11/12/15/






1. C# source code (RegExUdf.cs) :



usingSystem;
usingMicrosoft.SqlServer.Server;
usingSystem.Text.RegularExpressions;public partial classRegExBase
{

[SqlFunction(IsPrecise = true, IsDeterministic = true)]
publicstaticboolRegExMatch(stringpattern, stringmatchString)
{
Regex tmp = newRegex(pattern.TrimEnd(null));
returntmp.Match(matchString.TrimEnd(null)).Success;
}



[SqlFunction(IsPrecise = true, IsDeterministic = true)]
publicstaticintRegExMatchesCount(stringpattern, stringmatchString)
{
Regex tmp =
newRegex(pattern.TrimEnd(null));
returntmp.Matches(matchString.TrimEnd(null)).Count;
}



[SqlFunction(IsPrecise = true, IsDeterministic = true)]
publicstaticstringRegExReplace(stringpattern, stringinputString, stringreplaceString)
{
Regex tmp =
newRegex(pattern.TrimEnd(null));
returntmp.Replace(inputString.TrimEnd(null), replaceString.TrimEnd(null));
}



};





2. Let' compile source code:



csc /t:library RegExUdf.cs



In result you have "RegExUdf.dll". I put it into "C:\".



3. Let's go to MS SQL Server 2005 and run query (RegExUdf.sql) :




USE AdventureWorks
GO



sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO



ALTER DATABASE AdventureWorks
SET TRUSTWORTHY ON;



CREATE ASSEMBLY RegExUdf
FROM 'c:\RegExUdf.dll' - Pease, UPDATE this path !
WITH PERMISSION_SET = SAFE;
GO



CREATE FUNCTION RegExMatch(@pattern nvarchar(255), @str nvarchar(255))
RETURNS bit
AS
EXTERNAL NAME
RegExUdf.[RegExBase].RegExMatch;
GO
CREATE FUNCTION RegExMatchesCount(@pattern nvarchar(255), @str nvarchar(255))
RETURNS int
AS
EXTERNAL NAME
RegExUdf.[RegExBase].RegExMatchesCount;
GO
CREATE FUNCTION RegExReplace(@pattern nvarchar(255), @str nvarchar(255), @replace nvarchar(255))
RETURNS nvarchar(255)
AS
EXTERNAL NAME
RegExUdf.[RegExBase].RegExReplace;
GO



- Example:



SELECT TOP (100) PERCENT ProductID, Name,
dbo.RegExMatchesCount(N'[0-9]', Name) AS MatchesCount,
dbo.RegExReplace(N'[0-9]+', Name, N'X') AS NameReplaced
FROM Production.Product
WHERE (dbo.RegExMatch(N'^.+[0-9].+[0-9]+$', Name) = 1)
ORDER BY ProductID






4. Result:



RegEx for MS SQL Server 2005



5. Full source code (RegExUdf.cs RegExUdf.dll RegExUdf.sql) available: RegExUDF.zip








Technorati :
Del.icio.us :
Ice Rocket :
Flickr :
Zooomr :
Buzznet :
Riya :
43 Things :

Monday, July 2, 2007

'SQL Server 2005: Regular Expressions Make Pattern Matching And Data Extraction Easier

'SQL Server 2005: Regular Expressions Make Pattern Matching And Data Extraction Easier:
Now you can perform efficient, sophisticated text analysis using regular expressions in SQL Server 2005.