Category name:sql

Appending data to a sql text or ntext column

Today I came accross a problem where nvarchar data got larger than the maximum that can be stored in a record. I had to convert to a ntext column and append the data to it.

I quickly found out that I needed to use the UPDATETEXT function in combination with the TEXTPTR and DATALENGTH functions.

When you are working with the ntext type then the output of DATALENGTH must be divided by two to get the string length as it returns the length of the data in bytes.

DECLARE @TextToAppend nvarchar(max)
SET @TextToAppend = 'Append this piece of text'

DECLARE @pointer binary(16)
DECLARE @length int

	@pointer = TEXTPTR(MyLargeTextColumn)
	,@length = DATALENGTH(MyLargeTextColumn)/2 -- Needed for ntext
	ID = @ID

UPDATETEXT MyTableWithLargeTextColumn.MyLargeTextColumn @pointer @length 0 @TextToAppend


The above SQL code appends the text @TextToAppend to column MyTableWithLargeTextColumn.MyLargeTextColumn for the record with @ID.

Mssql: Cannot use bitwise operators on varbinary or numeric

I want to perform bitwise operations on a varbinary colum. As it contains binary data I though that it would work but it didn’t

This example works: 

declare @a int
declare @b int

set @a = 1
set @b = 2

select @a | @b

But this wont:

declare @a varbinary(100)
declare @b varbinary(100)

set @a = 1
set @b = 2

select @a | @b

The same problem with numeric data type. So that currently leaves me without a mssql solution. I guess I have to fallback to code to perform the conversion that I need.

  • Recent Posts
  • Recent Comments
  • Archives
  • Categories
  • Meta