Post reply

SQL LTrim not removing a character

  • October 9, 2011 at 1:45 pm

    #260135

    select LTrim(RTrim(FirstName)) As Ian FROM @tblCity2 where ProfileId = @ProfileId

    Gives me the result below, and truncates the last phone number digit.

    ' 615-661-457'

    If i change the FirstName to a varchar(13) from a varchar(12), i get this:

    ' 615-661-4574'

    Any idea how to remove the first space or what ever is in that space?

    Thanks

  • October 9, 2011 at 2:01 pm

    #1392215

    isuckatsql (10/9/2011)

    select LTrim(RTrim(FirstName)) As Ian FROM @tblCity2 where ProfileId = @ProfileId

    Gives me the result below, and truncates the last phone number digit.

    ' 615-661-457'

    If i change the FirstName to a varchar(13) from a varchar(12), i get this:

    ' 615-661-4574'

    Any idea how to remove the first space or what ever is in that space?

    Thanks

    can you check if the first character is space or some other character?

    select ASCII(substring(FirstName,1,1)) from

    FROM @tblCity2 where ProfileId = @ProfileId

    If the result is 32, It's space and Ltrim should remove this character, otherwise it won't.

  • October 9, 2011 at 2:13 pm

    #1392218

    First of all, for now and future use get yourself a copy of the Microsoft ASCII character set.

    http://msdn.Microsoft.com/en-us/library/4z4t9ed1(v=vs.71).aspx

    Note that the following are non displaying characters:

    ASCII

    Value Description

    8 Backspace

    9 Tab

    10 Line feed

    13 Carriage return

    28 File separator

    29 Group separator

    30 Record separator

    31 Unit separator

    Using the ASCII function check the value of that first character and use the appropriate SUBSTRING parameters to get rid of it.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • October 9, 2011 at 3:43 pm

    #1392225

    It was a Tab.

    Thanks guys!

  • October 9, 2011 at 4:39 pm

    #1392230

    For those interested in what some of the more cryptic designations for ASCII characters less than character 32 are, please see the following link...

    http://www.asciitable.com/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 9, 2011 at 4:45 pm

    #1392232

    Your welcome.

    Now if that is value passed to your SP, so be it. If it comes from a column in a data set, I would suggest that you develop a T-SQL statement to check all your rows in that table, and clean up those that may have a similar problem. Test it once, test it again and again. Note do this on a Non production DB and when you are certain you have it working properly, then and only then apply the code to your production db

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

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

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