Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Uncommon Table Expressions

By Peter Kierstead,

Uncommon Table Expressions

I present here a use for Common Table Expressions which may be helpful in your programming environment.

The Fundamentals

The basic component of this article is a CTE that is used to parse a string into a table with a single character column. This is done to allow string processing without WHILE loops or TALLY tables. Once your string is in this format many rowset-based operations can be applied to it resulting in the creation of SQL-based string functionality.

Following is the basic CTE used for the string functions:

Declare
	@Str VarChar(max);
Select
	@Str='This is a test...';

With Parse as
(
	Select
		SubString(@Str,1,1)[Chr],
		1[Idx]
	Union All
	Select
		SubString(@Str,Idx+1,1),
		Idx+1
	from Parse
	where (Idx+1)<=Len(@Str)
)
Select
	*
from Parse
option (MaxRecursion 0);

Let’s break this recursive CTE down into its functional components for analysis.

First we have the anchor component, so called because it forms the initial result set from which subsequent iterations (recursions) will process.

Select
	SubString(@Str,1,1)[Chr],
	1[Idx]

Here is it's initial contribution to the output rowset:

	|Chr	|Idx	|
1	|T	|1	|

Next we have the recursion component appended to the anchor via Union All.

Notice how it refers to the column values in the initial row.

Union All
Select
	SubString(@Str,Idx+1,1),
	Idx+1
from Parse

and it's intermediate, iteration 1, contribution to the output result set:

	|Chr	|Idx	|
1	|T	|1	|
2	|h	|2	|

Iteration 2:

	|Chr	|Idx	|
1	|T	|1	|
2	|h	|2	|
3	|i	|3	|

And finally, the completed output result set:

	|Chr	|Idx	|
1	|T	|1	|
2	|h	|2	|
3	|i	|3	|
4	|s	|4	|
5	| 	|5	|
6	|i	|6	|
7	|s	|7	|
8	| 	|8	|
9	|a	|9	|
10	| 	|10	|
11	|t	|11	|
12	|e	|12	|
13	|s	|13	|
14	|t	|14	|
15	|.	|15	|
16	|.	|16	|
17	|.	|17	|

But what caused the recursion to stop? From SQL Server 2005 Books Online

The termination check is implicit; recursion stops when no rows are returned from the previous invocation.

This is accomplished via the WHERE clause on the recursion component.

Recursion stops after the last character of the string has been parsed.

Union All
Select
	SubString(@Str,Idx+1,1),
	Idx+1
from Parse
where (Idx+1)<=Len(@Str)

Now that we understand how the recursive CTE is processing our data lets create a few useful functions.

The OCCURS Function

This function will count the occurrences of a specified character in a string.

Declare
	@Str VarChar(max);
Select
	@Str='This is a test...';

With Occurs as
(
	Select
		SubString(@Str,1,1)[Chr],
		1[Idx]
	Union All
	Select
		SubString(@Str,Idx+1,1),
		Idx+1
	from Occurs
	where (Idx+1)<=Len(@Str)
)
Select
	Count(*)
from Occurs
where Chr='t'
option (MaxRecursion 0);

Here the Occurs CTE does the same thing as the Parse CTE in the initial example. The occurrence counting actually happens in the SELECT statement that calls the CTE in conjunction with it's WHERE clause.

The PHONE_CLEAN Function

This function will remove all non-numeric characters from a string.

Declare
	@Str1 VarChar(max),
	@Str2 VarChar(max);
Select
	@Str1='(406) 555-1212',
	@Str2='';

With PhoneClean as
(
	Select
		Case
			when SubString(@Str1,1,1) like '[0-9]' then SubString(@Str1,1,1)
			else ''
		End[Chr],
		1[Idx]
	Union All
	Select
		Case
			when SubString(@Str1,Idx+1,1) like '[0-9]' then SubString(@Str1,Idx+1,1)
			else ''
		End,
		Idx+1
	from PhoneClean
	where (Idx+1)<=Len(@Str1)
)
Select
	@Str2=@Str2+Chr
from PhoneClean
option (MaxRecursion 0);
Select
	@Str2;

In this example the code in the CTE will only emit characters that are numeric into the output result set. The expected result is realized via a running concatenation in the SELECT statement that calls the CTE.

The REPLACE_Nth_CHAR Function

This function will replace the Nth occurrence of a character in a string with a specified character.

Declare
	@Str1 VarChar(max),
	@Str2 VarChar(max);
Select
	@Str1='XXXXX',
	@Str2='';

With ReplaceNthChar as
(
	Select
		SubString(@Str1,1,1)[Chr],
		1[Idx]
	Union All
	Select
		SubString(@Str1,Idx+1,1),
		Idx+1
	from ReplaceNthChar
	where (Idx+1)<=Len(@Str1)
)
Select
	@Str2=@Str2+
		Case Idx
			when 3 then '0'
			else Chr
		End
from ReplaceNthChar
option (MaxRecursion 0);
Select
	@Str2;

Here the ReplaceNthChar CTE does the same thing as the Parse CTE in the initial example. The Nth occurrence is replaced in the SELECT statement that calls the CTE via a CASE statement in a running concatenation.

I think you're getting the picture!

I leave it to you to repackage these examples as functions or procedures for use in your shop.

DISCLAIMER

For large character counts this technique won't perform as well as a routine using a pre-created "tally" table as this technique essentially creates it's "tally" table on the fly each time it is executed.

Total article views: 9296 | Views in the last 30 days: 1
 
Related Articles
BLOG

Substring() function in SQL Server

Substring():- This function is used to return the specified number (third argument "len") of charact...

FORUM
FORUM

substring

using substring to parse data from string

FORUM

Random 64 Characters alphanumeric String

Random 64 Characters alphanumeric String

FORUM

extact last 3 characters from a string

using substring

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones