How to split strings in sql server

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

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

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

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