How to insert new line/line break in SQL statement

[Ioanna Katsanou]

Hello,

I want to insert a row in to an Oracle DB in the following form:

value1
bob
alice

and then select it as well.

Does anyone know how to achieve that?

For example:

update table_name field_to_update = 'bob /n alice'  where condition;

Thanks,
Ioanna

[Dave Tolls]

If you have a String that has that value in it already then just use a PreparedStatement and bind the variable in using the setString method.

[Tim Holloway]

Welcome to the Ranch, Ioanna!

As a general rule, it's not a good idea to put control characters into a database field. For one thing, databases are often shared between many systems, and when some of the systems are Linux/Unix/MacOS and some are Windows, there's the problem that not all OS's use the same character sequence to terminate a line. And, incidentally, if you attempt to output that value onto a web page, HTML ignores end-of-line characters so it wouldn't render right (you'd need an actual HTML newline tag).

If it's not appropriate to put each name in its own database row (record), then a common solution would be to use a non-control character as a separator. Popular selections include the pipe (vertical bar) character, comma, semicolon or colon. Pipe is especially popular, since it's not used as common punctuation and thus allows punctuation to be part of the data values. And if you're really sure, you can use a space. For that matter, any character will do as long as it's not ever going to be used as part of an actual data value.

Here's an example using pipe:

String sample = "John|Mary";
String[] items = sample.split("\\|");
for (String s : items ) {
  resp.print(s);
  resp.print("<br/>");
}

This would produce:
John<br/>Mary<br/>

Which would space vertically on a web page. And could be easily changed to:

String sample = "John|Mary";
String[] items = sample.split("\\|");
for (String s : items ) {
  printer.println(s);
}

If you wanted a text (printstream) output.

Note the odd construction "\\|" on the split method. This is because split takes a regex argument, and the pipe character normally has a special meaning in regular expressions (regexes). To remove that special meaning, you have to prefix it with an escape character (backslash), and since backslash has a special meaning to the Java compiler, you have to escape the backslash with another backslash. Yikes!