Needed help in comma separated values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Needed help in comma separated values

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-10-21 : 02:32:42

Hello All,

I do have one field called CorporateCard with nvarchar data type. in one of my sp i will get comma separated values for above field. to fulfill the purpose i write the below code ....

DECLARE @CorporateCard NVARCHAR(4000)
DECLARE @ReportDt DATETIME

SET @CorporateCard = '3782-994249-51004, 3782-994249-51111'
SET @ReportDt = CONVERT(DATETIME,CONVERT(NVARCHAR, '08/30/2008' ,101))

DECLARE @FName NVARCHAR(255)
DECLARE @LName NVARCHAR(255)
DECLARE @version INT

DECLARE @tbl table
(
CardAccountNo NVARCHAR(4000)
)

DECLARE @p_Tmp_Str1 NVARCHAR(4000)

SET @p_Tmp_Str1 = NULL

WHILE IsNull(Len(@CorporateCard),0) > 0
BEGIN
IF IsNull(Len(@CorporateCard),0) <= 0 BREAK

IF IsNull(charindex(',',@CorporateCard),0) > 0
SET @p_Tmp_Str1 = (SELECT left(@CorporateCard,IsNull(charindex(',',@CorporateCard) - 1,0)))
ELSE
SET @p_Tmp_Str1 = @CorporateCard

IF (IsNumeric(@p_Tmp_Str1) = 1)

BEGIN
INSERT INTO @tbl
--VALUES( @p_Tmp_Str1, @p_Tmp_Str2, @p_Tmp_Str3, @p_Tmp_Str4)
VALUES( @p_Tmp_Str1)
END

------ --------------
IF IsNull(Len(@CorporateCard),0) > IsNull(Len(@p_Tmp_Str1),0)
SET @CorporateCard = (SELECT right(@CorporateCard, IsNull(Len(@CorporateCard),0) - IsNull(Len(@p_Tmp_Str1) + 1,0)))
Else
SET @CorporateCard = null
END

SELECT * FROM @tbl

-------------
But it's not giving me desire output....
can any one help me??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 02:37:56

i think what you need is this

INSERT INTO @tbl
SELECT Val
FROM dbo.ParseValues(@CorporateCard)t

parsevalues can be found below

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(200)  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources