TEXT Entries Into Separate Fields

Question

TEXT Entries Into Separate Fields

JuanBob

SSCertifiable

Points: 5059
More actions
May 23, 2005 at 10:44 pm

#163834

I have a table that has a TEXT field of varying lengths. I want to put each line of the TEXT field as a new row of another TEMP table.
Example of TEXT field contents:
Apples
Oranges
Pears

I want to extract each line and insert into another table as a separate record. Again, the TEXT field contents can be 100 lines or 10 lines.
Any help would be appreciated!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

I would do this in some front end scripting language. That's much easier.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

Franks right; splitting an entry on vbCrLf is not all that easy; you might need to modify something like the SPLIT function found here on sqlservercentral.com:

http://www.sqlservercentral.com/scripts/contributions/835.asp

the split function works great when splitting on a single character. a double character, like vbCrLf produces ugly results note thechar that appears to be a space before oranges and pears...but it's not.:

declare @STR varchar(500)

declare @delimiter varchar(10)

set @delimiter=char(10) + char(13)

set @STR= 'apples' + char(10) +char(13)

+ 'oranges' + char(10) +char(13)

+ 'pears' + char(10) +char(13)

select * from dbo.split(@str,@delimiter)

create table fruits(fruitname varchar(30) )

insert into fruits

select * from dbo.split(@str,@delimiter)

select * from fruits

|apples

| oranges

| pears

select ltrim(fruitname) from fruits

|apples

| oranges

| pears

here is the SPLIT function slightly modified to take the length of the seperator value into play, so it resolves the above issue:

CREATE FUNCTION SPLITPLUS (

@str_in VARCHAR(8000),

@separator VARCHAR(4 )= ',' )

RETURNS @strtable TABLE (strval VARCHAR(8000))

AS

BEGIN

DECLARE

@Occurrences INT,

@Counter INT,

@tmpStr VARCHAR(8000)

SET @Counter = 0

IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator

SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences

BEGIN

SET @Counter = @Counter + 1

INSERT INTO @strtable

VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr) + len(@separator),8000)

IF DATALENGTH(@tmpStr) = 0

BREAK

END

RETURN

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JuanBob SSCertifiable Points: 5059 More actions · Answer 3

Thanks for the help! I have 7.0 and had to use a SP in order to do this. It's working and now just trying to integrate the results from a web page.

Thanks!