In this article, I described how to use the TEXTPTR Function in SQL Server. The text pointer value can be used in ReadText and WriteText statements. The ReadText statement is used to read or retrieve the text data instead of the select statement. The WriteText statement is used to write large of amounts of data to a "text" field or to modify the entire text data. So let's have a look at a practical example of how to use ReadText and WriteText statements with TEXTPTR Function in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
TEXTPTR Function
The TEXTPTR Function returns a pointer value to the text, ntext, or image column in varbinary format. The retrieved text pointer value can be used in READTEXT and WRITETEXT statements. TEXTPTR returns a 16-byte binary string, we recommend declaring a local variable to hold the text pointer and then use the variable with READTEXT and WRITETEXT statements.
Syntax
The syntax of the TEXTPTR function is as follows :
TEXTPTR ( column )
Column - Column is the text, ntext, or image column that will be used.
Creating a table in SQL Server
Now we create a table named EmployeeTable.
Create table EmployeeTable
(
EmpID int,
EmpName varchar(30),
EmpSalary int,
EmployeeFile Text
)
Go
insert into employeeTable values('1','Smith','200000','Mrs. Smith has worked in Infosys since 22/7/2010')
Go
Select * from employeeTable
The following is the sample data for the employee Table.
![Employee-Table-In-SQL-Server.jpg]()
ReadText statement with TEXTPTR Function
The ReadText statement is used to read or retrieve text data instead of the select statement. The general syntax of the ReadText statement is:
Syntax
READTEXT table_name.col_name
pointer_name offset size
[HOLDLOCK]
Size - Size specifies the limit of the number of bytes of data to be returned.
Example
Declare @pointer varbinary(30)
Select @pointer =TEXTPTR (EmployeeFile)from employeeTable
Where EmpID=1
ReadText employeeTable.EmployeeFile @pointer 1 20
Output
![ReadText-statement-in-SQL-Server.jpg]()
WriteText statement with TEXTPTR Function
The WriteText statement is used to write large of amounts of data to a "text" field or used to modify all the text data. The general syntax of the WriteText statement is:
WRITETEXT table_name.col_name
pointer_name [with log] data
Example
Declare @pointer varbinary(30)
Select @pointer =TEXTPTR (EmployeeFile)from employeeTable
Where EmpID=1
Writetext employeeTable.EmployeeFile @pointer 'Rohatash'
Go
Select * from EmployeeTable
Output
![WriteText-statement-in-SQL-Server.jpg]()