Split a non-delimited string into 48 character lengths

Question

Split a non-delimited string into 48 character lengths

jamey8420

Ten Centuries

Points: 1099
More actions
January 12, 2011 at 3:59 pm

#232191

Hello all,
I have a description column in a SQL table that I need to split up into 48 character long strings. The column does not have any deliminations in it; the only thing I have to go off of is the length. Once I have the description "cut up" into 48 character pieces, I can then populate another table (which will be used to create a text file for a data load)...the load is looking for a specific format of file; hence the 48 character length.
Looking through examples, I've found plenty of samples with comma, semi-colon, and period delimited files; but none based on a length. This was previously being done in an Access macro, checking for lengths and then doing a lengthy list of substrings and mid commands. I was hoping for a cleaner solution with moving this to SQL.
Has anyone ever ran into an issue like this?
Thanks!
Jamey

Viewing 6 posts - 1 through 5 (of 5 total)

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

dkschill Hall of Fame Points: 3375 More actions · Answer 1

I don't know of a great way to do it, but I do know you could write a function that could loop through each row and reduce the content of each row in a inner loop. This would not be a great way, but it is the way I would do it on a low traffic server.

WayneS SSC Guru Points: 95410 More actions · Answer 2

How does this work? (see attached code)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

jamey8420 Ten Centuries Points: 1099 More actions · Answer 3

Thanks Wayne.....that worked. I created another stored procedure that I could feed the description into and then populated my table from that.

jamey8420 Ten Centuries Points: 1099 More actions · Answer 4

Hi Wayne,

I wanted to pick your brain on this again, instead of creating a new post. Like I said, I am using your solution and it's working great. It was noticed that sometimes, the 48 character line length is splitting words up between two lines.

Is there an easy way to keep words together (and wrap them to the next line) if they would exceed the predetermined length of my line (in my case, 48 characters)? Thanks!

jamey8420

James Goodwin Hall of Fame Points: 3691 More actions · Answer 5

Jamie, create a tally table that has a column of integers from 0 to <some large number: say 8000>

Then You can write a query like

SELECT n, SUBSTRING(<rowname>,n, 48) as splitdescription

from DescriptionSource, Tally

WHERE n%48 = 0

AND n < datalength(<rowname>)

--

JimFive