Delimited string - 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)

Delimited string

Author

Topic

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-07-02 : 16:27:02

I have the following:

DECLARE @lvchrString VARCHAR(1000)

SET @lvchrString = '1;2;3;4;ROY;6;7;8;9'

Is there something I can do to be able to get the 5th item in the delimited list without looping?

*************************
Someone done told you wrong!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-07-02 : 19:55:24

Hi Roy

OK, here is one way. It is a little ugly but it will do the job.
It involves a sequence table, which you can generate with this :



Create Table Sequence (seq int not null)
SET nocount on
declare @val int
select @val = 1
while @val <= 8000
	begin
	Insert into sequence values (@val)
	select @val = @val + 1

	end

Set nocount off

Then once you have that, you use the sequence table to parse out the string,
then insert into a temp table with an identity column to grab the right row.


DECLARE @lvchrString VARCHAR(1000) 

SET @lvchrString = '1;2;3;4;ROY;6;7;8;9' 


Select 
	identity(int,1,1) as rownum,
	seq,
	Substring(';' + @lvchrString + ';',seq,
	CharIndex(';' , ';' + @lvchrString + ';' , seq) - seq) SepValue
INTO #temp
FROM SEQUENCE

WHERE 
	seq <= len(';' + @lvchrString + ';') and
	Substring(';' + @lvchrString + ';', seq - 1, 1) = ';' and
	CharIndex(';' , ';' + @lvchrString + ';' , seq) - seq > 0 


SELECT * FROM #temp
WHERE rownum = 5

DROP TABLE #temp

Like I said, it's a little ugly. But it will get you thinking

Damian

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-07-03 : 04:43:58

Here's an example that uses CHARINDEX and SUBSTRING.

DECLARE @lvchrString VARCHAR(1000)
DECLARE @StartPos int
DECLARE @EndPos int

SET @lvchrString = '1;2;3;4;ROY;6;7;8;9'

SELECT @StartPos = CHARINDEX(';',@lvchrString,
(CHARINDEX(';',@lvchrString,
(CHARINDEX(';',@lvchrString,
CHARINDEX(';', @lvchrString, 1)+1)+1))+1)) + 1

SELECT @EndPos = CHARINDEX(';', @lvchrString, @StartPos+1) - @StartPos

SELECT SUBSTRING(@lvchrString, @StartPos,@EndPos)

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-07-03 : 09:26:44

Thanks Merkin and YellowBug. Its okay if the solution is ugly, this is only going to be run once a week, and speed or efficiency is not required. These solutions definitely got me thinking

*************************
Someone done told you wrong!

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-07-04 : 12:22:22

Here's an adaptation of a parsing function I use:

CREATE FUNCTION dbo.fnParse (@List varchar(100), @Position int)
RETURNS varchar(100) AS
BEGIN
IF @@NESTLEVEL > 1
SET @List = RIGHT(@List, LEN(@List)-CHARINDEX(';', @List))
IF @@NESTLEVEL < @Position
SET @List = dbo.fnParse(@List, @Position)
IF @@NESTLEVEL = 1
SET @List = LEFT(@List, CHARINDEX(';', @List)-1)
RETURN @List
END

GO

DECLARE @lvchrString VARCHAR(1000)
SET @lvchrString = '1;2;3;4;ROY;6;TEST;8;9'
SELECT dbo.fnParse(@lvchrString, 5)

GO

One drawback is that you are limitted to a list of 32 elements. This is due the functions recursive nature and the nesting limitations of SQL Server.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-04 : 12:49:40

OK, stupid question... WHY?
If T-SQL only has a call stack of 32 calls and a comparatively large time overhead for function calls, why write the function recursively?


CREATE FUNCTION dbo.fnParse (@List varchar(100), @Position int)  
RETURNS varchar(100)  AS
BEGIN
  WHILE @Position > 1
  BEGIN
    SET @List = RIGHT(@List, LEN(@List)-CHARINDEX(';', @List))
    SET @Position = @Position - 1
  END
  RETURN LEFT(@List, CHARINDEX(';', @List)-1)
END

works just as well. That's to say, it returns the first ;-separated piece when Position is <= 1 and fails when you try to get the last piece.

Edited by - Arnold Fribble on 07/04/2002 12:50:15

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-07-05 : 08:37:16

I ended up writing a VB app to do the parsing and write a new file that only had the columns I was interested in. So the stored procedure calls the VB app via xp_cmdshell and passes in as a parameter to the VB app the full path to the file. The VB app parses the file and spits the valid columns into a new file which the stored procedure then bulk inserts. Like I said earlier, this does not have to be a pretty solution or extremely fast. Thanks joshb and Arnold for the additional solutions.

*************************
Someone done told you wrong!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources