Post reply

Trim functions and Unicode fields??

  • June 10, 2004 at 4:29 am

    #162032

    Hey,

    I'm having some trouble trimming white space off the end of an nVarchar text field.

    If I try and return the value in the field using trim functions, e.g.

    select ltrim(rtrim([Field]))

    from Table

    where [Field] like '%value%'

    There are still two 'spaces' at the end of the text. I say spaces because if you take one of these characters and return the unicode value it's 32 .... is this correct?? If so then is there another way that you should approach trimming unicode fileds???

    Any help would be greatly appreciated.

    Cheers

    Rob.

  • June 10, 2004 at 4:52 am

    #509809

    What data type are you using to store the unicode nchar, nvarchar, or ntext?

    Give me a sample of one of the entries... 


    Andy.

  • June 10, 2004 at 4:57 am

    #509812

    The field datatype is nVarchar....

    Here is a copy and paste of one of the values in the table... 'Daytona  '

  • June 10, 2004 at 5:00 am

    #509813

    Hey... have just copied the value from my previous posting and tried to trim it... and it works... I think it will be hard to replicate the issue without having the original data....

  • June 10, 2004 at 7:51 am

    #509859

    What are you looking at to determine the two spaces?

    RTrim works with nvarchar, eg

    declare @x nvarchar(20)

    set @x = 'Daytona  '

    select cast(@x as varbinary)

    select cast(rtrim(@x) as varbinary)

    select '"'+rtrim(@x)+'"'

    gives

    0x44006100790074006F006E00610020002000

    0x44006100790074006F006E006100

    "Daytona"

    Far away is close at hand in the images of elsewhere.
    Anon.

  • June 10, 2004 at 8:13 am

    #509873

    Cheers David,

    Well I have to come clean...

    ...it turns out the two chars at the end of each field are in fact a carrage return / line feed not spaces. The data was originally loaded into SQL Server from an excel file via DTS transformation. Somehow when running the query in query analyser these were manifested as spaces???

    Anyhow, thaks for your input.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply