CODEWITHSUNDEEP
c#sqlsql-serverdatabase
HectorLector
HectorLector

Reputation: 1911

MS SQL nvarchar id field contains spaces

I am working on a C# project which reads values from a MS SQL Server 2008 or 2008 R2 Server.

The query looks something like this:

string charge="1234567";
string command = string.Format("SELECT * FROM tableX WHERE chargeField = '{0}' ", charge);

chargeField is from type nvarchar(50) - do not ask me why - I cannot change the database. Also the entries are written to the database by another tool which I also cannot change. Sometimes the chargeField value will have leading or ending space characters in it, so my query will return nothing because "1234567" != " 1234567 "

However, when I write the query like this (without ' '):

string command = string.Format("SELECT * FROM tableX WHERE chargeField = {0} ", charge);

it works. Why does this even work - don´t i need the '' signs to indicate that the database field is a string? Can I safely change all my queries? Is this a "feature" from MS SQL Server and what exactly happens here.

EDIT

I solved the problem by using:

string command = string.Format("SELECT * FROM tableX WHERE RTRIM(LTRIM(chargeField)) = {0} ", charge);

Thanks.

Upvotes: 1

Views: 1256

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294227

The rules of Data Type Precedence:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence

Numeric/int precedence is higher than nvarchar, hence the query is the equivalent of:

SELECT * FROM tableX WHERE CAST(chargeField as INT) = {0}

Note that a query like this cannot use an index on chargeField.

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

The SQL Server tries to convert the fields accordingly. Without quotation marks, you're passing in a number, so SQL Server tries to convert the value in chargeField to a number, which works in spite of leading spaces.

This won't work if chargeField contains the value 12345a. This will lead to a conversion error. It is enough for one record in the table to contain such a value to get the error.

To be on the safe side, change your query to

... WHERE LTRIM(chargeField) = '{0}'

but be aware that this leads to a possible index on the field no longer being used. What you could still do is create an additional column that is filled using a trigger with the value of LTRIM(chargeField), put an index on that column and adapt your WHERE clause accordingly.

Upvotes: 1

Related Questions