Need to break large text field into rows
-
I have a table with a text field that can be from 1 to more than 7000 chars. I need to break it into 3000 char. increments. I would like to avoid the use of a UNION query.
The data table has two fields:
ID int
BigText text
The result I would like would have three columns:
ID int,
SpliceNumber int
BigTextSplice varchar(1000)
I have been experimenting with derived tables with little success.
Help.
Just to show what I have done I see the view definition below (it uses a UNION in the derived table). This view is working in SQL Server 2000; however, it will need to be monitored to see that when the field sizes grows It will be updated ... I need a better approach ...
CREATE VIEW dbo.MyView AS
SELECT TOP 100 PERCENT
ID,
SpliceNumber,
BigTextSplice,
DATALENGTH(isnull(BigTextSplice,'')) AS Splice_Length
FROM (
SELECT ID, 1 AS SpliceNumber,CAST(SUBSTRING(BigText, 1, 3000) AS VARCHAR(3000)) AS BigTextSplice
FROM dbo.MyBigTextTable
UNION
SELECT ID, 2 AS SpliceNumber,CAST(SUBSTRING(BigText, 3001, 3000) AS VARCHAR(3000)) AS BigTextSplice
FROM dbo.MyBigTextTable
WHERE (DATALENGTH(BigText) > 3000)
UNION SELECT ID, 3 AS SpliceNumber,CAST(SUBSTRING(BigText, 6001, 3000) AS VARCHAR(3000)) AS BigTextSplice
FROM dbo.MyBigTextTable
WHERE (DATALENGTH(BigText) > 6000)
UNION SELECT ID, 4 AS SpliceNumber,CAST(SUBSTRING(BigText, 9001, 3000) AS VARCHAR(3000)) AS BigTextSplice
FROM dbo.MyBigTextTable
WHERE (DATALENGTH(BigText) > 9000)
UNION SELECT ID, 5 AS SpliceNumber,CAST(SUBSTRING(BigText, 12001, 3000) AS VARCHAR(3000)) AS BigTextSplice
FROM dbo.MyBigTextTable
WHERE (DATALENGTH(BigText) > 12000))Splice_Query
ORDER BY ID, SpliceNumber
Edited by - A Narro on 12/13/2003 5:59:27 PM
-
I'm not sure if this helps because it loops through your table and your data within a stored procedure.
CREATE PROC usp_SplitMyBigText AS
SET NOCOUNT ON
declare @lId Int
Declare @lLength Int
declare @lCount Int
CREATE TABLE #tmp_BigSplice (
ID int,
SpliceNumber int
BigTextSplice varchar(3000)
)
SET @lId = 0
WHILE @lId IS NOT NULL
BEGIN
SELECT @lId = MIN(@lID)
FROMdbo.MyBigTextTable
WHEREId > @lId
IF @lId IS NOT NULL
BEGIN
SET @lCount = 1
SELECT@lLength = DATALENGTH ( BigText )
FROM dbo.MyBigTextTable
WHERE Id = @lId
WHILE @lLength > 0
BEGIN
INSERT #tmp_BigSplice (ID , SpliceNumber , BigTextSplice )
SELECT @lId , @lCount , CAST(SUBSTRING(BigText,(@lCount - 1)*3000 + 1,3000) AS VARCHAR(3000))
FROM dbo.MyBigTextTable
WHERE Id = @lId
SET @lCount = @lCount + 1
SET @lLength = @lLength -3000
END
END
END
SELECT * FROM #tmp_BigSplice
DROP TABLE #tmp_BigSplice
========================
He was not wholly unware of the potential lack of insignificance.
-
How about....
CREATE VIEW dbo.MyView AS
SELECT TOP 100 PERCENT
ID,
SpliceNumber,
BigTextSplice,
DATALENGTH(isnull(BigTextSplice,'')) AS Splice_Length
FROM (
SELECT ID, SpliceNumber, CAST(SUBSTRING(BigText, StartPos, 3000) AS VARCHAR(3000)) AS BigTextSplice
FROM dbo.MyBigTextTable
Cross Join (
Select 1 as SpliceNumber, 1 As StartPos
UNION ALL Select 2, 3001
UNION ALL Select 3, 6001
UNION ALL Select 4, 9001
UNION ALL Select 5, 12001
) Splits
WHERE (DATALENGTH(BigText) > 12000))Splice_Query
ORDER BY ID, SpliceNumber
Once you understand the BITs, all the pieces come together
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply