I have a table like this:
| RowID | ProductDescription1 |
| ------------ | ------------ |
| 1 | 0296620300-0296620399; |
| 2 | 0296620400-0296620499;0296620500-0296620599; |
| 3 | 0296620600-0296620699;0296620700-0296620799; |
I want to become like this:
NewRowID | Start | End | SourceRowID |
---|---|---|---|
1 | 0296620300 | 0296620399 | 1 |
2 | 0296620400 | 0296620499 | 2 |
3 | 0296620500 | 0296620599 | 2 |
4 | 0296620600 | 0296620699 | 3 |
5 | 0296620700 | 0296620799 | 3 |
Now I have a function that can do splitting stuff which returning table :
ALTER FUNCTION [dbo].[ufn_stg_SplitString]
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)
--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0
Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))
Insert Into @ReturnTable(part)
Select @part
Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))
Select @iSpaces = charindex(@deliminator,@myString,0)
end
If len(@myString) > 0
Insert Into @ReturnTable
Select @myString
RETURN
END
I want to avoid using cursor if it's possible.
I am appreciated your comment/input.
2条答案
按热度按时间rryofs0p1#
First, this solution requires SQL Server 2005+. Second, at the bottom, I offer an alternate Split function which does not use a cursor. Third, here is a solution that does not rely on the values being of a specified length but instead that the delimiter is consistent:
And the Split function:
p4rjhz4m2#
SQL 2005/2008
Gives