database new line problem

[pieandpeas]

Hi, I've got a table with a column which is frequently used to store paragraphs, some with new lines in between. for example


a aaaaa aa aaaaaaa.
bbb bb.
cc ccccccccccccc.

I'd like to filter these results in vb, so that the new lines (or whatever it is seperating the lines) is removed, making it so that i can instead have a cell, or a representation of a cell on a page as one long string, spaces and full stops still included.

e.g

a aaaaa aa aaaaaaa.bbb bb. cc ccccccccccccc.


does anyone have an idea of how to do this, without changing the actual database formatting.

i use a piece of code to remove commas -

Dim strcomments() As String
Dim comments As String = ""
strcomments = array(29, n).ToString.Split(",")
comments = String.Join("", strcomments)

would it be possible to do this with this code, only instead of removing a ' , ' remove the new lines generated by someone copying and pasting paragraphs into a cell.

[jamesd0142]

Quick option, possibly select all rows from the table, into strings, and say

str = string1 + string2

???

[pieandpeas]

Quick option, possibly select all rows from the table, into strings, and say

str = string1 + string2

???

most cells in the database are multiline (strings spanning more than one new) and are unique to a row. so that wouldnt be possible :-(

[jamesd0142]

Well see if this helps,

DECLARE @a varchar(8000)
SELECT @a=COALESCE(@a, '') + col01 + space(1) FROM (SELECT distinct (col01) from Dataset_Rows) as a
SELECT rtrim(ltrim(@a)) AS [MyValues]
 

it allows you to select all results in a single row.

result = col01 col02 col03...

----------------- OR --------------------

If i understand you correctly...

Can't you get the data into vb as it stands then use the mid/substring methods to cut it apart?

[pieandpeas]

hi thanks for the reply, it would be easier for me to use the 'mid/substring' methods you are talking about, how does this work, i've only used splits on strings before.


thanks

[jamesd0142]

The mid function is simple:

dim a as string
a = mid(str, startpossition, length)
 

So if you have:

string Text = "abcdef"

you could pick out "cde" like this:

dim a as string
a = mid(Text,3,3)
 

Now you can also say:

dim a as string
a = mid(Text,3,len(text)-2)
 

so you could go from possition 3 to the end of the string etc.
len(text) = (length of text string) which is 6 characters in length.

this help at all?