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

Niladri.biswas
Posted by Niladri.biswas under Sql Server category on | Points: 40 | Views : 2585
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
[Code]
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'
[/Code]
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
[Code]
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)
[/Code]
The output in both the cases is as under
[Code]
Data
a
b
c
d
e
[/Code]

We can even applly the same on a table as under

[Code]
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'
[/Code]

-OR-
[Code]
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)
[/Code]
The output in both the cases is as under
[Code]
Data
a
b
c
d
e
[/Code]
So we can then go ahead and write our own table valued function that will do the trick.
[Code]
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
[/Code]
Usage of the above function

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

/* Result */
letters
-------
a
b
c
d
e
[/Code]
Case 2: On a table column
[Code]
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
[/Code]
Hope this helps

Comments or Responses

Login to post response