A little SQL help (possibly)

[Raistlin]

Hi Guys,

Here's the problem.

Let's say I have a field in a table which is called "txtSentence"

Now, I have a record in my table where the value of txtSentence is:

"This Is My Sentence"

However, what I actually want to display when I look for that record is:

"This
Is
My
Sentence"

I'm assuming that there is a character, or a symbol, or something (anything) that I can put between each word to force a carriage return - something like:

"This<cr>Is<cr>My<cr>Sentence"

Can anybody tell me what I need to put in there please? It needs to be included within the field in the table, splitting it out afterwards would be far too complicated.

Thanks

[punky]

You can, but not in SQL. SQL functions are fairly limited, but include maths ones like max, min, avg, count, etc.

You'd have to get the value with a SELECT query as per normal, and then parse the result as you want it to display.

What DBMS/Language are you using with the SQL? VB? PHP? Access?

[Raistlin]

Cheers matey, but (as is always the way) I've just solved it.

I'm trying to put information into an Info Window in Google Maps, the info window uses HTML.....so.....all I have to do is include <BR> between each word and it gives me a new line

Thanks though.

[philip.j.fry]

For future reference there are special characters you need for newlines, these differ between windows, macs and *nixes.

There are two escape characters that you need to know, \n is the line feed (LF) character and \r is the carriage return (CR) character.

*nixes use just the LF character for a newline: e.g "This\nIs\nMy\nSentence"

Windows uses the CR followed by the LF character: e.g. "This\r\nIs\r\nMy\r\nSentence"

Macs use just the CR. e.g. "This\rIs\rMy\rSentence"

These are normal characters and will store fine in a database. Most modern programming languages have a constant that represents the newline character for the system you are using.

[Raistlin]

Thanks for that. I tried \r but the database kept putting another \ in front of it.

I didn't try \r\n though, that's probably where I was going wrong.....

[greencreeper]

Bit late but... You shouldn't store formatting - just data. Formatting is done at the point the viewer views. That way you (or the viewer) can change the formatting dependant on, say, who's viewing it or how it's viewed.

[Raistlin]

Originally Posted by greencreeper

Bit late but... You shouldn't store formatting - just data. Formatting is done at the point the viewer views. That way you (or the viewer) can change the formatting dependant on, say, who's viewing it or how it's viewed.

Needs to be stored unfortunately, if it's not the different values all bleed into one on one long line and it throws the Info Box for the Marker out

[punky]

BTW in case you are missing out on something obvious here Raist:

SQL can store carriage returns and whatever you use to query your DB with, can handle it.

For example, you can have a LONGTEXT or VARCHAR in MySql which is linked a textarea HTML element. If you put carriage returns in your textarea, MySql will save them, but when you display them again, say with PHP, HTML takes over and ignores them because it doesn't see <br>s. However, don't forget you can use the <pre> </pre> HTML element tags, which ignores standard HTML formatting so carriage returns are outputted. The <pre> styles can be set with stylesheets or style tags just like any other HTML element.

[Richard M]

Originally Posted by punky

BTW in case you are missing out on something obvious here Raist:

SQL can store carriage returns and whatever you use to query your DB with, can handle it.

For example, you can have a LONGTEXT or VARCHAR in MySql which is linked a textarea HTML element. If you put carriage returns in your textarea, MySql will save them, but when you display them again, say with PHP, HTML takes over and ignores them because it doesn't see <br>s. However, don't forget you can use the <pre> </pre> HTML element tags, which ignores standard HTML formatting so carriage returns are outputted. The <pre> styles can be set with stylesheets or style tags just like any other HTML element.

Sounds like he's using MSSQL to me though...

[greencreeper]

Originally Posted by Raistlin

Needs to be stored unfortunately, if it's not the different values all bleed into one on one long line and it throws the Info Box for the Marker out

It's not possible to format the data after you retrieve it from the database, but before you display it?

[Paddy1]

Store the individual words rather than the sentence. e.g.


My Table
-----------
SentenceId
WordNo
Word

Data
SentenceId WordNo Word
1 1 This
1 2 is
1 3 my
1 4 sentence

Then

Select Word
From my_table
Where SentenceId = 1
Order By WordNo