seperate string with the delimiter...

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

General SQL Server Forums

New to SQL Server Programming

seperate string with the delimiter...

Author

Topic

davidk
Starting Member

5 Posts

Posted - 2009-02-04 : 23:48:39

Hi,

I have a string like this which is passing from the front end
str = 'id1-s1,s2,s3|id2-s4,s5,s6'

On the backend i need to seperate first id1 and then s1 and s2 and s3 then i need to seperate pipe seperator. The same process, id2 first and then s4 and s5 and s6.

After seperating the string, i have to update those values into the table

Update tbl1 set field1 = s1,field2 = s2,field3 = s3 where id = id1
and again
Update tbl1 set field1=s4,field5 = s5,field6 = s6 where id = id2

Can any help me on this
Advance thanks

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-04 : 23:55:20

Try this also

declare @var varchar(32)
set @var = 's1,s2,s3'
set @var = @var+','

;WITH csvtbl(i, j)
AS
(
SELECT 0, j = CHARINDEX(',', @var)
UNION ALL
SELECT CAST(j + 1 AS INT), j = CHARINDEX(',', @var, j + 1)
FROM csvtbl
WHERE CHARINDEX(',', @var, j + 1) <> 0
)

SELECT SUBSTRING(@var, i, j-i) from csvtbl

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-05 : 05:06:29

You might want to consider using a table valued function to split the string and return a table of valued.

here's an example:


IF EXISTS (
	SELECT * from dbo.sysobjects
	WHERE 
		id = object_id(N'[dbo].[fn_Split]')
		AND xtype IN (N'FN', N'IF', N'TF')
	)
	DROP FUNCTION [dbo].[fn_Split]
GO


CREATE FUNCTION fn_Split (@text VARCHAR(8000), @delimiter VARCHAR(20) = ' ')

	RETURNS @Strings TABLE (
		position INT IDENTITY PRIMARY KEY
		, value VARCHAR(8000)
		)

AS BEGIN
	DECLARE @index int
	SET @index = -1

	WHILE (LEN(@text) > 0) BEGIN
		-- Find the first delimiter
		SET @index = CHARINDEX(@delimiter , @text)

		-- No delimiter left?
		-- Insert the remaining @text and break the loop
		IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
			INSERT INTO @Strings VALUES (@text)
			BREAK 
		END 

		-- Found a delimiter
		-- Insert left of the delimiter and truncate the @text
		IF (@index > 1) BEGIN
			INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
			SET @text = RIGHT(@text, (LEN(@text) - @index))
		END
		-- Delimiter is 1st position = no @text to insert
		ELSE SET @text = RIGHT(@text, (LEN(@text) - @index))
	END
	RETURN
END
GO

You would use it like this:


DECLARE @foo VARCHAR(255)

SET @foo = 'foo,bar,widget'

SELECT * FROM fn_Split(@foo, ',')

You'd have to do a bit of work to change it to your requirements.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources