Click here to Skip to main content
15,904,153 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 22-Apr-22 7:56am
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.
 
Share this answer
 
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

SQL
SELECT s FROM SplitString('Hello John Smith', ' ')
WHERE zeroBasedOccurance=1
 
Share this answer
 
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 ,

SQL
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,


SQL
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
 
Share this answer
 
--In Sql

DECLARE @STRING VARCHAR(20),@NUM VARCHAR(20),@STR VARCHAR(20)
SET @STRING = '1HAR9I9SH7';

SET @NUM = @STRING
SET @STR = @STRING

WHILE(PATINDEX('%[^0-9]%',@NUM)> 0)

BEGIN

SET @STR = REPLACE(@STR,SUBSTRING(@STR,PATINDEX('%[^A-Z]%',@STR),1),'')

SET @NUM = REPLACE(@NUM,SUBSTRING(@NUM,PATINDEX('%[^0-9]%',@NUM),1),'')

END

SELECT @STR
SELECT @NUM
 
Share this answer
 
v2
thank you for all finally i find solutions 

SQL
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'
 
Share this answer
 
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
 
Share this answer
 

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