| Author |
Topic |
|
SQL_DI
Starting Member
5 Posts |
 Posted - 2009-07-01 : 15:35:11
|
I am bcp'in a table out into a tab delimited text file. When I open up the file with textpad, I am getting a | in an empty field. When I open up the file in word and use the show control characters, I get a Y with two dots on top. Never seen this one before. can I use the char() function to remove this. The only options I see in BOL are 9 for tab, 10 for line feed, and 13 for carriage return. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-01 : 15:38:31
|
In Textpad you have the chance to identify that special record an select it from table?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-01 : 15:44:51
|
select ascii('ÿ')gives 255.That's another kind of space.replace(column,char(255),' ')
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
SQL_DI
Starting Member
5 Posts |
Posted - 2009-07-01 : 15:48:07
|
| Yes, the attribute does not have anything in it for that record that I can see. |
|
|
|
SQL_DI
Starting Member
5 Posts |
Posted - 2009-07-01 : 15:51:54
|
| It's still there |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-01 : 15:59:34
|
What datatype is that column?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-07-01 : 17:07:02
|
quote:
Originally posted by webfred
What datatype is that column?
No, you're never too old to Yak'n'Roll if you're too young to die.
varcharYou want to sanitize the data?Is the data coming from user entry?From a different source system?Make the business or feeding systems clean up the data...or don't worry, be happyIs it causing any problems?ANY of the usual CHAR suspects in an exporet to a delimited file can cause you problemsTry a delimiter of "|~|"That should be safeBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-07-01 : 17:14:33
|
quote:
Originally posted by SQL_DI
It's still there
replace(column,char(159),' ')
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
SQL_DI
Starting Member
5 Posts |
Posted - 2009-07-01 : 17:56:46
|
| It's a varchar(60). I have a file created a few years back that has the same data, but it does not have this problem. I am wondering if it came about during a migration to 2005. CHAR(159) di not work either. |
|
|
|
|
Unusual character in data field