I'm working on a tool to import data from one database to another. One requirement is that I have to split a string from one source field into three (shorter) fields at the target. If possible the string should be split at a space character. If the string doesn't fit completely into the target fields, the rest can be omitted.
Usually I would solve this using an UDF but unfortunately neither UDF's nor Stored Procedures are allowed in my scenario.
My source database has the following table:
CREATE TABLE dbo.Organisations
(
OrganisationID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
OrganisationName nvarchar(180) NOT NULL
/* More columns omitted for brevity */
)
This table contains company names, for example:
OrganisationID | OrganisationName ---------------+-------------------------------------------------------------------- 1 | Microsoft Corporation 2 | S&T System Integration & Technology Distribution Aktiengesellschaft
During the import the records of this table should be inserted into a stating table in the target database. The staging table looks like this:
CREATE TABLE dbo.OrgStaging
(
OrganisationID int NOT NULL,
Name1 nvarchar(50) NOT NULL,
Name2 nvarchar(50) NOT NULL,
Name3 nvarchar(50) NOT NULL
/* More columns omitted for brevity */
)
If I would simply use SUBSTRING to split the name I would end up in the staging table like this:
OrganisationID | Name1 | Name2 ---------------+---------------------------------------------------+----------------- 1 | Microsoft Corporation | 2 | S&T System Integration & Technology Distribution A|ktiengesellschaft
But I don't want to split in the middle of a word so I would like to have the result like this:
OrganisationID | Name1 | Name2 ---------------+---------------------------------------------------+------------------ 1 | Microsoft Corporation | 2 | S&T System Integration & Technology Distribution |Aktiengesellschaft
To achieve this I came up with the following rather complex query:
DECLARE
@MaxLen int = 50; -- Maximum length of a target column
WITH
SpacePositions AS
(
SELECT
O.OrganisationID,
CHARINDEX(' ', O.OrganisationName, 0) AS Position
FROM
SourceDB.dbo.Organisations O
UNION ALL SELECT
O.OrganisationID,
CHARINDEX(' ', O.OrganisationName, S.Position + 1) AS Position
FROM
SourceDB.dbo.Organisations O
INNER JOIN SpacePositions S
ON CHARINDEX(' ', O.OrganisationName, S.Position + 1) > S.Position
AND S.OrganisationID = O.OrganisationID
),
SplitPositions AS
(
SELECT
S.OrganisationID,
S.Position - 1 AS Position
FROM
SpacePositions S
WHERE
S.Position != 0
UNION SELECT
O.OrganisationID,
LEN(O.OrganisationName) AS Position
FROM
SourceDB.dbo.Organisations O
),
FirstChunk AS
(
SELECT
D.OrganisationID,
1 AS ChunkStart,
MAX(D.Position) AS ChunkEnd
FROM
(
SELECT
S.OrganisationID,
S.Position + 1 AS Position
FROM
SplitPositions S
WHERE
Position BETWEEN 1 AND @MaxLen
UNION SELECT
S.OrganisationID,
@MaxLen
FROM
SplitPositions S
WHERE
NOT EXISTS
(
SELECT
*
FROM
SplitPositions SI
WHERE
SI.Position BETWEEN 1 AND @MaxLen
AND SI.OrganisationID = S.OrganisationID
)
) D
GROUP BY
D.OrganisationID
),
SecondChunk AS
(
SELECT
C.OrganisationID,
C.ChunkEnd + 1 AS ChunkStart,
MAX(D.Position) AS ChunkEnd
FROM
FirstChunk C
INNER JOIN
(
SELECT
S.OrganisationID,
S.Position + 1 AS Position
FROM
SplitPositions S
INNER JOIN FirstChunk C
ON C.OrganisationID = S.OrganisationID
WHERE
S.Position BETWEEN C.ChunkEnd + 1 AND C.ChunkEnd + @MaxLen
UNION SELECT
S.OrganisationID,
C.ChunkEnd + @MaxLen AS Position
FROM
SplitPositions S
INNER JOIN FirstChunk C
ON C.OrganisationID = S.OrganisationID
WHERE
NOT EXISTS
(
SELECT
*
FROM
SplitPositions SI
WHERE
SI.Position BETWEEN C.ChunkEnd + 1 AND C.ChunkEnd + @MaxLen
AND OrganisationID = C.OrganisationID
)
) D
ON D.OrganisationID = C.OrganisationID
GROUP BY
C.OrganisationID,
C.ChunkEnd
),
ThirdChunk AS
(
SELECT
C.OrganisationID,
C.ChunkEnd + 1 AS ChunkStart,
MAX(D.Position) AS ChunkEnd
FROM
SecondChunk C
INNER JOIN
(
SELECT
S.OrganisationID,
S.Position + 1 AS Position
FROM
SplitPositions S
INNER JOIN SecondChunk C
ON C.OrganisationID = S.OrganisationID
WHERE
S.Position BETWEEN C.ChunkEnd + 1 AND C.ChunkEnd + @MaxLen
UNION SELECT
S.OrganisationID,
C.ChunkEnd + @MaxLen AS Position
FROM
SplitPositions S
INNER JOIN SecondChunk C
ON C.OrganisationID = S.OrganisationID
WHERE
NOT EXISTS
(
SELECT
*
FROM
SplitPositions SI
WHERE
SI.Position BETWEEN C.ChunkEnd + 1 AND C.ChunkEnd + @MaxLen
AND OrganisationID = C.OrganisationID
)
) D
ON D.OrganisationID = C.OrganisationID
GROUP BY
C.OrganisationID,
C.ChunkEnd
)
INSERT INTO dbo.OrgStaging
(
OrganisationID,
Name1,
Name2,
Name3
)
SELECT
O.OrganisationID,
LTRIM(RTRIM(SUBSTRING(O.OrganisationName, C1.ChunkStart, C1.ChunkEnd))),
LTRIM(RTRIM(SUBSTRING(O.OrganisationName, C2.ChunkStart, 1 + C2.ChunkEnd - C2.ChunkStart))),
LTRIM(RTRIM(SUBSTRING(O.OrganisationName, C3.ChunkStart, 1 + C3.ChunkEnd - C3.ChunkStart)))
FROM
SourceDB.dbo.Organisations O
INNER JOIN FirstChunk C1
ON C1.OrganisationID = O.OrganisationID
INNER JOIN SecondChunk C2
ON C2.OrganisationID = O.OrganisationID
INNER JOIN ThirdChunk C3
ON C3.OrganisationID = O.OrganisationID
ORDER BY
O.OrganisationID;
It works as desired but I'm wondering if this can be stated a little more compact. I tried to combine the CTE's FirstChunk, SecondChunk and ThirdChunk into one recursive CTE but that is not working because of the GROUP BY clause which is not allowed in recursive CTEs.
Can this be restated more compact or is it already the best I can get?
Name1,Name2,Name3? Is there also, or will there ever be Name4, Name5, etc...? Are you sure that's how you want to structure it? Perhaps a one-to-many relationship is something to consider? \$\endgroup\$