Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 weird symbol after import

Author  Topic 

GilbertoH
Starting Member

4 Posts

Posted - 2012-11-14 : 08:19:35
Hello, after import, I got the following symbol on an NVARCHAR value datatype:

?

When I search for the value without this symbol, it find it, but when I try to do join with other tables, this value don´t work. (since its without this weird symbol on the other table.

Please could somebody what is this symbol and how to access this specific data, since if I try to search for "data ?" with this simbol at the end, I got nothing.
Since this is combined with good data, and I have no way to access the visual mode to change it manually, how can I get rid of this symbol and how can I avoid it on future import process.



--
Regards, GilbertoH.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 09:54:19
It could be some spurious character in the data, or it could be that the collation you are using does not support the character. In either case, you can get a sense of what is in there by casting it to binary.
SELECT CAST(yourCol AS VARBINARY) FROM YourTable;
Now look at the result to see what character it is. If that does not make much sense, try this and you will see what I mean
SELECT CAST('A1' AS VARBINARY) 
-- Gives this --> 0x4131
Go to Top of Page

GilbertoH
Starting Member

4 Posts

Posted - 2012-11-14 : 11:54:30


VARBINARY show me the same value for normal and abnormal value.

number: 7421000602156 varbinary 0x555043
abnormal:7421000602156? varbinary 0x555043



--
Regards, GilbertoH.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 12:11:42
It may be getting cut off. Try one of these:
SELECT CAST(yourCol AS VARBINARY(MAX)) FROM YourTable;
SELECT CAST(RIGHT(yourCol,10) AS VARBINARY) FROM YourTable;
Go to Top of Page

GilbertoH
Starting Member

4 Posts

Posted - 2012-11-14 : 12:22:01
that fix it for this time, but I need to identify what was that, where it come from and how to avoid that to happend on the future.

is there any recomendation to check for this kind of inconsistence on a numeric value stored as varchar.

I was thinking to convert everything twice on the importing process, first to numbers and then back to text, but the problem is that sometimes I need the zeros at the left, plus the lenght is variable.

--
Regards, GilbertoH.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-14 : 12:45:54
You will need to consult the vendor or client who provided the data.

I didn't quite follow what you meant by converting to number and back.

Microsoft has a feature/product called Data Quality Services. I have never used it and don't know the first thing about it. You may want to investigate that to see if it would be useful.
Go to Top of Page
   

- Advertisement -