Comma Seperation from a String

Question

Comma Seperation from a String

KINGSLEEN-597111

SSC Enthusiast

Points: 118
More actions
January 7, 2008 at 5:31 am

#119523

Declare @purchaseReqNo varchar(50),@reqQty varchar(10)
set @purchaseReqNo='12,241,342,34234'
while len(@purchaseReqNo) > 1
begin
SET @reqQty =substring(@purchaseReqNo,0,charindex(',', @purchaseReqNo,0))--,len(@purchaseReqNo))
SET @purchaseReqNo =substring(@purchaseReqNo,charindex(',', @purchaseReqNo)+1,len(@purchaseReqNo))
Declare @temptab table (Reqno varchar(50))
INSERT INTO @temptab(Reqno)values(@reqQty)
end
Select * from @temptab
In the Above from (@purchaseReqNo) i splited the , and stored in temp table..in @purchaseReqno if i give , in the last its working fine without , at last its not working Kindly pls give solution

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

It looks like you are attempting to insert the comma separated values from your variable into the temp table correct? If so, this is a great job for a tally table with a split function. Search SSC for split function or numbers table and you'll find lots of help.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Alocyte SSChasing Mays Points: 617 More actions · Answer 2

Hi

I did something similar like this:

while charindex('|',@FundCodes) <> 0

begin

if charindex('|',@FundCodes) <> 0

begin

set @FundCode = left(@FundCodes, charindex('|',@FundCodes)-1)

set @FundCodes = right(@FundCodes, len(rtrim(@FundCodes))-len(@FundCode)-1)

end

If your input string is a pair of {[ID,Qty],[ID,Qty],...} then you would need to loop through the input string for every SECOND comma, thus breaking it into the {[],[],...} and in that loop, use the above logic to split the [ID,Qty] into @ID,@Qty and insert that into your tempTable.

Note that the key is not to make sure that the length of the input string is not 0, but that the number of delimiting characters is not 0. Make sure you replace the '|' with ','.

Hope this helps.

So long, and thanks for all the fishpaste 😉

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

This will do it... could turn it into a function...

DECLARE @PurchaseReqNo VARCHAR(50)

SET @PurchaseReqNo = '12,241,342,34234'

DECLARE @TempTab TABLE (Element INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, ReqQty INT)

INSERT INTO @TempTab (ReqQty)

SELECT ReqQty = SUBSTRING(','+@PurchaseReqNo+',', t.Number+1, CHARINDEX(',', ','+@PurchaseReqNo+',', t.Number+1)-t.Number-1)

FROM Master.dbo.spt_Values t

WHERE t.Type = 'P'

AND t.Number > 0

AND SUBSTRING(','+@PurchaseReqNo+',', t.Number, 1) = ','

AND t.Number < LEN(','+@PurchaseReqNo+',')

SELECT * FROM @TempTab

If you plan to split strings wider than about 2000 characters, we'll need to build a Tally table or use one of the "alternate" methods for VARCHAR(MAX) strings.

And, don't look for an explicit loop in this... it's not needed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)