String to Paragraph.

Question

Post reply

String to Paragraph.

Kelvin Phayre

Ten Centuries

Points: 1358
More actions
July 11, 2011 at 8:09 am

#241325

Has anyone a procedure that will take a 150 character string and split it up into multiple smaller strings of length 60.
text150 char(150)
text60_1 char (60)
text60_2 char (60)
text60_3 char (60)
The split will be the last space before character 60 in text150 put in text60_1
Remainder of the first 60 chars + next 60 to text60_2 etc etc
EG
SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR USE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE. T-OFF FOR NEC SUDAN (Q No. 13)
text60_1=SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR
text60_2=USE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE.
text60_3=T-OFF FOR NEC SUDAN (Q No. 13)

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

this seems to work for me:

SELECT

SUBSTRING(text150,1,60) AS text60_1,

SUBSTRING(text150,61,60) AS text60_2,

SUBSTRING(text150,121,60) AS text60_3

FROM YourTable

SELECT

SUBSTRING(text150,1,60) AS text60_1,

SUBSTRING(text150,61,60) AS text60_2,

SUBSTRING(text150,121,60) AS text60_3

FROM (SELECT 'EG

SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR USE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE. T-OFF FOR NEC SUDAN (Q No. 13)

text60_1=SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR

text60_2=USE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE.

text60_3=T-OFF FOR NEC SUDAN (Q No. 13)

' as text150) x

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!

drew.allen SSC Guru Points: 76996 More actions · Answer 2

Lowell (7/11/2011)
this seems to work for me:

The OP asked to specifically break on spaces. This only seems to work, because this particular test string has spaces at positions 60 and 120.

This code will work in more cases.

SELECT

SUBSTRING(text150,1,BR1) AS text60_1,

SUBSTRING(text150,BR1+2,BR2) AS text60_2,

SUBSTRING(text150,BR1+BR2+3,60) AS text60_3

FROM (SELECT 'EG

SABRE THIS CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR USE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE. T-OFF FOR NEC SUDAN (Q No. 13)

text60_1=SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR

text60_2=USE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE.

text60_3=T-OFF FOR NEC SUDAN (Q No. 13)

' as text150) x

CROSS APPLY ( SELECT 60 - CharIndex(' ', Reverse(SubString(text150, 1, 60))) AS BR1 ) AS BR1

CROSS APPLY ( SELECT 60 - CharIndex(' ', Reverse(SubString(text150, BR1+2, 60))) AS BR2 ) AS BR2

It's possible that text that contains very long words (e.g., antidisestablishmentarianism) may cause the third string to truncate.

I should also mention that this sounds like a presentation issue, and should probably be left to the presentation layer.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

quan23 SSCommitted Points: 1506 More actions · Answer 3

I was playing around with using a set of variables to hold the positions of the spaces like this

select

'SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR USE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE. T-OFF FOR NEC SUDAN (Q No. 13)'

into #tmpString

declare @i1 int, @i2 int, @i3 int

set @i1 = (select 60-charindex(' ',reverse(substring(input,1,60))) from #tmpString)

set @i2 = (select @i1+60-charindex(' ',reverse(substring(input,@i1+60,60))) from #tmpString)

set @i3 = (select @i2+60-charindex(' ',reverse(substring(input,@i2+60,60))) from #tmpString)

select

substring(input,1,@i1) as text60_1, substring(input,@i1+2, @i2-@i1+1) as text60_2, substring(input,@i2+2,60) as text60_3

from #tmpString

But Drew's Cross Apply solution is much simpler and makes better use of native sql functionality

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Here's another APPLY solution:

DROP TABLE #Strings

CREATE TABLE #Strings (Longstring VARCHAR(150))

INSERT INTO #Strings (Longstring) values ('SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOR USE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE. T-OFF FOR NEC SUDAN (Q No. 13)')

INSERT INTO #Strings (Longstring) values ('SABRE a CIRCUIT BREAKER MOTORISATION SCHEMATIC DIAGRAM, FOx xSE WITH MICOM & A MULTI VOLTAGE SHUNT TRIP. EXTENSIBLE. T-xxF FOR NEC SUDAN (Q No. 13)')

;WITh Numbers AS (SELECT TOP 200 rn = ROW_NUMBER() OVER (Order BY NAME) from sys.columns)

SELECT

text60_1 = LEFT(s.longstring,P1.StringLength),

text60_2 = LEFT(STUFF(s.Longstring,1,P1.StringLength,''),P2.StringLength),

text60_3 = STUFF(s.Longstring,1,P1.StringLength+P2.StringLength,'')

FROM #Strings s

CROSS APPLY (SELECT StringLength = MAX(n.rn) FROM Numbers n WHERE SUBSTRING(Longstring,n.rn,1) = ' ' AND n.rn < 61) P1

CROSS APPLY (SELECT StringLength = MAX(n.rn) FROM Numbers n WHERE SUBSTRING(STUFF(Longstring,1,P1.StringLength,''),n.rn,1) = ' ' AND n.rn < 61) P2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

quan23 SSCommitted Points: 1506 More actions · Answer 5

Like the use of Stuff() there, and if you change the cte slightly to use this code which I have snagged from Jeff Moden's brilliant DelimitedSplit8K function it runs even quicker on my pc

(Original DelimitedSplit8k article http://www.sqlservercentral.com/articles/Tally+Table/72993/)

; WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(rN) AS (--==== This provides the "zero base" and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT 0 UNION ALL

SELECT TOP 200 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

)

Kelvin Phayre Ten Centuries Points: 1358 More actions · Answer 6

Kelvin Phayre

Ten Centuries

Points: 1358

July 11, 2011 at 10:03 am

#1351191

Sorted 😀

Many thanks.

You guys are the best.