Click here to Skip to main content
14,743,331 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to split strings in sql server without separators or delimiters?
Posted
Updated 20-Apr-12 21:47pm
v2
Comments
Abhinav S 21-Apr-12 2:47am
   
Bold tags removed.

The function given here[^] will help you split string on any character.
This thread[^] has some solutions as well.
   
Comments
Vasim889 21-Apr-12 2:54am
   
thnks u .but i want solution like this select top 10 * from dbo.split('Chennai#Bangalore#Mumbai') .i am not using any separators and delimeters
First, create a function

create function 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 zeroBasedOccurance,
            substring(
                @str, 
                a, 
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens
      )
    GO


Then, use it as table

SELECT s FROM SplitString('Hello John Smith', ' ')
WHERE zeroBasedOccurance=1
   
Comments
Vasim889 21-Apr-12 6:06am
   
thnks u .but my Question is split strings without separators or delimiters like this
SELECT s FROM SplitString('Hello John Smith')
WHERE zeroBasedOccurance=1
SELECT s FROM SplitString('Hello@John@Smith')
WHERE zeroBasedOccurance=1
SELECT s FROM SplitString('Hello~John~Smith')
WHERE zeroBasedOccurance=1
SELECT s FROM SplitString('Hello^John^Smith')
WHERE zeroBasedOccurance=1
SELECT s FROM SplitString('Hello;John;Smith')
WHERE zeroBasedOccurance=1
without separators
hi ,


you can use this function ,

create FUNCTION [dbo].[Split]
(
@RowData nvarchar(2000)
)
RETURNS @RtnValue table
(
Data nvarchar(500)
)

AS

BEGIN

Declare @Cnt int


Declare @SplitOn nvarchar(5)
 
 
declare @seperator varchar(1)
Declare @specialCharcters nvarchar(50)
set @specialCharcters='~!@#$%^&()_-<>,./*+\|[]{};:" ' -- add any special characters missed
 
    declare @l int
   set @l = len(@RowData)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@RowData, @p, 1))
 
 
      if (charindex(char(@c),@specialCharcters)>=1)
begin
set @SplitOn=char(@c)

end
set @p = @p + 1
  
      end
   
    
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)

Begin

Insert Into @RtnValue (data)

Select

Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

Set @Cnt = @Cnt + 1

End

Insert Into @RtnValue (data)

Select Data = ltrim(rtrim(@RowData))

Return

END




then call by like,


select top 10 * from dbo.split('Chennai"Bangalore"Mumbai')

select top 10 * from dbo.split('Chennai,Bangalore,Mumbai')
select top 10 * from dbo.split('Chennai#Bangalore#Mumbai')


Regards,
Pal
   
thank you for all finally i find solutions 

CREATE PROCEDURE [dbo].[EI_SP_SPLIT]       
@Strings NVARCHAR(MAX),      
@Separetor NVARCHAR(1)=null      
AS      
BEGIN      
SET NOCOUNT ON;      
DECLARE @COUNT INT      
SET @COUNT=LEN(@Strings)    
WHILE  @COUNT > 0    
BEGIN    
    
if     
(ASCII(LEFT(RIGHT(@Strings,@COUNT),1))> = 33 and ASCII(LEFT(RIGHT(@Strings,@COUNT),1))< =47)    
OR    
(ASCII(LEFT(RIGHT(@Strings,@COUNT),1))> = 58 and ASCII(LEFT(RIGHT(@Strings,@COUNT),1))< =64)    
OR   
(ASCII(LEFT(RIGHT(@Strings,@COUNT),1))> = 91 and ASCII(LEFT(RIGHT(@Strings,@COUNT),1))< =96)    
OR    
(ASCII(LEFT(RIGHT(@Strings,@COUNT),1))> = 123 and ASCII(LEFT(RIGHT(@Strings,@COUNT),1))< =127)    
and    
(@Separetor is not null)    
begin    
 set @Separetor =LEFT(RIGHT(@Strings,@COUNT),1)    
END    
 SET @COUNT=@COUNT-1    
END     
IF @Strings  IS NOT  NULL      
BEGIN      
DECLARE @INPUT NVARCHAR(MAX)      
DECLARE @OUTPUT NVARCHAR(MAX)      
if (right(@Strings,1) <> @Separetor ) 
begin      
set @Strings = @Strings + @Separetor      
end      
create table #temp      
(      
Strings nvarchar(max)      
)      
SET @COUNT= len(@Strings)-len(replace(@Strings,@Separetor,''))        
WHILE @COUNT >  0      
BEGIN      
SET @INPUT =  left( @Strings,(Charindex(@Separetor,@Strings))-1)      
SET @Strings = right( @Strings,len(@Strings)-(Charindex(@Separetor,@Strings)))      
SET @COUNT=@COUNT-1      
insert into #temp      
(Strings)values(@INPUT)      
END    
SET @OUTPUT='select * from #temp '      
EXEC (@OUTPUT)      
END       
END
exec ei_sp_split'Try%this%one'
   
select Item from dbo.SplitStrings_CTE('rer,rerer,rerer,rere',',')


Try this.........


ALTER FUNCTION [dbo].[SplitStrings_CTE]
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS @Items TABLE (id int identity,Item NVARCHAR(4000))
with schemabinding
AS
BEGIN
DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);

WITH a AS
(
SELECT
[start] = 1,
[end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, @ld), 0), @ll),
[value] = SUBSTRING(@List, 1,
COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, @ld), 0), @ll) - 1)
UNION ALL
SELECT
[start] = CONVERT(INT, [end]) + @ld,
[end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, [end] + @ld), 0), @ll),
[value] = SUBSTRING(@List, [end] + @ld,
COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, [end] + @ld), 0), @ll)-[end]-@ld)
FROM a
WHERE [end] < @ll
)
INSERT @Items SELECT [value]
FROM a
WHERE LEN([value]) > 0
OPTION (MAXRECURSION 0);

RETURN;
END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900