Hi ,
In this article we will create a table value split function in sql server ,and use it in sql query.
In this article we will create a table value split function in sql server ,and use it in sql query.
2)Create Function::
Create function [dbo].[SplitString]
(
@str nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex (@separator, @str)
union all
select
p + 1,
b + 1,
charindex (@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 ID,
substring (
@str,
a,
case when b > 0 then b-a ELSE 4000 end )
AS s from tokens
)
(
@str nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex (@separator, @str)
union all
select
p + 1,
b + 1,
charindex (@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 ID,
substring (
@str,
a,
case when b > 0 then b-a ELSE 4000 end )
AS s from tokens
)
3)Test Case::
declare @your_str varchar(max)
declare @saparated_by char(1)
set @your_str ='sandipG@sql@blogspot@.@In'
set @separated_by ='@'
select id,s saprated_string from SplitString(@your_str,@saparated_by)
This post has a full information i really love all of this information .
ReplyDeleteBest Regards,
Liza Curinto
forex server