How to one column string automatically moving to next column when limit is reached, during select statement in sql server 2008?

Question

1.00/5 (2 votes)

See more:

Hello,

How to one column string automatically moving to next column when limit is reached, during select statement in sql server 2008?

Please help me.

Thanks in Advance.

Ankit Agarwal
Software Engineer

Posted 20-May-13 21:47pm

ankit.agg1984

Updated 20-May-13 21:49pm

v3

Add a Solution

Comments

Maciej Los 21-May-13 3:59am

Please, be more specific and provide more details (example data).

[no name] 21-May-13 4:03am

Text1|Text2|Text3|Text4
This is a example|||

select * from TextExample (what ? query for this)

Text1|Text2|Text3|Text4
Thi|s a|Exam|ple

Erik Rude 21-May-13 4:13am

Do you know in advance the capacity of each column?
If so you can use some of the string manipulation functions, if not you probably have to look at doing some PL/SQL stored proc dure stuff.

Your example could still do with a bit of formatting. Then please use improve question instead of the comments.

gvprabu 21-May-13 4:34am

Give some sample data... and explain more about your issue in this.

[no name] 21-May-13 5:00am

Suppose our column size only 256 character, so How to one column string automatically moving to next column when limit is reached, during select statement in sql server 2008?

when execute select query so my data explicit after with 256 character reached in each column, our column have four during select statement query.

2 solutions

Solution 1

Your question makes no sense, because SELECT statement does not needs to split long string into 256 char length string.

Have a look at example:

SQL

DECLARE @longstring NVARCHAR(MAX)

SET @longstring = N'This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. 
					This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. 
					This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. 
					This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. 
					This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. 
					This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string. This is very long-long string.'


SELECT @longstring AS [LongString], LEN(@longstring) AS LengthOfLongString

If you want to insert long string into 256 limit column, you need to use SQL String functions[^] like: LEFT(), SUBSTRING(), RIGHT() to split long string into 4 columns.

Posted 21-May-13 3:36am

Maciej Los

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-05-21T03:49:00

Solution 2

Hi,

You can use SUBSTRING function as follows...
But I don't think so, Where you will use this...

SQL

SELECT SUBSTRING(Col_Name,1,256) 'ResultCol1',
    CASE WHEN LEN(Col_Name) > 256 THEN SUBSTRING(Col_Name,256,512) ELSE NULL END 'ResultCol2',
    CASE WHEN LEN(Col_Name) > 512 THEN SUBSTRING(Col_Name,512,768) ELSE NULL END  'ResultCol3',
    /* ... Continue Up to your requirement :-) */
FROM table Name

GVPrabu

Posted 21-May-13 3:49am

gvprabu

Updated 21-May-13 3:51am

v3

Comments

[no name] 24-May-13 5:56am

Thanks Buddy!!..This is great help..Thumbs up :-))

gvprabu 24-May-13 6:00am

welcome :-)

[no name] 24-May-13 7:31am

Need more clarity on implementation of it!!
Actually we have a long string coming from .NET form and we want to store it in five columns of our data table in a way that 250 is the limit of each column cell. Suppose string is 1050 characters long then Text1 column takes first 250 characters and Text2 takes next 250 means from 251 to 500 and so on.
From the query that you posted it is taking 0-250 in Text1, 250-750 in Text2.
Please try to reply as early. Thanks.

gvprabu 24-May-13 7:39am

No Problem, U have to change the SUBSTRING Function as your wish. Thats all. If any clarifications get back to me.

gvprabu 24-May-13 7:40am

u have to add up to 5 levels

gvprabu 24-May-13 7:42am

SELECT SUBSTRING(Col_Name,1,256) 'ResultCol1',
CASE WHEN LEN(Col_Name) > 256 THEN SUBSTRING(Col_Name,256,512) ELSE NULL END 'ResultCol2',
CASE WHEN LEN(Col_Name) > 512 THEN SUBSTRING(Col_Name,512,768) ELSE NULL END 'ResultCol3',
CASE WHEN LEN(Col_Name) > 768 THEN SUBSTRING(Col_Name,768,1024) ELSE NULL END 'ResultCol4',
CASE WHEN LEN(Col_Name) > 1024 THEN SUBSTRING(Col_Name,1024,1280) ELSE NULL END 'ResultCol4',
/* ... Continue Up to your requirement :-) */
FROM table Name

gvprabu 24-May-13 7:44am

See have to fix like this as per your requirement. We can't do all your work. We will give u the logic. U should understand the logic and change based on your request.
All the best....

[no name] 24-May-13 9:23am

Thanks For Support,
We did it,
We will touch with in future.

gvprabu 24-May-13 9:27am

Sure, we ll be in Code Project :-) :-) :-)

Maciej Los 24-May-13 17:41pm

Good work!
+5