October 18, 2010 at 5:14 am
I recently had to help someone out with a data migration issue. The legacy (source) system was storing all client forenames in a single column as space delimited data. The task was to select each of these forenames as an individual column. Obviously each person could have any number of forenames up to a maximum limit. But the number of forenames per person is unknown. Therefore they needed a function or procedure or some method of selecting a single row of data and splitting each source “forename” column into 6 forename columns (target). This was the solution I came up with, can anyone improve on this? Maybe a more elegant solution without the use of a UDF?
(Copy paste the code below and run it in SSMS. It should work without any problems and will help you understand the problem a little better. The code creates a test table, inserts a few lines of test data, creates the function, then selects the data with and without the use of this function.)
-- create test table
CREATE TABLE [dbo].[Test_Split_Function](
[Surname] varchar(50) NULL,
[Forename] varchar(500) NULL
)
GO
-- insert some test data
INSERT INTO [dbo].[Test_Split_Function]
([Surname],[Forename])
VALUES ('Bell','Alexander Graham')
INSERT INTO [dbo].[Test_Split_Function]
([Surname],[Forename])
VALUES ('Poe','Edgar Allen')
INSERT INTO [dbo].[Test_Split_Function]
([Surname],[Forename])
VALUES ('Jones','John Paul')
INSERT INTO [dbo].[Test_Split_Function]
([Surname],[Forename])
VALUES ('Plant','Robert')
INSERT INTO [dbo].[Test_Split_Function]
([Surname],[Forename])
VALUES ('Surname','Forename1 Forename2 Forename3 Forename4 Forename5')
GO
-- create the function
CREATE FUNCTION [fnNameSplitter] (@Position VARCHAR(1),
@FullNameList VARCHAR(500))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @NameList VARCHAR(200),
@StartPos INT,
@Length INT,
@NamePos INT,
@Delimiter VARCHAR(1),
@Forename VARCHAR(50)
DECLARE @TempNameList TABLE (
NamePosition INT,
Forename VARCHAR(50))
SET @StartPos = 0
SET @Length = 1
SET @NameList = @FullNameList
SET @Delimiter = ' '
SET @NamePos = 0
WHILE @Length > 0
BEGIN
SET @NamePos = @NamePos + 1
SET @Length = @Length + ( (SELECT charindex(@Delimiter, [Forename],
@Length
+ 1)
FROM [Test_Split_Function]
WHERE [Forename] = @NameList) - @Length
)
IF @Length > 0
BEGIN
INSERT @TempNameList
VALUES(@NamePos,
substring(@NameList, @StartPos + 1,
( @Length - @StartPos )))
SET @StartPos = @StartPos + ( @Length - @StartPos )
END
IF @Length = 0
BEGIN
INSERT INTO @TempNameList
VALUES (@NamePos,
substring(@NameList, @StartPos + 1,
( len(@NameList) - @StartPos )))
END
END
SET @Forename = (SELECT Forename
FROM @TempNameList
WHERE NamePosition = @Position)
RETURN @Forename
END
GO
-- test the function
-- first select unsplit names
SELECT * FROM [dbo].[Test_Split_Function]
-- now use function to split names
SELECT Surname,
dbo.[fnNameSplitter] (1, [Forename]) as Forename1,
dbo.[fnNameSplitter] (2, [Forename]) as Forename2,
dbo.[fnNameSplitter] (3, [Forename]) as Forename3,
dbo.[fnNameSplitter] (4, [Forename]) as Forename4,
dbo.[fnNameSplitter] (5, [Forename]) as Forename5,
dbo.[fnNameSplitter] (6, [Forename]) as Forename6
-- add as many forename columns as necessary
FROM [dbo].[Test_Split_Function]
October 18, 2010 at 5:25 am
Aha. There are similarities in this code for what I did as an article today. Using spaces to alter character case. In this situation, though, I'd advise looking at the responses to my article which are somewhat more elegant than what I did.
The CTE solution is particularly good. No RBAR. You may need to make adjustments.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply