SELECT WHERE produces strange results....
-
Hello:
If I enter the following, I get data:
SELECT TOP (1000) [ID]
,[Jobs_ID]
,[ProjectNumber]
,[QTY]
,[ProductName]
,[ComponentProduct]
,[Designer]
,[Assigned]
,[StartDate]
,[ERD]
,[ToProductionDate]
,[CompleteDate]
,[Notes]
FROM [ResourcePlanning].[dbo].[JobDetail]
Yet when I add the last line either this way:
WHERE ProjectNumber = '16-2506'or as Design Editor seems to prefer:
WHERE ProjectNumber = N'16-2506'The field type for ProjectNumber is nchar(10)...
Thank you for any help. To me this should be so simple.
Steve Anderson
-
You don't state what issue happens when you add the WHERE. [You don't need the N' and shouldn't use it.]
Perhaps there's some other character in the column besides just '16-2506'. Look at the ASCII for all bytes of that column in one of the rows that wasn't SELECTed with the WHERE present (assuming that is the issue).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Seems to prefer this, but I'm not sure how spaces consistently ended up in the data:
SELECT ProjectNumber, ProductName
FROM JobDetail
WHERE (ProjectNumber LIKE '%16-2506%')I suppose I can live with this though...
So SQL 101, sorry for the remedial question.Steve Anderson
-
So, still need to know what you as asking about. You really haven't stated a problem or question.
-
No data was returned with the first query.
Steve Anderson
- stephen.aa - Monday, July 2, 2018 3:02 PM
There is probably an "unprintable" character like a carriage return, line feed, or tab. I find the simplest way to figure it out is to convert the troublesome column to VARBINARY and then read the hex code to find the wayward character, but I'm also a bit of a geek, so that may not work for you.
Despite what Scott says, you SHOULD use the N. That specifies that you are entering a Unicode string. You should get in the habit of doing that, because it can cause very significant differences if your string contains Unicode characters, because it will convert those characters to ASCII, and may not preserve the correct value when converted back.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Monday, July 2, 2018 3:12 PM
I would only use the N'' when the column I am comparing to is declared as NCHAR, NVARCHAR, or NTEXT (Deprecated).
- drew.allen - Monday, July 2, 2018 3:12 PM
No, you shouldn't, unless the literal string you are entering contains special characters that need Unicode. Using a Unicode literal against a non-Unicode column can cause serious performance issues because SQL will implicitly cast the column to Unicode, effectively rendering a non-sargable search argument. If the column is Unicode, SQL will implicitly cast the literal to Unicode, but that's perfect and causes no issue at all.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Monday, July 2, 2018 4:11 PM
Actually, it won't.
DECLARE @TestValue1 NVARCHAR(32) = N'안녕하세요'
,@TestValue2 NVARCHAR(32) = '안녕하세요';
SELECT @TestValue1, @TestValue2;SELECT '안녕하세요',N'안녕하세요'
DECLARE @TestTable TABLE(DataValue NVARCHAR(32));
INSERT INTO @TestTable
VALUES(N'안녕하세요'),('안녕하세요');SELECT * FROM @TestTable AS [tt];
SELECT * FROM @TestTable AS [tt] WHERE [tt].[DataValue] = '안녕하세요';
SELECT * FROM @TestTable AS [tt] WHERE [tt].[DataValue] = N'안녕하세요';If you have a literal with UNICODE characters you need to precede the string with an N.
- Lynn Pettis - Monday, July 2, 2018 4:21 PM
I covered that, yes, IF the string actually contains Unicode chars, you need to use the N'. If it does not, you don't. SQL does indeed implicitly add an "N'" to the string, but the parser has already parsed and stored the non-Unicode data, causing it to be ?s instead of the actual data. That's unavoidable because of the steps SQL goes through in query prep and exec.
We had a massive performance/deadlocking issue with some purchased software because their query app stuck "N'" in front of every literal, and one of the columns was not Unicode. I've seen people just slap "N'" on literals "just in case", and that's a very bad practice. So, if the literal does not contain special chars, I say don't use Unicode for it.
DECLARE @TestTable TABLE(DataValue NVARCHAR(32));
INSERT INTO @TestTable
VALUES(N'안녕하세요'),(N'안녕하세요'),('ABC');
SELECT * FROM @TestTable AS [tt] WHERE [tt].[DataValue] = 'ABC';SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Monday, July 2, 2018 5:01 PM
This:
We had a massive performance/deadlocking issue with some purchased software because their query app stuck "N'" in front of every literal, and one of the columns was not Unicode. I've seen people just slap "N'" on literals "just in case", and that's a very bad practice. So, if the literal does not contain special chars, I say don't use Unicode for it.
Had this issue with our in-house developed application. The cause was two fold, actually, a decision to change all CHAR/VARCHAR columns to NCHAR/NVARCHAR in order to store UNICODE data even where no UNICODE data would be stored. I managed to convince them to NOT change the primary report key columns to NVARCHAR, they are defined as VARCHAR(36) to hold a character representation of guids (don't go into why that is a bad idea as I understand the arguments both pro and con and understand why they used guids because of replication) which could be generated in the application or the database and if in the application couldn't be stored in a column defined as uniqueidentifier (it wouldn't convert to be stored in a MS SQL database). The problem was the version of Cold Fusion being used at the time was all or nothing. All parameters had to be varchar or nvarchar when passed to the database. Deadlocks between unrelated tables, massive non-clustered index scans; it was like SQL Server was running around like a chicken with its head cut off. After two weeks of constantly asking why the ReportKey values were nvarchar and getting nothing, the engineering working the problem finally decided to "flip the switch" and change all the parameters to varchar. Amazing, the deadlocks vanished, index scans dropped significantly, index seeks went up; it was as if SQL Server sat down and said I can finally work the way I need to work.
An upgrade to a newer version of Cold Fusion allowed the engineers to be more granular in setting data types for parameters and those issues are gone.
All of this comes down to using the appropriate data types and avoiding the data type mismatches. If the data type in the database is NCHAR/NVARCHAR use N'' for strings, if CHAR/VARCHAR don't.
- Lynn Pettis - Monday, July 2, 2018 5:20 PM
The real issue comes when you change nvarchar to varchar. We had one developer who insisted on making everything nvarchar. He was also adamant about specifying N' on strings, even when the string was "plain" chars. When we changed the columns to varchar, no more index seeks, of course, since SQL had to implicitly convert the column to nvarchar to match the nvarchar literal.
So, again, to me, if the literal string does not contain unicode chars, don't make it a unicode string, regardless of what the current format of the column is. I guess unless it's virtually certain the column type will never change, such as a person's name.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher - Tuesday, July 3, 2018 7:31 AM
I guess we will have to agree to disagree. If a column is defined NCHAR/NVARCHAR then any literal should be coded UNICODE, period. If you change the data type from NCHAR/NVARCHAR to CHAR/VARCHAR then you change any literal strings that may be used as well. Use the same data types. Oh, and something I found out, it also depends on the collation of the server/database/column if an index will be used or not when implicit data conversions occur between non-Unicode and Unicode strings. Using the appropriate data types is still better.
-
This may be a little off topic, so if so just say so.
The query statement below works fine:
SELECT ProjectNumber, ProductName
FROM JobDetail
WHERE (ProjectNumber LIKE '%16-2506%')Ultimately, I am using this data in a C# entity framework program, using LINQ.
My understanding is, the equivalent to the above query in LINQ is:
var qryProductName = from b in dt_JobDetail.AsEnumerable()
orderby b.Field<String>("ProductName")
where (b.Field<String>("ProductName") != null && b.Field<String>("ProjectNumber").Contains(cboProjectNo.Text.Trim()))
select b.Field<String>("ProductName");
var lstProductName = qryProductName.Distinct().ToList();cboProductName.DataSource = lstProductName;
cboProductName.DisplayMember = "ProductName";However this too is not producing results. Is this a bad translation from SQL to LINQ?
Thanks again, for all the useful comments. I had no idea what N even was.
Steve Anderson
- stephen.aa - Monday, July 2, 2018 3:02 PM
Since you said in the original post that the column definition was NCHAR(10), SQL Server will put spaces at the end. Did you try this WHERE clause with only a trailing wildcard?
WHERE ProjectNumber LIKE '16-2506%'You usually don't want to have a leading wildcard in a LIKE statement so that the database can potentially use an index that starts with the column ProjectNumber.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply