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.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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