What is the Null Character literal in TSQL?

by

There are two different behaviors in the Cade Roux’s answer: replacement is successful (when SQL collation is used) and unsuccessful (Windows collation is used). The reason is in type of collation used.

This behaviour was submitted to Microsoft nearly 4 years ago:

Q: When trying a replace a NUL character
with replace(), this works is the
value has an SQL collation, but not a
Windows collation.

A: This is due to the fact that 0x0000
is an undefined character in Windows
collations. All undefined characters
are ignored during comparison, sort,
and pattern matching. So searing for
‘a’ + char(0) is really searching for
‘a’, and searching for char(0) is
equivalent to empty string.

The way to handle undefined character
is a bit confusing, but this is the
way that Windows defined to sort them,
and SQL Server conforms with the
general Windows API.

In SQL collation, there is no notion
of undefined character. Each code
point is assigned a weight, that’s why
we don’t see a problem there.

but unfortunately, it is still undocumented.

So, it seems the only one solution is to change collation to SQL collation (e.g. SQL_Latin1_General_CP1_CI_AS may be used as well).

* I removed my previous answer as unnecessary

More Related Contents:

  1. Calculate a Running Total in SQL Server
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. Not equal != operator on NULL
  4. SET NOCOUNT ON usage
  5. How can I get column names from a table in SQL Server?
  6. How can I select the first day of a month in SQL?
  7. Subquery using Exists 1 or Exists *
  8. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  9. Select statement to find duplicates on certain fields
  10. Is it possible to specify condition in Count()?
  11. Getting only Month and Year from SQL DATE
  12. Update records in table from CTE
  13. WHERE Clause vs ON when using JOIN
  14. how can I Update top 100 records in sql server
  15. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  16. How can a LEFT OUTER JOIN return more records than exist in the left table?
  17. Count Number of Consecutive Occurrence of values in Table
  18. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  19. Pivot data in T-SQL
  20. Column name or number of supplied values does not match table definition
  21. Split one column value into multiple column values
  22. T-SQL – Aliasing using “=” versus “as” [closed]
  23. Know relationships between all the tables of database in SQL Server
  24. alternatives to REPLACE on a text or ntext datatype
  25. SQL poor stored procedure execution plan performance – parameter sniffing
  26. How to return a incremental group number per group in SQL
  27. Eliminate and reduce overlapping date ranges
  28. COALESCE Function in TSQL
  29. Convert bigint to datetime
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment