Split string into fields....HELP..Urgent!

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)

Split string into fields....HELP..Urgent!

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-01-12 : 15:02:24

Hi I need to split data in a field where ; is the delimiter.

i.e.

Name
Joe; Bloggs
David; Peter; Paul

Result should be:
Name Name1 Name2 Name3
Joe; Bloggs Joe Bloggs
David; peter; Paul David Peter Paul

I am playing and using the charindex function but no luck so far.

Thanks in advance!!

p.s I need to get this done by Sat evening!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-12 : 15:41:36

Why the rush?

Peter Larsson
Helsingborg, Sweden

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-01-12 : 16:59:12

Deadline for a project...:S

Results need to be analysed on Sunday (of all days!)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-12 : 17:11:28

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

Tara Kizer

markusf
Starting Member

15 Posts

Posted - 2007-01-18 : 10:04:28

This function splits a string on any delimitor, and is quite fast also actually :-)

CREATE FUNCTION dbo.get_StrSplit (
@p_String VARCHAR(1024)
,@p_FieldOffset TINYINT
,@p_Delimitor VARCHAR(4) = '|'
)
RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @v_start INT
DECLARE @v_end INT
DECLARE @v_string_len INT
DECLARE @v_delimitor_i INT
DECLARE @v_return VARCHAR(1024)

SET @v_string_len = LEN(@p_String)
SET @v_delimitor_i = 1
SET @v_start = 0
SET @v_end = 0
SET @v_return = ''

WHILE (@v_delimitor_i <= @p_FieldOffset)
BEGIN
IF (@v_delimitor_i = @p_FieldOffset)
BEGIN
-- starting point is allready set
SET @v_end = CHARINDEX(@p_Delimitor, @p_String, @v_start)
IF (@v_end = 0)
BEGIN
-- last col
SET @v_end = @v_string_len + 1
END

SET @v_return = SUBSTRING(@p_String, @v_start, @v_end - @v_start)
BREAK
END

-- find next delimitor
SET @v_start = CHARINDEX(@p_Delimitor, @p_String, @v_start) + 1

IF @v_start = 1
BEGIN
--outside range (since we take + 1)
BREAK
END
SET @v_delimitor_i = @v_delimitor_i + 1
END
RETURN @v_return
END
GO

--
Markus Foss, Norway

Ian83

5 Posts

Posted - 2007-01-20 : 09:49:33

(Spam Removed)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources