This article describes an alternative and rather interesting way of doing string tokenizing in T-SQL.
The process of splitting a string to its tokens using a pre-defined separator is a subject discussed in many cases. The Java and C languages have this facility built in the language definition. And I and others, in previous articles have shown the T-SQL classic implementation of tokenizing a string by "cutting" it to its pieces according to the separator.
I bring here another implementation of tokenizing using the PARSENAME function. We reach the Nth -1 position of the separator in the given string and then we replace the Nth position of the separator with a '.' character. Then we return the activate the PARSENAME T-SQL function with value = 2. SQL Server is fooled to believe that this is a valid SQL "object" and returns the desired token string as output.
This is the code implementation for the tokenizing process:
Create function dbo.fn_split_string
(@BigStr varchar(200),
@seperator char(1),
@ind int
) returns varchar(200)
as
begin
declare @xpos int
declare @i int
declare @res varchar(200)
set @i=1
set @xpos = CharIndex (@seperator,@BigStr)
while @i < @ind
begin
set @i = @i + 1
set @BigStr = substring (@BigStr, @xpos + 1,len(@BigStr) - @xpos )
set @xpos = CharIndex (@seperator,@BigStr)
end
if @xpos = 0
set @res = @BigStr
else
begin
set @BigStr = substring (@BigStr,1,@xpos - 1) + '.' +
substring (@BigStr, @xpos + 1,len(@BigStr) - @xpos )
set @res = ParseName (@BigStr,2)
end
return @res
end
go
Here’s an example of a call to the function:
select dbo.fn_split_string ( 'eli@leiba@app@dba@in@iec' , '@', 1) as token1,
dbo.fn_split_string ( 'eli@leiba@app@dba@in@iec' , '@',2) as token2,
dbo.fn_split_string ( 'eli@leiba@app@dba@in@iec' , '@',3) as token3,
dbo.fn_split_string ( 'eli@leiba@app@dba@in@iec' , '@',4) as token4,
dbo.fn_split_string ( 'eli@leiba@app@dba@in@iec' , '@',5) as token5,
dbo.fn_split_string ( 'eli@leiba@app@dba@in@iec' , '@',6) as token6
We will get the following results:
Token1 Token2 Token3 Token4 Token5 Token6
-------------- -------------------- ------------------ --------------- ------------- ------------
eli leiba app dba in iec
(1 row(s) affected)
Conclusion
The function I presented here can be used as a general tool for tokenizing strings as shown.
Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail: iecdba@hotmail.com)
