November 7, 2003 at 6:45 am
I need help on this. This is a problem I just inherited from someone else. There is one table which has a text field/column. This column in the table A have multiple records stored as one field. example:
rec1: This is a test
rec2: This is a test2
rec3: This is a test3
In this table, all these records are stored as
rec1: This is a test rec2: This is a test2 rec3: This is a test3
with all these stored in one column. What I want to do is to parse this column into another table call table B where it will be broken into three separate records. I was thinking of using DTS but how will I be able to determine where record 1, 2, or 3 starts and ends in the column.
Help please. anyone
I have SQL Server 2k/SP 3/Win 2k sp 4
November 7, 2003 at 7:12 am
How many records are in your table A? You can write a loop to take each value and using patindex and substring, break it into smaller chuncks of data. Something like:
DECLARE @Value VARCHAR(8000), @NewValue VARCHAR(8000), @Begin INT
SELECT TOP 1 @Value=Field FROM TableA
WHILE @@ROWCOUNT>0
BEGIN
DELETE TableA WHERE Field=@Value
SET @Value=SUBSTRING(@Value,6,8000)
WHILE (SELECT PATINDEX('%REC_:%',@Value)) > 0
BEGIN
SET @Begin = (SELECT (PATINDEX('%REC_:%',@Value)))
SET @NewValue = LTRIM(SUBSTRING(@Value,1,@Begin-1))
SET @Value=SUBSTRING(@Value,@begin+6,8000)
INSERT INTO TableB SELECT @NewValue
END
INSERT INTO TableB SELECT @Value
SELECT TOP 1 @Value=Field FROM TableA
END
Brian
November 7, 2003 at 7:24 am
The problem with above is the 8k limit and if the numbers are > 1 digit. Using '%REC%:%' should cater for this.
The code below will give you the number of bytes for the first rec in the column.
(charindex(':', textcol,
charindex(' ', textcol)) - charindex(' ',
reverse(substring(textcol,1,charindex(':', textcol,
charindex(' ', textcol)) - charindex(' ', textcol)
)))) - 1
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply