SQL - TEXTVALID() Function

---

---

The SQL TEXTVALID() function is used to verify the pointer value.

It accepts two parameters table.column and text_ptr, and checks whether the specified text pointer is valid or not. It returns 1, if and only if the specified text pointer is valid; else returns zero(0). It throws an error if the specified column is a different datatype(like varchar, int, etc.).

Note − The identifier for the text column must include the table name. You cannot use an update-text, write-text, or read-text without a valid text pointer.

Syntax

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

TEXTVALID ( 'table.column' ,text_ ptr ) 

Parameters

• table − It is the name of the table that will be used.
• column − It is a column of the table that will be used to verify.
• text_ptr − It is a text pointer that needs to be checked.

Return value

This function returns an integer value(1 for valid, 0 for non-valid).

Example

If the specified text pointer is a valid text pointer, the TEXTVALID() function returns 1.

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 verifies whether a valid text pointer exists for each value in the column PIN of the Customers table −

SELECT ID, NAME, PIN, TEXTVALID('CUSTOMERS.PIN', TEXTPTR(PIN)) AS PIN_VALID_POINTER FROM CUSTOMERS;

Output

The above SQL query produces the following output −

+----+------------+-------------------------------+
| ID | NAME       | PIN       | PIN_VALID_POINTER | 
+----+------------+-------------------------------+
|  1 | Ramesh     | 380001    |  1                | 
|  2 | Khilan     | 110006    |  1                |
|  3 | kaushik    | 325001    |  1                |
|  4 | Chaitali   | 400002    |  1                |
+----+------------+-----------+-------------------+

Example

You can also pass the TEXT datatype column as an argument to SQL TEXTPTR() function and use the TEXTVALID() function to verify whether a valid text pointer exists for each value in the column CITY of the Customers table −

SELECT ID, NAME, CITY, TEXTVALID('CUSTOMERS.CITY', TEXTPTR(CITY)) AS CITY_VALID_POINTER FROM CUSTOMERS;

Output

On executing the above statement, it will produce the following output −

+----+------------+---------------+--------------------+ 
| ID | NAME       | CITY          | CITY_VALID_POINTER | 
+----+------------+---------------+--------------------+ 
|  1 | Ramesh     | Jamalpur      |  1                 | 
|  2 | Khilan     | Chandni Chowk |  1                 |
|  3 | kaushik    | Aamli         |  1                 |
|  4 | Chaitali   | Aamli         |  1                 |
+----+------------+---------------+--------------------+

Example

If the specified text pointer is not a valid text pointer, the TEXTVALID() function returns 0.

The following SQL query verifies whether a valid text pointer exists for each value in the column NAME of the Customers table −

SELECT ID, NAME, TEXTVALID('CUSTOMERS.NAME', TEXTPTR(CITY)) AS NAME_VALID_POINTER FROM CUSTOMERS;

Output

+----+------------+--------------------+ 
| ID | NAME       | NAME_VALID_POINTER | 
+----+------------+--------------------+ 
|  1 | Ramesh     | 0                  |    
|  2 | Khilan     | 0                  |   
|  3 | kaushik    | 0                  |  
|  4 | Chaitali   | 0                  |   
+----+------------+--------------------+

Example

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

In the following example, we are passing the column SALARY(datatype decimal) as an argument to the TEXTPTR() function, and using the SQL TEXTVALID() function to verify whether a valid text pointer exists for each value in the column SALARY of the Customers table −

SELECT ID, SALARY,TEXTVALID('CUSTOMERS.SALARY', TEXTPTR(SALARY)) AS NAME_VALID_POINTER FROM CUSTOMERS;

Output

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

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