How to show CLOB type in a SELECT in SQL Server?

Question

I have a table with one column of CLOB type data, they are all very short no more than 20 bytes, however I cannot see the actual string in the CLOB data.

For example if I use SELECT *, under the CLOB type every data is like:

CLOB, 8 Bytes
CLOB, 15 Bytes
CLOB, 9 Bytes

But I just want to see the content of the CLOB data.

I tried:

SELECT DBMS_LOB.SUBSTR(ClobColumnName, 20 ,1)

And it doesn't work, error is:

Error Code: 4121, SQL State: S1000
Cannot find either column "DBMS_LOB" or the user-defined function or aggregate "DBMS_LOB.SUBSTR", or the name is ambiguous.

So can I ask what's the syntax for direct display a CLOB data in a query?

I'm using SQL Server with dbVisualizer.

SQL Server does not have a `clob` datatype. What is the real column datatype? I assume `varbinary(x)`/`binary(x)`? If so why are you using that to store strings in the first place? — Martin Smith, Mar 13 '13 at 06:59
@MartinSmith, you are right, I'm new to these confusing different SQL languages. it's showing as a varchar(2005) data type with huge column size (2148473647) on column attribute list in dbVisualizer. I have to use this software since I'm using linux rather than Windows to manage SQL server... — tomriddle_1234, Mar 13 '13 at 22:21

score 31 · Accepted Answer · edited Dec 04 '15 at 07:56

31

I figured out one solution. There should be better ways, please show more possible solutions in the comments.

SELECT CAST(ClobColumnName AS VARCHAR(50)) AS ClobColumnName ;

edited Dec 04 '15 at 07:56

boutta

24,189
7
34
49

answered Mar 13 '13 at 05:12

tomriddle_1234

3,145
6
41
71

score 8 · Answer 2 · edited Jan 07 '18 at 13:52

8

I have table with one column has CLOB data type(1000K), after storing message/data into CLOB column and found one solution see the actual data in CLOB column.

  SELECT CAST(T.CLOB_COLUMNNAME AS VARCHAR(1000)) AS SAMPLEDATA 
  FROM TABLE_NAME AS T

The above query CAST the CLOB(Character Large Objects) into a normal String.

edited Jan 07 '18 at 13:52

Osama AbuSitta

3,918
4
35
51

answered Jun 20 '14 at 04:30

VINAYAK SHANAWAD

81
1
1

This response is much better (for me) than remembering to cast every time I select. – kianatennyson Nov 08 '18 at 20:44

score 3 · Answer 3 · answered Dec 02 '13 at 14:10

3

To see it in DbVis you just have to change it in the options. There is an entry for the display of CLOB columns.

answered Dec 02 '13 at 14:10

Midas1977

31
2

2

Tell us where is that setting and you get an upvote from me ;) – Ska Mar 07 '19 at 12:11
Pro (paid) version only. – Capn Sparrow May 29 '19 at 03:13

score 3 · Answer 4 · answered Aug 30 '16 at 14:29

3

I presume you are using jDTS driver to connect to the SQL Server. In the driver properties of the connection you can set the "USELOBS" to False to automatically cast them to string.

answered Aug 30 '16 at 14:29

NecKroM

61
4

score 1 · Answer 5 · answered Nov 25 '13 at 16:44

I had the same problem and solved it by using DBeaver (http://dbeaver.jkiss.org/) instead of dbVisualizer.

When I use DBeaver and do a select * from my SQLServer I can just double-click the CLOB in the result set and it opens in a new window with the content. Very slick.

How to show CLOB type in a SELECT in SQL Server?

5 Answers5

Linked