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 : 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

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

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

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', '-')

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)

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!

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

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

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

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?

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

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.

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

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

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

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.....

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

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!

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 :)

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources