How to split line breaks into a new record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to split line breaks into a new record

Author

Topic

antman28
Starting Member

10 Posts

Posted - 2009-04-28 : 13:13:08

I have a table where some rows have attributes separated by line breaks. How can I run a query that creates a new record for an ID with multiple attributes?

Current layout...

ID Attribute
1 Blue
Red
2 Blue
3 Blue
Green
White
4 Blue
White

Desired layout...

ID Attribute
1 Blue
1 Red
2 Blue
3 Blue
3 Green
3 White
4 Blue
4 White

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-28 : 13:19:15

Handle this in your application and not in SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

antman28
Starting Member

10 Posts

Posted - 2009-04-28 : 13:29:24

This data lies in an Access DB. The issue has already been resolved for new entries. However, there are several past entries that need to be separated as explained above.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-28 : 13:35:08

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Change the commas to char(13) + char(10) and it will work fine.

antman28
Starting Member

10 Posts

Posted - 2009-04-28 : 20:25:56

I get an error message...

Syntax error (missing operator) in query expression 'ID <=Len(char(13) + char(10)' + Locations + 'char(13) + char(10)') AND SubString('char(13) + char(10)' + Locations + 'char(13) + char(10)' , ID - 1, 1) = 'char(13) + char(10)' AND CharIndex('char(13) + char(10)' , 'char(13) + char(10)' + Locations +".

My full statement reads...

SELECT FileName,
NullIf(SubString('char(13) + char(10)' + Locations + 'char(13) + char(10)' , ID , CharIndex('char(13) + char(10)' , 'char(13) + char(10)' + Locations + 'char(13) + char(10)' , ID) - ID) , '') AS Location
FROM Tally, tblPubs
WHERE ID <= Len('char(13) + char(10)' + Locations + 'char(13) + char(10)') AND SubString('char(13) + char(10)' + Locations + 'char(13) + char(10)' , ID - 1, 1) = 'char(13) + char(10)'
AND CharIndex('char(13) + char(10)' , 'char(13) + char(10)' + Locations + 'char(13) + char(10)' , ID) - ID > 0
INSERT INTO LocationsSplit SELECT FileName,
NullIf(SubString('char(13) + char(10)' + Locations + 'char(13) + char(10)' , ID , CharIndex('char(13) + char(10)' , 'char(13) + char(10)' + Locations + 'char(13) + char(10)' , ID) - ID) , '') AS Location
FROM Tally, tblPubs
WHERE ID <= Len('char(13) + char(10)' + Locations + 'char(13) + char(10)') AND SubString('char(13) + char(10)' + Locations + 'char(13) + char(10)' , ID - 1, 1) = 'char(13) + char(10)'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-28 : 21:04:16

Remove the single quotes around char(13) + char(10).

antman28
Starting Member

10 Posts

Posted - 2009-04-29 : 13:49:56

After removing the quotes I get the same message. If I remove everything starting with "INSERT INTO LocationsSplit..." it lets me save the query. When I open the query however, I get the message "Undefined function 'NullIf' in expression". Any thoughts? Sorry for all the questions, this project just has me stuck.
Where should I put the "INSERT INTO..." line?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-29 : 13:51:44

where are you doing this? are you using sql server db?

antman28
Starting Member

10 Posts

Posted - 2009-04-29 : 15:52:50

I'm doing it within MS Access

antman28
Starting Member

10 Posts

Posted - 2009-04-30 : 12:19:43

It would be easiest if I could run a query that would create the additional entries as needed.