INF: How to Manipulate DBCS Data in a Text Field

• Microsoft SQL Server, version 6.5

SUMMARY

This article describes how to correctly manipulate double-byte character set (DBCS) data in text fields.

MORE INFORMATION

UPDATETEXT and WRITETEXT are powerful tools that allow users to modify existing text data through a text pointer. While using a text pointer, it is very common for users to accidentally split a double-byte character in half. This may result in "data corruption" from user's perspective. The following scripts demonstrate this common problem.

   USE pubs
   GO

   SET NOCOUNT ON

   DROP TABLE textTable
   GO

   CREATE TABLE textTable
   (
      id SMALLINT
         IDENTITY(1,1)
         PRIMARY KEY CLUSTERED,
      hexValues VARBINARY(20) NOT NULL,
      textField TEXT NOT NULL
   )
   GO

   DECLARE @hexValues VARBINARY(20)
   SELECT @hexValues = 0xa540a541a542
   INSERT textTable VALUES
      (@hexValues,
         CONVERT (VARCHAR(20), @hexValues))
   INSERT textTable VALUES
      (@hexValues,
         CONVERT (VARCHAR(20), @hexValues))
   GO

   SELECT * FROM textTable

   DECLARE @hexValues VARBINARY(20)
   SELECT @hexValues = 0xa543
   DECLARE @string VARCHAR(20)
   SELECT @string = CONVERT(VARCHAR(20), @hexValues)

   DECLARE @textPointer VARBINARY(16)
   SELECT @textPointer = TEXTPTR(textField)
      FROM textTable WHERE id = 2
   UPDATETEXT textTable.textField @textPointer 1 2 @string

   UPDATE textTable
      SET hexValues =
         CONVERT (VARBINARY(20), CONVERT(VARCHAR(20), textField))
      WHERE id = 2

   CREATE PROCEDURE IsThisLeadByteInCP950
      @string VARCHAR(255), @offset SMALLINT
   /* @string: is the target string that will be checked */
   /* @offset: is the 1-based starting position,         */
   /*          specified as the number of bytes (from    */
   /*          the start of the existing text value) to  */
   /*          skip.                                     */
   AS
      SELECT @string = RIGHT(REVERSE(@string), @offset)
      DECLARE @i SMALLINT
      SELECT @i = ASCII(@string)
      IF @i >= 129 and @i <= 254
      /* lead byte range: 0x81 to 0xFE */
         RETURN 1
      ELSE
         RETURN 0
   GO

   DECLARE @hexValues VARBINARY(20)
   SELECT @hexValues = 0xa540a541a542
   DECLARE @string VARCHAR(20)
   SELECT @string = CONVERT(VARCHAR(20), @hexValues)
   DECLARE @i SMALLINT
   EXEC @i = IsThisLeadByteInCP950 @string, 1
   IF (@i = 1)
      PRINT "It is a lead byte in code page 950, command aborted."
   ELSE
      PRINT "It is not a lead byte in code page 950, command continue..."