Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 119
#SQLServer 88
Oracle 70
#SQL SERVER 35
BRH 31
SQL Server 2012 29
denali 23
#TSQL 19
TSQL 19
C# 15

Archive · View All
October 2011 31
November 2011 30
September 2011 30
August 2011 18
December 2011 15
July 2011 13
June 2011 8
May 2012 4
April 2012 3
January 2010 3

Split a set of contiguous string into individual characters/letters using Set Based approach

Jan 1 2012 10:09PM by Niladri Biswas   

It is often a requirement to split a set of contiguous string into individual characters/letters. The below code will help us to do so

Declare @str Varchar(50) = 'abcde'

Select
   Data = Substring(@str,Number,1)        
 From  master.dbo.spt_values
 where Number Between 1 And Len(@str)
 And Type='P' 

Basically we are spliting the characters based on the position by using the master.dbo.spt_values system table which on the other hand acts as a number table. We can, however, create our own number table for achieving the same. The below is a way of doing so

Declare @str Varchar(50) = 'abcde'
;With NumCte As
(
	Select Number = 1 Union All
	Select Number +1 From NumCte 
	Where Number < 1000
)
Select
   Data = Substring(@str,Number,1)        
 From  NumCte
 where Number Between 1 And Len(@str)
 Option (Maxrecursion 0)

The output in both the cases is as under

 Data
a
b
c
d
e

We can even applly the same on a table as under

 Declare @t Table(Col1 varchar(50))
 Insert Into @t Select 'abcde'

 Select
   Data = Substring(Col1,Number,1)        
 From  @t
 Inner Join master.dbo.spt_values
 On Number Between 1 And Len(Col1)
 And Type='P'

-OR-

 Declare @t Table(Col1 varchar(50))
 Insert Into @t Select 'abcde'

 ;With NumCte As
(
	Select Number = 1 Union All
	Select Number +1 From NumCte 
	Where Number <1000
)
 Select
   Data = Substring(Col1,Number,1)        
 From  @t
 Inner Join NumCte
 On Number Between 1 And Len(Col1)
 Option (Maxrecursion 0)

The output in both the cases is as under

 Data
a
b
c
d
e

So we can then go ahead and write our own table valued function that will do the trick.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitIntoIndividualLetters]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SplitIntoIndividualLetters]
GO
CREATE FUNCTION [dbo].[SplitIntoIndividualLetters](@Str varchar(8000))        
Returns @Lettertable TABLE (letters varchar(8000))        
AS  
Begin
		;With NumCte As
		(
			Select Number = 1 Union All
			Select Number +1 From NumCte 
			Where Number <1000
		)
		Insert Into @Lettertable(letters)
		Select Substring(@str,Number,1)        
		 From  NumCte
		 where Number Between 1 And Len(@str)
		 Option (Maxrecursion 0)
	Return
End           

Usage of the above function

Case 1: On a single variable
Declare @str Varchar(50) = 'abcde'
Select * 
From dbo.SplitIntoIndividualLetters(@str)

/* Result */
letters
-------
a
b
c
d
e
Case 2: On a table column
Declare @t Table(Col1 varchar(50))
Insert Into @t Select 'abcde' Union All Select 'xyz'

Select 
		OriginalData = t.Col1
		, l.Letters
From @t t
CROSS APPLY
	dbo.SplitIntoIndividualLetters(t.Col1) AS l
	
/* Result */
OriginalData	Letters
abcde	a
abcde	b
abcde	c
abcde	d
abcde	e
xyz	x
xyz	y
xyz	z

Hope this helps

Tags: #SQLServer, SQL Server, #TSQL,


Niladri Biswas
7 · 21% · 6710
3
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • Recursive CTEs are just another form of RBAR and should be avoided in these situations. There are more efficient ways to create virtual or materialised tables of numbers. See the methods of Jeff Moden and Itzik Ben Gan for example.

    commented on Jan 20 2012 12:26AM
    goco
    1526 · 0% · 13
  • To see just how bad a recursive counting CTE like the example given can be compared to other methods, please see the article at the following link. It also demonstrates 3 other methods for counting including a version of Ben-Gan's fine code.

    link text

    To be sure, not all recursive CTEs are bad but recusrive CTEs that count like this usually are.

    commented on Jan 22 2012 7:05PM
    Jeff Moden
    161 · 1% · 300
  • Apollogies... the link on my last post didn't come through and it won't let me edit the post. The link is...

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    commented on Jan 22 2012 7:07PM
    Jeff Moden
    161 · 1% · 300

Your Comment


Sign Up or Login to post a comment.

"Split a set of contiguous string into individual characters/letters using Set Based approach" rated 5 out of 5 by 3 readers
Split a set of contiguous string into individual characters/letters using Set Based approach , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]