April 23, 2009 at 11:31 am
This is probably something to do with character sets but my google research is not coming up with an answer.
I imported an Excel Spreadsheet into a table. Every column is a varchar in this table. The problem is when I run a simple query like:
SELECT *
FROM MyTable
WHERE myColumn = 'Whatever'
It brings back nothing even though I can plainly see with my eyes that the value 'Whatever' is in thousands of the rows for that column.
I used the DIFFERENCE function on a row that I know has the value 'Whatever' and sure enough, it returned 4, which means there is some difference I am not aware of. I updated that column with the LTRIM(RTRIM(myColumn)) so make sure that spaces weren't giving me trouble.
Does this have something to do with a character set difference?
April 23, 2009 at 11:34 am
Depending on your method of import, Excel usually comes in as nvarchar rather than varchar. If that's the case, then you will need to search using the RTRIM() function:
SELECT * FROM MyTable WHERE RTRIM(MyColumn) = 'Whatever'
April 23, 2009 at 11:38 am
I already ran:
UPDATE MyTable
SET myColumn = RTRIM(LTRIM(myColumn))
to make sure there were no spaces interfering, so that's not the problem. I already checked it with both trim functions anyway and that didn't work.
April 23, 2009 at 11:43 am
Jesse McLain (4/23/2009)
Depending on your method of import, Excel usually comes in as nvarchar rather than varchar. If that's the case, then you will need to search using the RTRIM() function:
SELECT * FROM MyTable WHERE RTRIM(MyColumn) = 'Whatever'
The difference between varchar and nvarchar is not to do with trailing spaces. One's unicode, one's not. SQL will do implicit conversions as necessary, but if the column is varchar and the string literal it's been compared to is varchar (as it is here), such conversions won't be needed.
SQL ignores trailing spaces on string columns when matching, so RTRIM shouldn't change anything.
Easy to see -
CREATE TABLE str (
SomeString VARCHAR(10)
)
INSERT INTO str values ('abc')
INSERT INTO str values ('abc ')
SELECT * from str where somestring = 'abc'
(2 row(s) affected)
tnk, just to see exactly what's in that column, run this
SELECT '[' + mycolumn + ']'
FROM MyTable
WHERE myColumn LIKE '%Whatever%'
What does it return?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 11:45 am
Check the collation on the database and column.
Try using
WHERE UPPER(myColumn) = UPPER('Whatever')
OR
WHERE myColumn = 'Whatever' COLLATE Latin1_General_CI_AS
...just to test if it is a case sensitive collation issue
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 23, 2009 at 11:51 am
The difference between varchar and nvarchar is not to do with trailing spaces. One's unicode, one's not. SQL will do implicit conversions as necessary, but if the column is varchar and the string literal it's been compared to is varchar (as it is here), such conversions won't be needed.
I seem to remember reading somewhere that nvarchar was sensitive to trailing spaces (as in, it put them in automatically, similar to the char type), but this code shows that I'm wrong and you're right:
DECLARE @Temp TABLE (TempValue nvarchar(10))
INSERT INTO @Temp (TempValue) VALUES (N'Whatever')
SELECT * FROM @Temp WHERE TempValue = N'Whatever'
It successfully returns the inserted value.
April 23, 2009 at 11:52 am
Yeah, thanks for the response Jesse but I'm not sure you understood what I was asking. It definitely doesn't have anything to do with trimming spaces.
mister.magoo, I attempted both of those ideas and neither worked. I thought of case sensitivity previously as well and ruled that out already. I did not try the collation idea but that proved not to be the problem.
I'm stumped. I've never seen this before.
April 23, 2009 at 11:57 am
you could try converting the column to varbinary and checking the hex values to see what is really in there.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 23, 2009 at 12:03 pm
mister.magoo, I think you're on to something here.
I ran:
SELECT CONVERT(VARBINARY, ClientRelationship), CONVERT(VARBINARY, 'Client')
FROM InitialImport ii
WHERE [Name] = 'Some Guy'
There seems to be a trailing A0 on the end of the value stored in the column.
0x436C69656E74A0
0x436C69656E74
April 23, 2009 at 12:09 pm
Did you try Gail's suggestion to see what is actually in each column?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 23, 2009 at 12:13 pm
that is a line feed character. It makes me wonder how the data got that in.:)
Try updating the column by replacing char(10) in the data with ''
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 23, 2009 at 1:15 pm
I did try Gail's suggestion and it returned what we all would expect: the value with no trailing spaces.
I also ran:
select CONVERT(VARBINARY, 'abc'), CONVERT(VARBINARY, 'abc ')
and the second value has a '20' on the end of it, so there is either more than one kind of space, or it's a tab, or there is a character set difference at work here, or something on the end of these strings. What's crazy is if you copy and paste the value in Management Studio into your query window, it detects that it is a space and puts a space in. However by looking at them converted to VARBINARY we see that a space is not a space. RTRIM doesn't work in the query so it must not be a space as far as RTRIM or LTRIM are concerned.
I solved my problem by just running an update that chops off the last character of every column. Annoying but it worked. I got this Excel file from a web app that was made by a contractor that mined Outlook Contact data somehow. The project ultimately failed to work correctly ever but we want to throw what data would could recover out of it into a new system for test and evaluation.
Thanks all for your suggestions.
April 23, 2009 at 1:17 pm
Line feed character...I have no clue why that was put in there. Interestingly it was not put on the first column in the spreadsheet, but it was put on every column after it. The spreadsheet was exported out of a custom web app that was made by a contractor for my firm a couple of years ago. It never quite worked right and now we're trying to use something off the shelf.
April 23, 2009 at 2:07 pm
something done on a different OS?
Windows uses CR+LF,
Mac uses one (CR?), Linux the other (LF?).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 23, 2009 at 3:41 pm
The data from the failed application came from a PostgreSQL database. The app is a website and it lets you export to a spreadsheet but it came from that open source database and that might be where the funky space character came from.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply