Click here to Skip to main content
15,612,595 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
here i need spliting 2 strings in below process. How can i split these two strings in sql server.
could any one know, send me the reply for this....


VB
STRING 'A,B,C,D'
STRING '1,2,3,4'

APLHA  NUMB
A       1
B       2
C       3
D       4





Regards
Nanda Kishore.CH
Posted

You will have to handle it yourself, there is no built in mechanism for that. But it's not that complex: Using comma separated value parameter strings in SQL IN clauses[^] shows a function designed to handle a single CSV string, you could easily adapt it to do two.
 
Share this answer
 
Create this function that will used for parse values
SQL
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(max) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(max))
AS
BEGIN
    DECLARE @Value varchar(max)
    WHILE @String is not null
    BEGIN
        SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(
          @String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, 
          @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(
          @String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
        INSERT INTO @RESULTS (Val)
        SELECT @Value
    END
RETURN
END

Query to use...
SQL
select a.val as Alpha ,b.val as Nums from dbo.parsevalues('a,b,c,d',',')  as a
left join dbo.parsevalues('1,2,3,4',',')  as b on a.id=b.id

Happy Coding!
:)
 
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