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 : 10:58:20
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:33:25
Duplicate post
http://sqlteam.com/forums/topic.asp?TOPIC_ID=58216

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sqlmember
Starting Member

7 Posts

Posted - 2005-11-25 : 02:37:49
Do you want the output like this

declare @str varchar(255)
set @str = 'YT76-K-008-T5'

DECLARE @string varchar(255),
@separator char(1),
@separator_position int,
@array_value varchar(1000)

DECLARE @OUTPUT TABLE (Row varchar(255))

SET @string = 'YT76-K-008-T5'
SELECT @separator = '-'

SET @string = @string + @separator

WHILE PATINDEX('%' + @separator + '%' , @string) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%' , @string)
SELECT @array_value = LEFT(@string, @separator_position - 1)

INSERT INTO @OUTPUT SELECT Array_Value = @array_value

SELECT @string = STUFF(@string, 1, @separator_position, '')
END

SELECT * FROM @OUTPUT

-Khurram Iqbal
Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-25 : 09:47:33
Hi yes that is the output I want thanks, but the code I supplied would work the same if I looped thru it.

The problem I have is how do I use this output to insert into a row of an exiting table?

Table like this:

Part f1 f2 f3 f4
YT76-K-008-T5
YY87-K-089-H8

To end up wiith

Part f1 f2 f3 f4
YT76-K-008-T5 YT76 K 008 T5
YY87-K-089-H8 YY87 K 089 H8

I was just trying the stuff from the link that Madhivanan posted which has a function that returns a table, but cant get the function call to assign the returned table to a table in my procedure:(

I need to cycle through my table and update all the f fields with the values from the function but haven't a clue how to do it? :(

PS thanks for your input chaps







Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-25 : 11:11:25
Almost there, I now know how to get a table back from a function :)

Select *
From dbo.function('balh blah', '-')
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-25 : 11:59:47
A cheap and nasty trick is to use PARSENAME. It's not very flexible, but if you have no dots in your part numbers, and they only have four parts:

UPDATE PartTable
SET f1 = PARSENAME(REPLACE(Part,'-','.'), 4),
f2 = PARSENAME(REPLACE(Part,'-','.'), 3),
f3 = PARSENAME(REPLACE(Part,'-','.'), 2),
f4 = PARSENAME(REPLACE(Part,'-','.'), 1)

Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-25 : 15:14:25
Arnold not sure I understand your example? :(

However I now call my function passing the title and delimiter being used and the parent rows id value;

SELECT *
FROM dbo.string_split (A.id, A.title, '-')

This works ok to select the items created but I still can figure out how to update my callers table with the values return from the function call.

In your example you have multiple entries in the set statement, that is KOOL I did not know you could do that! :)

Do you know how I can include the call to my function into an update statement? Not sure I am explaining myself very good here!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-11-26 : 05:18:31
Is each part fixed length?

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

i.e.

4 characters, 1 character, 3 characters, 2 characters?

If so use SUBSTRING function

If they are variable length, delimited by hyphens, AND you only want the first 4 elements then use Arnold's code, or use my code below. Do not use a SPLITing function in this case - that is to split a string where the NUMBER of elements is variable.

SELECT
[f1] = substring(Part + '-', 0 + 1, charindex('-', Part + '-', 0 + 1) - 0 - 1 )
, [f2] = substring(Part + '-', charindex('-', Part + '-') + 1, charindex('-', Part + '-', charindex('-', Part + '-') + 1) - charindex('-', Part + '-') - 1 )
, [f3] = substring(Part + '-', charindex('-', Part + '-', charindex('-', Part + '-') + 1) + 1, charindex('-', Part + '-', charindex('-', Part + '-', charindex('-', Part + '-') + 1) + 1) - charindex('-', Part + '-', charindex('-', Part + '-') + 1) - 1 )
, [f4] = substring(Part + '-', charindex('-', Part + '-', charindex('-', Part + '-', charindex('-', Part + '-') + 1) + 1) + 1, charindex('-', Part + '-', charindex('-', Part + '-', charindex('-', Part + '-', charindex('-', Part + '-') + 1) + 1) + 1) - charindex('-', Part + '-', charindex('-', Part + '-', charindex('-', Part + '-') + 1) + 1) - 1 )
FROM PartTable

Kristen
Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-26 : 07:14:21
Hi, I have it all working the way I want, I think :) !! But still have a problem geeting the data back.

I am using a cursor as the dataset in use is small so perfomance should not be an issue. I have everything working perfect except getting the values into the table.

If I open a cursor for update and place the values for the fetch into variables how do I get these modified variable back into the table? I keep getting nulls?

DECLARE crs_parts CURSOR
FOR
SELECT A.part, A.title, A.type, A.option, A.mail, A.to
FROM @Sales A
FOR UPDATE

OPEN crs_parts

FETCH NEXT FROM crs_parts INTO @part, @title, @type, @option, @mail, @to

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @TempParts (title, type, option, mail, to)
SELECT
A.title,
A.type,
A.option,
A.mail,
A.to
FROM dbo.part_splice_fn(@part, '-') A

SET @title = (SELECT title FROM @tempParts)
SET @Type = (SELECT type FROM @tempParts)
SET @option = (SELECT option FROM @tempParts)
SET @Mail = (SELECT mail FROM @tempParts)
SET @to = (SELECT to FROM @TempParts)

DELETE
FROM
@tempParts

FETCH NEXT FROM crs_parts INTO @part, @title, @type, @option, @mail, @to
END

CLOSE crs_parts

DEALLOCATE crs_parts

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-11-26 : 09:17:08
Looks horribly complicated. Why not just use an UPDATE - e.g. like the one Arnold suggested?

Kristen
Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-26 : 10:19:07
I have tried to just use an update statement but cannot figure out how to extract the field parts in this way.

I can't use arnolds code as I have a total of 5 fields which could increase in time.

I cannot figure out how to amend a field that is contained in a cursor?
Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-26 : 10:33:43
OK I have been reading a lot on this site about the 'DO NOT USE' cursors advice.

So I am back to square 1 :(

I see that the advice is to maybe use a loop to cycle through the rows, but how do you do that in SQL? I know how to do it VB.NET etc, movenext etc. But there isn't a way to do that in SQL?

I am trying to suss this out myself as much as I can, but if someone can give me a hint on how you cycle through a row at a time, modify that row and then move to the next I would be really grateful :)

Thnx
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-26 : 12:00:00
Four possibilities:

You could use a UDF that returns the n'th piece of a string for a given separator instead of PARSENAME. It won't be particularly fast because UDF calls (at least in SQL Server 2000) are slow, and because it will have to make multiple passes over the string to find each successive piece. But it's pretty straightforward. For example:

CREATE FUNCTION dbo.Piece (@s varchar(8000), @sep varchar(8000), @pn int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @i int, @sl int
SET @i = 1
SET @sl = DATALENGTH(@sep)
WHILE @pn > 1
BEGIN
SET @i = CHARINDEX(@sep, @s, @i)
IF @i = 0 BREAK
SET @i = @i + @sl
SET @pn = @pn - 1
END
RETURN
CASE
WHEN @i = 0 THEN
'' -- run out of pieces: could return NULL here, instead
ELSE
SUBSTRING(@s, @i, COALESCE(NULLIF(CHARINDEX(@sep, @s, @i), 0), 8000) - @i)
END
END
GO

UPDATE PartTable
SET f1 = dbo.Piece(Part, '-', 1),
f2 = dbo.Piece(Part, '-', 2),
f3 = dbo.Piece(Part, '-', 3),
f4 = dbo.Piece(Part, '-', 4),
f5 = dbo.Piece(Part, '-', 5)


You could extend Kristen's approach by another piece.

If you're using SQL Server 2005, you can CROSS APPLY the table with the return value of a table-valued Split function (though you'll then need to pivot the output into multiple columns).

It's possible to take a similar approach with a tally table, counting the number of separators before each character, but it's messy, and slow if the strings between separators are long.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-11-26 : 12:32:31
"You could extend Kristen's approach by another piece"

Kristen has a piece of code that generates this stuff, so you could ask Kristen to generate a bigger piece

Kristen
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-11-26 : 12:38:02
Oops! kristen had forgotten that he has some code to generate an UPDATE statement too!

DECLARE @I1 int, @I2 int, @I3 int, @I4 int, @I5 int
UPDATE U
SET
@I1 = CHARINDEX('-', part + '-')
, [title] = SUBSTRING(part + '-', 1, @I1-1)
, @I2 = CASE WHEN COALESCE(@I1, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX('-', part + '-', @I1+1), DATALENGTH(part + '-')) END
, [type] = CASE WHEN @I2 = 0 THEN NULL ELSE SUBSTRING(part + '-', @I1+1, @I2-@I1-1) END
, @I3 = CASE WHEN COALESCE(@I2, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX('-', part + '-', @I2+1), DATALENGTH(part + '-')) END
, [option] = CASE WHEN @I3 = 0 THEN NULL ELSE SUBSTRING(part + '-', @I2+1, @I3-@I2-1) END
, @I4 = CASE WHEN COALESCE(@I3, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX('-', part + '-', @I3+1), DATALENGTH(part + '-')) END
, [mail] = CASE WHEN @I4 = 0 THEN NULL ELSE SUBSTRING(part + '-', @I3+1, @I4-@I3-1) END
, @I5 = CASE WHEN COALESCE(@I4, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX('-', part + '-', @I4+1), DATALENGTH(part + '-')) END
, [to] = CASE WHEN @I5 = 0 THEN NULL ELSE SUBSTRING(part + '-', @I4+1, @I5-@I4-1) END
FROM @Sales AS U

Kristen
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-26 : 13:03:33
Oh, that's rather neat. I hadn't thought of using variables to store the separator positions. I was struggling with derived tables and (because it's an UPDATE) losing!

But since you're returning NULL for pieces beyond the end, we can put the NULLs into the variables and then call SUBSTRING without the CASE statements:

DECLARE @I1 int, @I2 int, @I3 int, @I4 int, @I5 int
UPDATE U
SET
@I1 = CHARINDEX('-', part + '-')
, f1 = SUBSTRING(part + '-', 1, @I1-1)
, @I2 = NULLIF(CHARINDEX('-', part + '-', @I1+1), 0)
, f2 = SUBSTRING(part + '-', @I1+1, @I2-@I1-1)
, @I3 = NULLIF(CHARINDEX('-', part + '-', @I2+1), 0)
, f3 = SUBSTRING(part + '-', @I2+1, @I3-@I2-1)
, @I4 = NULLIF(CHARINDEX('-', part + '-', @I3+1), 0)
, f4 = SUBSTRING(part + '-', @I3+1, @I4-@I3-1)
, @I5 = NULLIF(CHARINDEX('-', part + '-', @I4+1), 0)
, f5 = SUBSTRING(part + '-', @I3+1, @I5-@I4-1)
FROM PartTable AS U

Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-26 : 13:06:07
Thanks chaps.

Arnold, I have a UDF which works a treat and returns a table of the split values thus requiring only one call per row, but I can't figure out how to get the values in the columns from the returned table into the row that called the UDF :(

Kristen, I'm trying to make sense of your code snippet, but it is discombobulating me :(
I can now see the way to go is in the update statement with multiple values in the set statement, but surely isn't one call to a UDF more efficient than multiple calls to charindex, coalesce, datalength and substring if used?

I am amazed that this is being such a problem to overcome especially as it would be so easy to do in .NET, but have to use Transact!

Why do they supply a cursor method if everyone says don't use it? And if so why have MS not devised a more efficient way to process at the individual row level.

I have learned loads tho from this problem and all received posts as well as rummaging thru this site. :)

I shall continue to try to decifer your code.....
Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-26 : 13:07:33
Oww just seen your post Arnold, a lot clearer to see and decifer, I shall have a ponder on it :)

Thnx
Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-26 : 13:09:47
Arnold! I did not know you could put other code in between the lines that set values in the set statement! Crap if I knew that we might have struck gold like....yonks ago!
Go to Top of Page

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2005-11-26 : 13:32:03
ARNOLD!!!

You are a star :) :) :) Thanks a million :)

I have not run your code yet, but have stepped thru it on paper and I see clearly how it works, very clever!!!

I would never have thought of doing it this was. I think the last line should be f5 = SUBSTRING(part + '-', @I4+1, @I5-@I4-1) and not f5 = SUBSTRING(part + '-', @I3+1, @I5-@I4-1) but am not complaining.

I have learned so much from this post, thanks to all who responded :)
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-26 : 13:41:59
well if i saw this post before i'd say:
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 8

but now it's useless, no?

Go with the flow & have fun! Else fight the flow
Go to Top of Page
    Next Page

- Advertisement -