April 13, 2016 at 2:25 pm
I have a problem where an SSIS import will give me some ? characters in my varchar column. I want to run an update command where it will replace all the ? characters with an X. What do I use to actually represent the ? character?
This is not a parameterized query question. I really want to look for ? characters.
Thanks!
April 13, 2016 at 2:39 pm
pvong (4/13/2016)
I have a problem where an SSIS import will give me some ? characters in my varchar column. I want to run an update command where it will replace all the ? characters with an X. What do I use to actually represent the ? character?This is not a parameterized query question. I really want to look for ? characters.
Thanks!
Nothing special required:
DECLARE @x VARCHAR(10) = '??text???';
SELECT @x
, REPLACE(@x,'?','');
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 13, 2016 at 2:45 pm
Why don't you handle this in SSIS before you load the data? An expression on your pipeline column would fix this. The real question is, why do you have ?'s in your data? Is this coming from the source or is this something that maybe a character set mismatch is causing? If the ?'s are coming from your source, use an expression on Derived column transform to replace the ?'s. Syntax is virtually the same REPLACE([Column], '?', '').
Alternatively, depending on what your SSIS source component is, you could add Phil's t-sql replace into the source query (assuming it is SQL Server source). For non SQL Server sources, use the replace function for that flavor of SQL.
April 13, 2016 at 5:41 pm
Are you sure those are actually question marks?
Those might be characters not from your default code page.
Because you import some sort of unicode strings into varchar columns those characters are converted into non-printable ones from your default code page, which are displayed as question marks.
Try to use nvarchar data type for your destination table.
_____________
Code for TallyGenerator
April 13, 2016 at 5:47 pm
Sergiy (4/13/2016)
Are you sure those are actually question marks?Those might be characters not from your default code page.
Because you import some sort of unicode strings into varchar columns those characters are converted into non-printable ones from your default code page, which are displayed as question marks.
Try to use nvarchar data type for your destination table.
Good point.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 18, 2016 at 12:51 pm
John Rowan (4/13/2016)
Why don't you handle this in SSIS before you load the data? An expression on your pipeline column would fix this. The real question is, why do you have ?'s in your data? Is this coming from the source or is this something that maybe a character set mismatch is causing? If the ?'s are coming from your source, use an expression on Derived column transform to replace the ?'s. Syntax is virtually the same REPLACE([Column], '?', '').Alternatively, depending on what your SSIS source component is, you could add Phil's t-sql replace into the source query (assuming it is SQL Server source). For non SQL Server sources, use the replace function for that flavor of SQL.
John - I'm going with your suggestion which then brings me to an SSIS question which I have posted here. If any of you guys can help me out, I would really appreciate it.
As for the nvarchar, I need the destination table in SQL to be decimal type because it's doing calculations constantly on those values.
http://www.sqlservercentral.com/Forums/Topic1778844-364-1.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply