Split the String

Question

Split the String

yuvipoy

SSChampion

Points: 10469
More actions
December 23, 2013 at 1:32 am

#280627

Split the string into two
If the String lenght is 27
27- for 1 string
27*2=54 for 2 string
27*3=71 for 3 string
3data combained it is 71 i need to split those data in two "71 as 27 and 54 or 54 and 27"
how to split this 71 as 27 and 54 or 54 and 27 ?

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

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

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

Please post some sample data and the expected output. It's very difficult to tell what you want from your description.

^{“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

yuvipoy SSChampion Points: 10469 More actions · Answer 2

I/P

'0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ'

Divide by 3;

i need it as

O/P

'0ABCDEFGHIJKLMNOPQRSTUVWXYZ' -1

'0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ' -2

String len =71(27+54);

I know one string len can not exceed 27

say the string is now 27*4 =108

I/P

'0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ'

Divide by 2;

O/P

'0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ' -2

Divide by 3;

O/P

'0ABCDEFGHIJKLMNOPQRSTUVWXYZ' -1

'0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ' -3

Lowell SSC Guru Points: 323518 More actions · Answer 3

I modified this from somethign i saved in my snippets that is associated to the same issue: split a string on a specific number of character lengths.

if you search for "wordwrap" here on SSC there are a number of other solutions for the same issue.

declare @SplitLength INT =27

-- See Jeff Moden's article

-- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.

-- NOTE! A permanent tally table will always be MUCH faster

-- than this inline one. See the above article to create your own!

;WITH

Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),

SampleData (TheString)

AS

(

SELECT

'qghoaownbuorwnq3mqevno4qno4qgn4wuoin4tygnwoinv3qn7931f318jg420b0i4nb4nv31u9qnv3q'UNION ALL

SELECT '0ABCDEFGHIJKLMNOPQRSTUVWXYZ' UNION ALL

SELECT '0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ' UNION ALL

SELECT '0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ0ABCDEFGHIJKLMNOPQRSTUVWXYZ'

)

SELECT

N,

TheString,

TextData = SUBSTRING(TheString, ((N-1)*@SplitLength)+1, @SplitLength)

FROM SampleData

CROSS JOIN Tally

WHERE N-1<=8000/@SplitLength

AND SUBSTRING(TheString, ((N-1)*@SplitLength)+1, @SplitLength) > ''

ORDER BY TheString,N;

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!