Replace Function Doesn't Seem to Work
-
I am using SQL Server 2005 to try to remove hidden characters represented by odd little squares when I run a SELECT statement. I tried ltrim/rtrim and that doesn't work. I even tried the replace function, but that doesn't work, either. Does anyone have any ideas that might work for me?
-
I guess the column data-type for the column u are tryin to replace is NVarchar.. Can you please show us the REPLACE statement u tried?
-
Did it look like REPLACE(yourstring,'Odd Little Squares','') ? Cuz that's not gonna work at all! =).
Replacing things like that without knowing what they are can be a pain, you first have to figure out what the characters are then replace them. Conversely, you can use a string cleaning function like the one here [/url]to clean the string of everything you don't want at once.
-
The source column looks something like this:
XYZ12A01[FOUR ODD SQUARES]LP_EXL_ID
Then I use the following REPLACE function within a Derived Column transformation:
LTRIM(RTRIM(REPLACE([Column 0],"LP_EXL_ID","")))
The destination column is varchar(200). The resulting data looks like this:
XYZ12A01[FOUR ODD SQUARES]
I'm not quite sure what to do at this point.
-
imani_technology (5/18/2010)
The source column looks something like this:XYZ12A01[FOUR ODD SQUARES]LP_EXL_ID
Then I use the following REPLACE function within a Derived Column transformation:
LTRIM(RTRIM(REPLACE([Column 0],"LP_EXL_ID","")))
The destination column is varchar(200). The resulting data looks like this:
XYZ12A01[FOUR ODD SQUARES]
I'm not quite sure what to do at this point.
chances are the four odd squares are carriage returns...CHAR(13) + CHAR(10); they might even be Tab Characters(CHAR(9)
so if you look at the data in SSMS in grid mode, SSMS replaces CrLf with spacesso the data is presented on a single line; other programs replace them with blocks;
it's very common.
you could switch to text view (CONTROL + T) to confirm this.
then you need to decide whether the CrLf should be allowed int he data or not.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yup, it's a couple of carriage returns alright! So, how do I get rid of them?
-
i would go with something simple:
Replace(FieldWithCrLf,CHAR(13) + CHAR(10),'')--vbCrLf
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
One small problem: the Derived Column Transformation doesn't seem to recognize Char(10). Here is what I used:
(REPLACE(ReplacedDealName,Char(10),""))
-
imani_technology (5/18/2010)
One small problem: the Derived Column Transformation doesn't seem to recognize Char(10). Here is what I used:(REPLACE(ReplacedDealName,Char(10),""))
sorry thought this was pure SQL;
i belive in SSIS, you have the vb constants available to you:
vbCRLf,vbTab,vbCr,vbLf
REPLACE(ReplacedDealName,vbCrLf,""))
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I received an error: "Attempt to find input column vbCrLf."
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply