More SET SQLFORMAT fun in SQLcl

13 Comments

1. 

   Robin 9 years ago Reply

   Hi Jeff,

   is it possible to remove the left and right enclosure, so that the output will look like:

   C1;C2;C3
   1;John;Doe

   Otherwise i have to trim the output.

   Thanks

   Robin

   • 

     bruno 8 years ago

     Hi Jeff,

     I have the same doubt. Can you help us?

     Thanks
     Bruno

   • 

     thatjeffsmith Post Author 8 years ago

     not in SQLcl – but if you do it in the GUI, you can export a result set and set your string enclosures to null in the Export dialog

2. 

   Jørgen Rasmussen 9 years ago Reply

   Hi Jeff!

   Uh oh – I’m one of those pesky europeans insisting comma is decimal point, so my default for NLS_NUMERIC_CHARACTERS is always comma/point.

   I’m quite fond of “set sqlformat” – but quite often I run into problems if I don’t consider my NLS_NUMERIC_CHARACTERS settings.

   Observe this (SQLcl: Release 4.2.0.15.349.0706):

   SQL> SELECT 1 A, 2 b, 3.4 c FROM dual;

   A B C
   ———- ———- ———-
   1 2 3,4

   Everything is nice and as I usually want my data displayed on the console when it contains numbers with decimals.

   SQL> SET sqlformat delimited , ” ”
   SQL> SELECT 1 A, 2 b, 3.4 c FROM dual;

   “A”,”B”,”C”
   1,2,3,4

   Notice suddenly 4 columns instead of 3

   I always have to remember:
   ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ‘.,’;

   SQL> SET sqlformat delimited , ” ”
   SQL> SELECT 1 A, 2 b, 3.4 c FROM dual;

   “A”,”B”,”C”
   1,2,3.4

   And then toggle back afterwards:
   ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ‘,.’;

   My wish could be that SQLcl either:

   a) numeric columns with contents conflicting with delimiters are enclosed as a string: 1,2,”3,4″

   or

   b) delimiter character is automatically switched to point and then back again after execution of statement

   Thank you for a great product
   Regards
   Jørgen

3. 

   Alex 9 years ago Reply

   Hi,

   I’m new to SQLcl, I’m exploring it’s use to migrate a large amount of clob (xml) data to Postgres because of it’s formatting features. Unfortunately, right out of the gate I’m noticing it’s slapping double quotes around the data. Is there anyway to modify this behavior? I would expect strings to be single quotes, and numbers to have neither.

   Thanks.

   • 

     thatjeffsmith Post Author 9 years ago

     I’m sorry, I can’t help you move to Postgres.

4. 

   ricardo fonseca 9 years ago Reply

   Hi Jeff,

   I have been using quite a bit sqlcl so far and the options to export data in various format and it works great !

   Now in regards of viewing the data, I found that sometimes it takes some time to view the information you need. I explain myself ., I have tables with multiple columns ( more than 80 – 100 ) . Sometimes I m “browsing” the tables data without knowing which field have the information I want.

   In such scenario I found mysql / mariadb command line \G option to be really intresting by somehow unpivoting record data attributes to horizantally presentation it gives you a grasp of what ‘s inside very fast so afterwards you can proceed with a more selective attributes approach.

   I don’t know if it’s possible or something but it I think it will be GREAT to ad this capability to sqlcl . Maybe by adding a new sqlformat ? Personnaly I will call it “unpivot” 😉

   MariaDB [mysql]> select * from db\G
   *************************** 1. row ***************************
   Host: localhost
   Db: crossword
   User: crossword
   Select_priv: Y
   …..
   …..

   Thanks for reading.

   Ricardo

   • 

     thatjeffsmith Post Author 9 years ago

     Indeed – I proposed we add this format using this approach. You can do it ‘by hand’ today though.

   • 

     ricardo fonseca 9 years ago

     Nice , I ll try that !

     BTW : Just to let you know , on your site the inputbox below search posts ( the google search thingy ) when you write text on it the color is white and as the background is white as well . It s hard to read what we are typing

5. 

   R Eamon 9 years ago Reply

   It would be nice to:

   * control the record delimiter (perhaps that is set another way?)
   * don’t enclose a field if it does not contain a record or field delimiter or an enclosure char (left and right are almost always the same–indeed I’ve never seen them not be the same); or the corollary: only enclose a column in a record if the data in that record column contains a character with special meaning (the delimiters and enclosures)

   • 

     Jason 8 years ago

     I agree. Is there any way to get rid of the enclosure characters all together?

6. 

   ricardo fonseca 9 years ago Reply

   Hi Jeff,

   Thanks for the post, Is this part of the sqlcl new release made available on the dec15th?

   I use the occasion to wish you a nice christmas time and happy new year!

   Thank you

   Ricardo

   • 

     thatjeffsmith Post Author 9 years ago

     Yes, and thank you!!