Click here to Skip to main content
14,332,103 members
Rate this:
Please Sign up or sign in to vote.
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....


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

Solution 1

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

Solution 2

Create this function that will used for parse values
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...
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!
:)
   

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