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

sql-server database tsql clob
tomriddle_1234 picture tomriddle_1234 · Mar 13, 2013 · Viewed 73.1k times · Source

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.

Answer

tomriddle_1234 picture tomriddle_1234 · Mar 13, 2013

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 ;

Related questions

Altering column size in SQL Server

How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)

Read More
Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema. I'm almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate = OBJECTPROPERTY( …

Read More
What does 'COLLATE SQL_Latin1_General_CP1_CI_AS' do?

I have an SQL query to create the database in SQLServer as given below: create database yourdb on ( name = 'yourdb_dat', filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf', size = 25mb, maxsize = 1500mb, filegrowth = 10mb ) log …

Read More