Re: PARSENAME for 6 parts
- From: "Aaron Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx>
- Date: Wed, 05 Aug 2009 13:51:09 -0400
Sorry, let me try that again. I was merging two different functions and got
column names messed up. The are more efficient ways to split up strings in
SQL Server (including CLR) but not all will take order into account without
a loop.
USE tempdb;
GO
CREATE FUNCTION [dbo].[SplitStrings_Ordered]
(
@List NVARCHAR(MAX),
@Delimiter NCHAR(1) = ','
)
RETURNS @Strings TABLE
(
[Position] INT IDENTITY(1,1),
[Value] NVARCHAR(MAX) NOT NULL
)
AS
BEGIN
DECLARE
@Value NVARCHAR(MAX),
@Pos INT;
SET @List = @list + ' ';
WHILE DATALENGTH(@List)>0
BEGIN
SET @Pos = CHARINDEX(@Delimiter,@List);
IF @Pos = 0
SET @Pos = DATALENGTH(@List)+1;
SET @Value = LTRIM(RTRIM(LEFT(@List, @Pos-1)));
IF @Value > ''
INSERT @Strings([Value]) SELECT @Value;
SET @List = SUBSTRING(@List, @Pos+1, DATALENGTH(@List));
END
RETURN
END
GO
SELECT [Position], [Value]
FROM dbo.SplitStrings_Ordered('FS113/LECT/01-2009/Fall/Full', '/')
ORDER BY [Position];
GO
DROP FUNCTION dbo.SplitStrings_Ordered;
GO
On 8/5/09 1:40 PM, in article C69F3C4A.21991%ten.xoc@xxxxxxxxxxxxxx, "Aaron
Bertrand [SQL Server MVP]" <ten.xoc@xxxxxxxxxxxxxx> wrote:
PARSENAME() only supports up to 4 parts because it was designed to
deconstruct 4-part names. You can write your own function of course. This
one returns a resultset with two columns: position and value. You can use
as is or pivot if you need to present everything in one row.
USE tempdb;
GO
CREATE FUNCTION [dbo].[SplitStrings_Ordered]
(
@List NVARCHAR(MAX),
@Delimiter NCHAR(1) = ','
)
RETURNS @Items TABLE
(
[Position] INT IDENTITY(1,1),
[Value] NVARCHAR(MAX) NOT NULL
)
AS
BEGIN
DECLARE
@Item NVARCHAR(MAX),
@Pos INT;
SET @List = @list + ' ';
WHILE DATALENGTH(@List)>0
BEGIN
SET @Pos = CHARINDEX(@Delimiter,@List);
IF @Pos = 0
SET @Pos = DATALENGTH(@List)+1;
SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos-1)));
IF @Item > ''
INSERT @Items(Item) SELECT @Item;
SET @List = SUBSTRING(@List,@Pos+1,DATALENGTH(@List));
END
RETURN
END
GO
SELECT [Position], [Value]
FROM dbo.SplitStrings_Ordered('FS113/LECT/01-2009/Fall/Full', '/')
ORDER BY [Position];
GO
DROP FUNCTION dbo.SplitStrings_Ordered;
GO
On 8/5/09 1:28 PM, in article
677e1a30-d490-4f7c-8762-a07a5dfc1e3c@xxxxxxxxxxxxxxxxxxxxxxxxxxxx, "Janet"
<ckauvar@xxxxxxxxx> wrote:
Given an expression such as FS113/LECT/01-2009/Fall/Full I need to
break it into 6 distinct parts.
I've used the PARSENAME function in the past for similar activities
but my current expression has 6 parts and I know PARSENAME is limited
to 4 parts. Can someone recommend an alternate function?
Here is how PARSENAME might have worked, except it doesn't because
there are 6 parts:
PARSENAME(REPLACE(course.shortname, '/', '/'), 6) AS event_id,
PARSENAME(REPLACE(course.shortname, '/', '/'), 5) AS event_sub_type,
PARSENAME(REPLACE(course.shortname, '/', '/'), 4) AS section,
PARSENAME(REPLACE(course.shortname, '/', '/'), 3) AS academic_year,
PARSENAME(REPLACE(course.shortname, '/', '/'), 2) AS academic_term,
PARSENAME(REPLACE(course.shortname, '/', '/'), 1) AS session
Any recommendations for how to do this? Thanks!
.
- Follow-Ups:
- Re: PARSENAME for 6 parts
- From: Janet
- Re: PARSENAME for 6 parts
- References:
- PARSENAME for 6 parts
- From: Janet
- Re: PARSENAME for 6 parts
- From: Aaron Bertrand [SQL Server MVP]
- PARSENAME for 6 parts
- Prev by Date: Re: PARSENAME for 6 parts
- Next by Date: Re: Mirror backup on SQL Server 2005
- Previous by thread: Re: PARSENAME for 6 parts
- Next by thread: Re: PARSENAME for 6 parts
- Index(es):
Relevant Pages
|
