MS SQL Server Tools - Mysterious text in query result

Author

Mysterious text in query result

BigMan2001

2006-03-24, 7:39 am

Hi,

I have a database with a column defined as varchar(100) called Surname.
When I use Query Analyzer to retrieve data from the column and display as
text, I get strange text appearing for one of the records. Thus, the field
contains "Wilson" but when I run the following:

SELECT '''' + Surname + '''', Surnname FROM tblManagers
WHERE FirstName = 'Glenn'

I get the following result:

'Wilson d g e d w i t h t h e a ' Wilson

Where has the "d ged with the a" come from. If I show the results in a grid
the final apostrophe is missing from the first part of the query:

'Wilson

If I run a query using ADO and show the result on a web page, the extra text
is not shown.

Any ideas what is going on here?

Glenn

Erland Sommarskog

2006-03-26, 8:26 pm

[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]

BigMan2001 (BigMan2001@discussi
ons.microsoft.com) writes:
> I have a database with a column defined as varchar(100) called Surname.
> When I use Query Analyzer to retrieve data from the column and display
> as text, I get strange text appearing for one of the records. Thus, the
> field contains "Wilson" but when I run the following:
>
> SELECT '''' + Surname + '''', Surnname FROM tblManagers
> WHERE FirstName = 'Glenn'
>
> I get the following result:
>
> 'Wilson d g e d w i t h t h e a ' Wilson
>
> Where has the "d ged with the a" come from. If I show the results in a
> grid the final apostrophe is missing from the first part of the query:
>
> 'Wilson
>
> If I run a query using ADO and show the result on a web page, the extra
> text is not shown.

Looks like some junk data slipped in, and there is a NUL character hiding
there. See what

SELECT convert(varbinary(10
0), Surname) FROM tblManagers
WHERE FisttName = 'Glenn'

returns.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Sponsored Links