Slice a 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

Transact-SQL (2000)

Slice a string

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-24 : 11:01:28

I am trying to splice a string in a field for each row of a table and cannot suss how to do it. I could do it in .NET but Transact is not so flexible.

Say I have three rows and each have a unique title entry thus:

YT76-K-008-T5
AS87-L-908-JI
BB65-L-988-JU

How do I split these into individual sections using the - as a delimiter.

I hve this block which does just that but I cant figure out how to use it in an update statement on the table?

[code]
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.string_splice_sp (@String varchar(30), @StringOutput varchar(15) output, @NewStartPos int output) AS

DECLARE @Pos int

--get position of first '-' in @String

SELECT @Pos = CHARINDEX ( '-' , @String )

SELECT @StringOutput = LEFT(@String, @Pos - 1)

SELECT @NewStartPos = @Pos + 1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[code]

This sample lets me know this proc does what I need

declare @T varchar (15), @S int
exec dbo.string_splice_sp 'YT76-K-008-T5', @T output, @S output
select @T
select @S

result is YT76 6

I get the first block plus the new start position to send next time round.

Any ideas?

Thnx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-25 : 01:32:32

Refer this split function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources