May 1, 2003 at 1:54 pm
What is the best way of getting a comma-delimited value out of a text field and display it value by value?
For example, In TABLE_A, there are 3 records each of which stores a comma-delimited value. SELECT THE_TEXT FROM TABLE_A produces this result:
THE_TEXT
=========
A,B,C,D
E
F,G
I would like to insert each value within that field into TABLE_B so that SELECT * FROM TABLE_B will produce the following result.
THE_VALUE
=========
A
B
C
D
E
F
G
Is that possible?
Thanks in advance,
Billy
/* --- cut here --- */
USE TEMPDB
GO
BEGIN TRAN
SET NOCOUNT ON
CREATE TABLE TABLE_A(THE_ID INT IDENTITY(1,1), THE_TEXT TEXT);
CREATE TABLE TABLE_B(THE_VALUE CHAR(1));
INSERT INTO TABLE_A(THE_TEXT) VALUES('A,B,C,D');
INSERT INTO TABLE_A(THE_TEXT) VALUES('E');
INSERT INTO TABLE_A(THE_TEXT) VALUES('F,G');
SELECT * FROM TABLE_A;
SET NOCOUNT OFF
ROLLBACK;
GO
/* --- cut here --- */
May 2, 2003 at 7:06 am
Don't really know if there is a best way. It depends on volume and type of data. You only gave example of 3 rows with one character data. The only way I know is to use loops, either a cursor and loop for each char or the following for a set based solution.
Note, this solution will remove the text from the text fields in TABLE_A.
declare @CT int
set @CT = 1
while @CT > 0
begin
insert into TABLE_B select SUBSTRING(THE_TEXT,1,CHARINDEX(',',THE_TEXT)-1) from TABLE_A where CHARINDEX(',',THE_TEXT)>0
update TABLE_A set THE_TEXT = SUBSTRING(THE_TEXT,CHARINDEX(',',THE_TEXT)+1,DATALENGTH(THE_TEXT)-CHARINDEX(',',THE_TEXT)+1) where CHARINDEX(',',THE_TEXT)>0
select @CT = count(*) from TABLE_A where CHARINDEX(',',THE_TEXT)>0
end
insert into TABLE_B select THE_TEXT from TABLE_A where DATALENGTH(THE_TEXT)>0
select * from TABLE_B order by THE_VALUE
Far away is close at hand in the images of elsewhere.
Anon.
May 2, 2003 at 9:53 am
Like David said, it depends on the volume of data. If the total size of all the fields in your query concatenated together is less than ~8kb, then you could concatenate them all together like this:
Declare @s-2 varchar(8000)
Set @s-2 = ''
Select @s-2 = @s-2 + ',' + THE_TEXT from TABLE_A
Set @s-2 = right(@s, len(@s)-1)
Then you can loop through this string and separate the values using charindex and a pointer.
Jay Madren
Jay Madren
May 2, 2003 at 10:01 am
Nice one Jay, did not think of that one. Probably make cleaner code as well.
Far away is close at hand in the images of elsewhere.
Anon.
May 2, 2003 at 11:53 am
Thanks David and Jay.. I am looking for the best way to address the worst case scenario (ie. huge number of records and text chars are greater than 8000).... I think that David's method is a lot closer to what I am looking for because it can handle accumulated text values greater than 8000. However, I may run into problems if for one of the records has a single text value greater than 8000 because the SUBSTRING will convert the value in the text field into a character field (for example: PRINT SUBSTRING(REPLICATE('X',8000) + REPLICATE('Y',10),7995,10) produces XXXXXX and not XXXXXXYYYY).
Sincerely,
Billy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply