Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



Getting Column Values

Description

When you get a LOB type column, the data stored in a file to which the column refers will be displayed. You can execute an explicit type change by using CAST operator, CLOB_TO_CHAR( ) function, and BLOB_TO_BIT( ) function.

Remark
  • If the query is executed in CSQL, a column value (Locator) will be displayed, instead of the data stored in a file. To display the data to which a BLOB/CLOB column refers, it must be changed to strings by using  CLOB_TO_CHAR( ) function.
  • To use the string process function, the strings need to be converted by using the CLOB_TO_CHAR( ) function.
  • You cannot specify a LOB column in GROUP BY clause and ORDER BY clause.
  • Comparison operators, relational operators, IN, NOT IN operators cannot be used to compare LOB columns. However, IS NULL expression can be used to compare whether it is a LOB column value (Locator) or NULL. This means that TRUE will be returned when a column value is NULL, and if a column value is NULL, there is no file to store LOB data.
  • When a LOB column is created, and the file is deleted after data input, a LOB column value (Locator) will become a state that is referring to an invalid file. As such, using CLOB_TO_CHAR( ), BLOB_TO_BIT( ), CLOB_LENGTH( ), and BLOB_LENGTH( ) functions on the columns that have mismatching LOB Locator and a LOB data file enables them to display NULL.
Example

-- displaying locator value when selecting CLOB and BLOB column in CSQL interpreter

SELECT doc_t.doc_id, content, image FROM doc_t, image_t WHERE doc_t.doc_id = image_t.doc_id;

 

  doc_id                content               image

==================================================================

  'doc-1'               file:/home1/data1/ces_658/doc_t.00001282208855807171_7329  file:/ home1/data1/ces_318/image_t.00001282208855809474_7474

  'doc-2'               file:/home1/data1/ces_180/doc_t.00001282208854194135_5598  file:/

home1/data1/ces_519/image_t.00001282208854205773_1215

 

2 rows selected.

 

-- using string functions after coercing its type by CLOB_TO_CHAR( )

SELECT CLOB_TO_CHAR(content), SUBSTRING(CLOB_TO_CHAR(content), 10) FROM doc_t;

 

   clob_to_char(content)  substring( clob_to_char(content) from 10)

============================================

  'This is a Dog'       ' Dog'

  'This is a Cat'       ' Cat'

 

2 rows selected.

 

SELECT CLOB_TO_CHAR(content) FROM doc_t WHERE CLOB_TO_CHAR(content) LIKE '%Dog%';

 

   clob_to_char(content)

======================

  'This is a Dog'

 

SELECT CLOB_TO_CHAR(content) FROM doc_t ORDER BY CLOB_TO_CHAR(content)

 

   clob_to_char(content)

======================

  'This is a Cat'

  'This is a Dog'

 

-- an error occurs when LOB column specified in WHERE/ORDER BY/GROUP BY clauses

SELECT * FROM doc_t WHERE content LIKE 'This%';

SELECT * FROM doc_t ORDER BY content;