adding line breaks to a string

[tieties]

Ok so what i want to do is break up a string after the lenght of the string is = to 160, then i want to add a line break..but all this needs to be done in the Select...can anyone help me with this

thankx in advance

select case len(COD_P2_Q1) 
        when 160 
        then COD_P2_Q1 + char(13) + COD_P2_Q1 
    end as COD_P2_Q1L1  
from Applications

this is what i have sofar and return a null and yes i know this is not correct, im new to sql

[pritaeas]

Why do you want to do this in the query? This is typically something that should be done by the code/script running this. Anyway, I think trying to write a function for this problem would be better. I assume that you want to add two line breaks if the string is longer than 320. Have a look at SUBSTRING.

[tieties]

i basically want to break up the string so it can move over to a new line on a page in ssrs...if its longer than 160 it goes out of the page size. ok thanks for the feedback will look into it

[tieties]

So I figured the substring out now my problem is when i cut off the sentence it cutts off sometimes in a middle of a word and i dont want that now i found a piece of code to check for the last space before the last word in a sentence
But i dont know how to put this all together.

REVERSE(RIGHT(REVERSE(COD_P2_Q1), len(COD_P2_Q1) - NULLIF(charindex(' ', REVERSE(COD_P2_Q1)),0)))        
@COD_P2_Q1 = isnull(substring(COD_P2_Q1, 1, 60), '')

this is my code now the @COD_P2_Q1 is where i cut the sentence off, can someone help me with this pleaze

[BitBlt]

Here's an ugly script that will illustrate the technique. Is it the best way? Who knows. But it works and should do what you want it to do.

declare @myString varchar(1000)
select @myString = 'Here is some text that contains more than 160 characters so I can test the line break thing for sql server reporting services objects and see if it will do whatever I want it to do'

select SUBSTRING(@myString, 1, 160) as 'First 160 Chars',
reverse(SUBSTRING(@myString, 1, 160)) as 'Reversed',
CHARINDEX(' ', reverse(SUBSTRING(@myString, 1, 160))) as 'Position of Last Space',
SUBSTRING(@myString, 1, 160 - CHARINDEX(' ', reverse(SUBSTRING(@myString, 1, 160))) + 1) 'First 160 Chars with No Cutoff Word',
SUBSTRING(@myString, 160 - CHARINDEX(' ', reverse(SUBSTRING(@myString, 1, 160))) + 1, LEN(@myString)) as 'Remainder of the sentence'

Note that this is only good for ONE line break (as Pritaeas mentions above). If you are clever, you can make a recursive function to have it work with any length string. I'll leave that to you if you wish to try it.

Hope this helps!

[iamthwee]

Yes this is a terrible idea.

The data you store in the db should be the master copy.
What happens if down the line you make you make your page size smaller or bigger? You're royally fubared.

Now a scripting function which renders the page can be changed at leisure and is highly flexible. Go with this for best practice.