SQL - TEXTPTR() Function

---

---

The SQL TEXTPTR() function is used to retrieve the pointer value of the text or images.

It accepts column as a parameter and returns a 16-byte pointer value of the current text, ntext, or images. A text pointer value is a unique varbinary(16-byte) value that indicates each text, ntext, or images column in each row in which LOB(large objects is a set of data types for storing large amounts of unstructured or semi-structured data) you are working with.

The retrieved text pointer value can be used to read, write, or update the existing text statements.

Syntax

Following is the syntax of the SQL TEXTPTR() function −

TEXTPTR ( column )

Parameters

• column − It is a text, ntext, or image from which the pointer value will be retrieved.

Return value

This function returns the pointer value of the text or images.

Example

If we pass the NTEXT datatype column of a table as an argument to the TEXTPTR() function, it will return the column's content pointer value.

Assume we have created a table with the name Customers using the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
ID INT NOT NULL,    
NAME VARCHAR (20) NOT NULL,    
AGE INT NOT NULL,    
ADDRESS CHAR (25) ,    
SALARY DECIMAL (18, 2),
PIN NTEXT,
CITY TEXT);

Now, let's insert some record into the Customers table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY, PIN, CITY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00, '380001', 'Jamalpur');
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY, PIN, CITY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00,'110006','Chandni Chowk' );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY, PIN, CITY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00,'325001', 'Aamli'); 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY, PIN, CITY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00,'400002', 'Kalbadevi' );

The following SQL query retieves the pointer value of the content of the column PIN(NTEXT) in the Customers table −

SELECT TEXTPTR(PIN) AS POINTER_VALUE FROM CUSTOMERS;

Output

The above SQL query produces the following output −

+------------------------------------+
| POINTER_VALUE                      |
+------------------------------------+
| 0xFEFFA10F00000000DA01000001000000 |
| 0xFEFFA30F00000000DA01000001000200 |
| 0xFEFFA50F00000000DA01000001000400 |
| 0xFEFFA70F00000000DA01000001000600 |
+------------------------------------+

Example

You can also pass the TEXT datatype column of a table as an argument to this function, it will return the pointer value of the content of the columns.

Considering the above Customers table, the CITY column is created with the datatype TEXT, so now let's pass the CITY column as an argument to the TEXTPTR() function to retrieve the pointer value of the content of the column.

The following SQL query retrieves the pointer value of the content of the column CITY(TEXT) in the Customers table −

SELECT TEXTPTR(CITY) AS POINTER_VALUE_OF_TEXT FROM CUSTOMERS;

Output

+------------------------------------+
| POINTER_VALUE_OF_TEXT              |
+------------------------------------+
| 0xFDFFA20F00000000DA01000001000100 |
| 0xFDFFA40F00000000DA01000001000300 |
| 0xFDFFA60F00000000DA01000001000500 |
| 0xFDFFA80F00000000DA01000001000700 |
+------------------------------------+

Example

If we pass the VARCHAR datatype column as an argument to this function, it will throw an error.

In the following example, we are passing the column NAME(datatype varchar) as an argument to the TEXTPTR() function to retrieve the pointer value of the column content.

SELECT TEXTPTR(NAME) AS POINTER_VALUE_OF_TEXT FROM CUSTOMERS;

Output

On executing the above statement, it produces the following output −

Argument data type varchar is invalid for argument 1 of textptr function.