Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2010
    Posts
    87

    Unanswered: Remove Trailing Character from Field

    Hello folks,

    I concatenated 6 fields of data with a > separator. As it turns out, not every record / row had a full 6 fields of data to combine. So the result was a concatenation in which many of my results have trailing > as a character.

    I can't have any trailing > at all!

    I search google, youttube and this forum, I don't see how to remove trailing characters that aren't spaces.

    Is there any way to remove trailing >. Sometimes there are 5 in a row!

    Basically I need to remove every instance of > from the end of every record in my field. Some having one > and others having 2,3,4 or even 5 of them.

    I don't even have a starting point anywhere on this. Anyone got an idea?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look at the string functions
    update mytable
    set mycolumn = left(mycolumn,len(mycolumn)-1
    where right(mycolumn,1) = ">";
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2010
    Posts
    87
    Thank you but you give me far to much credit for understanding where to apply your string functions.

    Is that a query? Update query? Table Query? Other Query? SQL View in one of the above?

    If my table name is "ONE BIG TABLE" and my column name is "category_name" would this be correct? Remember, It could be 1,2,3,4 or 5 of these ">" that I'm removing

    I edited your instruction below, if this is right, where do I put it?

    update ONE BIG TABLE
    set category_name = left(category_name,len(category_name)-1
    where right(category_name,1) = ">";

  4. #4
    Join Date
    Aug 2010
    Posts
    87
    Hello,

    Thanks for the tips, this is what ended up working. I ran this from SQL view in an Update Query! I can't even believe I know what these things are now! LOL I am still a newbie but I'm flying, with the help of you and others, through Access! It's like a crash course! Thank you

    This is what ended up working.... (I ran it six times to get all the > characters out.

    UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].category_name = IIf(Right([category_name],1)=">",Left([category_name],Len([category_name])-1),[category_name]);

    For anyone new (like me) trying to do this. "ONE BIG TABLE" is the name of my table. "category_name" is the name of my field and i'm removing the trailing characters in the field IF they are >. You can change that to whatever you want I'd imagine. I created an Update Query and switched to SQL view and pasted my code (above) in there and saved it.


    This next part is for google and people that are looking to do this so I'm typing in the queries (no pun) that people are looking for so they can find it and so that google will pick it up.

    Access remove last character from field if
    access remove last character in string
    access remove last character
    access remove last character query

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    effectively
    Code:
    update mytable
    set mycolumn = left(mycolumn,len(mycolumn)-1
    where right(mycolumn,1) = ">";
    and

    Code:
    UPDATE [ONE BIG TABLE]
    SET [ONE BIG TABLE].category_name = IIf(Right([category_name],1)=">",Left([category_name],Len([category_name])-1),[category_name]);
    are the same. for the purists that last statement has probably got their blood pressure up a few notches

    they do the same thing in a different way
    both are seeking to rremove a trailing >
    Code:
    update mytable
    set mycolumn = left(mycolumn,len(mycolumn)-1
    where right(mycolumn,1) = ">";
    uses three elements
    1) update mytable
    - tells the SQL engine we are trying to change values
    2) set mycolumn = left(mycolumn,len(mycolumn)-1
    - says replace the current value of mycolumn with everything in mycolumn less one character
    3) where right(mycolumn,1) = ">";
    - limits the rows to be processed by saying only include those rows who's rightmost character is = ">"

    your version does the same thing but in a different way
    Code:
    UPDATE [ONE BIG TABLE]
    SET [ONE BIG TABLE].category_name = IIf(Right([category_name],1)=">",Left([category_name],Len([category_name])-1),[category_name]);
    the difference is that you are changing every value, using the IIF function you will always return a value, one where the preceding statement is true the other where it is false.

    you don't need to encapusalte table or column names unless they have a space in them. you've created a rod for your back by calling your table ONE BIG TABLE so that must be referred to as [ONE BIG TABLE], but you do not need to refer to your columns as [category_name], category_name will do fine

    the difference between the two queries is that one uses a where clause to limit the rows processed, the other doesn't, one only requests changes where a condition is satisfied, the other attempts to make changes to every row. granted the way SQL engines actually interpret that request depends on the SQL engine. some examine requested changes and do nothing if the resultant value would be the same as the proposed new value


    every time you see a sentance starting with
    select, eg select category_name from [one big table]
    update, eg update [one big table] set category_name = "<a value>"
    delete, eg delete * from [one big table]
    ..then its almost certainly a fragement of SQL
    usually you will have to replace the suggested names of tabels and or columns with the names of your tables/columns. each contributor has their own style
    some type the SQL bits in uppercase, eg SELECT my, comma, separated, column, list FROM mytable
    some use italics for the stuff you need to replace, eg SELECT my, comma, separated, column, list FROM mytable
    I tend to use mysomething to indicate where you should replace
    eg
    Code:
    update mytable
    set mycolumn = left(mycolumn,len(mycolumn)-1
    where right(mycolumn,1) = ">";
    so for you in this problem
    mytable would be replaced with [one big table]
    mycolumn would be replaced with category_name
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2010
    Posts
    87
    Very very useful information. I'm glad you clarified too because I initially was building queries with ONE BIG TABLE and didn't know why the didn't work. Then I saw the spaces and though, maybe a bracket? And tried it, which made me then bracket everything, thinking it was right! I should have named it with one word. I could go back and change my dozen queries and change the dB name. Or hey, in the future I can just update them so I don't break anything today

    Thank you healdem, you're a true pro!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •