Click here to Skip to main content
14,271,828 members
Rate this:
Please Sign up or sign in to vote.
See more:
How to split strings in sql server without separators or delimiters?
Posted
Updated 20-Apr-12 20:47pm
v2
Comments
Abhinav S 21-Apr-12 2:47am
   
Bold tags removed.
Rate this:
Please Sign up or sign in to vote.

Solution 1

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
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
Rate this:
Please Sign up or sign in to vote.

Solution 3

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

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'
   
Rate this:
Please Sign up or sign in to vote.

Solution 8

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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100