splitting a string value. - SQL Server Forums

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

SQL Server Development (2000)

splitting a string value.

Author

Topic

simonchia
Starting Member

11 Posts

Posted - 2004-07-04 : 20:21:50

greetings, i need to split a string value from a column e.g
999999, 888888, 777777, 111111, 222222 and store these values into a temp table or such. anyway, i've successfully split em... BUT... if its only "999999" without the comma. it wont split cause i'm using CHARINDEX and requestion CHARINDEX to look for this value ",". how can i solve this. plz dont ask me to use parsename cause its only good up for 4 values. below are my code... need advice.

CREATE PROCEDURE pr_DISTI_CMF_NO (@loginId nchar(10)) AS DECLARE @intNextCnt int
DECLARE @intStart int
DECLARE @intLen int
DECLARE @subString nchar(10)
SET @intNextCnt = 1
SET @intStart = 0
SET @intLen = 0

--create the tmp table to store the split value
CREATE TABLE #tmpCMF(
CMFNo nchar(10)
)

WHILE (@intNextCnt <> 0)
BEGIN
SELECT @intNextCnt = CHARINDEX(',', usr_cmf_num, @intNextCnt + 1) FROM CPM WHERE Login_Id = @loginid

SELECT @subString = SUBSTRING(usr_cmf_num, @intStart ,CHARINDEX(',', usr_cmf_num)) FROM CPM WHERE Login_Id = @loginid
SET @intStart = @intNextCnt + 1
print @intStart

INSERT INTO #tmpCMF VALUES(LTRIM(@subString))
END

SELECT cmf.cust_id, cmf.disti_id, a.cust_lvl_2_nm FROM cmf,
(SELECT CMF.cust_id, #TMPCMF.cmfno, CMF.Disti_ID, Disti.cust_lvl_2_nm FROM CMF
INNER JOIN #TMPCMF ON Right(CMF.cust_id, 6) = #TMPCMF.cmfno
INNER JOIN Disti ON CMF.Disti_ID = Disti.Disti_ID) a
WHERE cmf.disti_id = a.disti_id
GROUP BY a.cust_lvl_2_nm, cmf.cust_id, cmf.disti_id
ORDER BY a.cust_lvl_2_nm ASC

DROP table #TMPCMF
GO

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-04 : 20:28:48

Have you given this a read?

http://www.sqlteam.com/item.asp?ItemID=2652

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

simonchia
Starting Member

11 Posts

Posted - 2004-07-04 : 22:19:10

nop i have not.. but thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-04 : 22:44:02

just add a comma to the end...

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources