December 6, 2010 at 3:03 pm
What do I have wrong here?
DECLARE @MappingIds NVarchar(Max)
SET @MappingIds = 'E371F678-45DC-4C83-9D91-286E2BD8ACCF,E371F678-45DC-4C83-9D91-286E2BD8ACCB,E371F678-45DC-4C83-9D91-286E2BD8ACCC'
SET @MappingIds = REPLACE(@MappingIds,'|',',') +','
----Flip csv into a table
;WITH
cteSplit AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)-1 AS RowNumber,
SUBSTRING(@MappingIds,N+1,CHARINDEX(',',@MappingIds,N+1)-N-1) AS Element
FROM Common.Tally
WHERE N < LEN(@MappingIds)
AND SUBSTRING(@MappingIds,N,1) = ',' --Notice how we find the comma
)
SELECT * FROM cteSplit
December 6, 2010 at 3:33 pm
You're pulling data from the comma forward, so you need a leading comma as well as a trailing one.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 6, 2010 at 8:35 pm
JKSQL (12/6/2010)
What do I have wrong here?
DECLARE @MappingIds NVarchar(Max)SET @MappingIds = 'E371F678-45DC-4C83-9D91-286E2BD8ACCF,E371F678-45DC-4C83-9D91-286E2BD8ACCB,E371F678-45DC-4C83-9D91-286E2BD8ACCC'
SET @MappingIds = REPLACE(@MappingIds,'|',',') +','
----Flip csv into a table
;WITH
cteSplit AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)-1 AS RowNumber,
SUBSTRING(@MappingIds,N+1,CHARINDEX(',',@MappingIds,N+1)-N-1) AS Element
FROM Common.Tally
WHERE N < LEN(@MappingIds)
AND SUBSTRING(@MappingIds,N,1) = ',' --Notice how we find the comma
)
SELECT * FROM cteSplit
I've got a little trick to show you but I first need to know, does it REALLY need to be able to handle NVARCHAR(MAX)???
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2010 at 2:25 pm
In this case a nvarchar(max) is not necessary but used for "Future" possibilities. So I can limit it.
December 8, 2010 at 7:15 am
Sorry for the delay... have been working some strange hours. On my way to work. Will get to this tonight if no one beats me to it (and they usually do, which helps).
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2010 at 9:55 am
I have a cool function that I use for just this reason:
Create a table function:
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(1000)
)
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0
THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1)
ELSE @String
END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0
THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String))
ELSE NULL
END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
then just call it like this:
DECLARE @MappingIds NVarchar(Max)
SET @MappingIds = 'E371F678-45DC-4C83-9D91-286E2BD8ACCF,E371F678-45DC-4C83-9D91-286E2BD8ACCB,E371F678-45DC-4C83-9D91-286E2BD8ACCC'
select * from ParseValues(@MappingIds,',')
December 9, 2010 at 12:33 pm
I know that function and love it. The result set should not look like that though.
Not this:
1
2
3
4
but
1 | 2
3 | 4
December 9, 2010 at 12:57 pm
This is actually what it needs to be. I finally got around to debugging it. I was missing a comma before and after. If there is a more optimal approach I am eager to hear it
DECLARE @MappingIds NVarchar(Max)
DECLARE @LCSRTempTable TABLE (
[RowId] int Not Null
,[OldId] int Not null
,[NewId] int Null)
--Format of csv - OldPKId,NewId|
SET @MappingIds = ',1,2,3,4,5,6'
SET @MappingIds = REPLACE(@MappingIds,'|',',') +','
----Flip csv into a table
;WITH
cteSplit AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)-1 AS RowNumber,
SUBSTRING(@MappingIds,N+1,CHARINDEX(',',@MappingIds,N+1)-N-1) AS Element
FROM Common.Tally
WHERE N < LEN(@MappingIds)
AND SUBSTRING(@MappingIds,N,1) = ',' --Notice how we find the comma
)
--INSERT INTO @LCSRTempTable ([RowId], [OldId],[NewId])
SELECT (RowNumber/2)+1 AS RowNumber,
MAX(CASE WHEN RowNumber%2 = 0 THEN Element END) AS Old,
MAX(CASE WHEN RowNumber%2 = 1 THEN Element END) AS TheNewId
FROM cteSplit
GROUP BY RowNumber/2
SELECT * FROM @LCSRTempTable
December 9, 2010 at 1:03 pm
Ah, you mean exactly as I stated in the initial comment to your q? 🙂
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 9, 2010 at 1:21 pm
Yeah when you said that I thought about it you are for sure correct thanks
December 9, 2010 at 6:53 pm
churlbut (12/9/2010)
I have a cool function that I use for just this reason:Create a table function:
CREATE FUNCTION [dbo].[ParseValues](@String varchar(8000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE
(ID int identity(1,1),
Val varchar(1000)
)
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0
THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1)
ELSE @String
END,
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0
THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String))
ELSE NULL
END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
then just call it like this:
DECLARE @MappingIds NVarchar(Max)
SET @MappingIds = 'E371F678-45DC-4C83-9D91-286E2BD8ACCF,E371F678-45DC-4C83-9D91-286E2BD8ACCB,E371F678-45DC-4C83-9D91-286E2BD8ACCC'
select * from ParseValues(@MappingIds,',')
Just a suggestion... the code you've posted is an mlTVF (MultiLine Table Valued Function) and it has a WHILE loop in it. Take a look at the following article and see why that's usually a bad idea.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 9, 2010 at 7:26 pm
JKSQL (12/9/2010)
This is actually what it needs to be. I finally got around to debugging it. I was missing a comma before and after. If there is a more optimal approach I am eager to hear it
DECLARE @MappingIds NVarchar(Max)DECLARE @LCSRTempTable TABLE (
[RowId] int Not Null
,[OldId] int Not null
,[NewId] int Null)
--Format of csv - OldPKId,NewId|
SET @MappingIds = ',1,2,3,4,5,6'
SET @MappingIds = REPLACE(@MappingIds,'|',',') +','
----Flip csv into a table
;WITH
cteSplit AS
(
SELECT ROW_NUMBER() OVER (ORDER BY N)-1 AS RowNumber,
SUBSTRING(@MappingIds,N+1,CHARINDEX(',',@MappingIds,N+1)-N-1) AS Element
FROM Common.Tally
WHERE N < LEN(@MappingIds)
AND SUBSTRING(@MappingIds,N,1) = ',' --Notice how we find the comma
)
--INSERT INTO @LCSRTempTable ([RowId], [OldId],[NewId])
SELECT (RowNumber/2)+1 AS RowNumber,
MAX(CASE WHEN RowNumber%2 = 0 THEN Element END) AS Old,
MAX(CASE WHEN RowNumber%2 = 1 THEN Element END) AS TheNewId
FROM cteSplit
GROUP BY RowNumber/2
SELECT * FROM @LCSRTempTable
Not more optimimal but a whole lot more convenient. First, make an iTVF (inline Table Valued Function) which is usually faster than an mlTVF. This is assuming you really do have a properly built Tally table in your "common" schema...
CREATE FUNCTION common.Split8K
--===== Define I/O parameters
(
@pString VARCHAR(7999),
@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N)-1 AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM common.Tally
WHERE N <= DATALENGTH(@pString)+1 --DATATLENGTH allows for trailing space delimiters
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
GO
After that, the good code you wrote can be easily warped into child's play without the need to add the leading comma...
DECLARE @MappingIds VARCHAR(8000)
SELECT @MappingIds = '1,2,3,4,5,6'
--Format of csv - OldPKId,NewId|
SELECT [RowID] = ItemNumber/2+1,
[OldID] = MAX(CASE WHEN ItemNumber%2 = 0 THEN Item ELSE '' END),
[NewID] = MAX(CASE WHEN ItemNumber%2 = 1 THEN Item ELSE '' END)
FROM common.Split8K(@MappingIds,',')
GROUP BY ItemNumber/2+1
Note that one of the "optimizations" is to steer away from any MAX datatype if you don't really need it.
I believe I'd pick a different name than "NewID", though. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply