Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19

    Unanswered: Get rid of end character...

    Does anyone no a way that would allow me to delete the final character of a string if it is a comma?

    I have lots of rows of numbers such as:-

    1,2,5,6,7,
    2,5,6,7,4,9,10,
    2,3
    2,4,5,9,7,
    1,3
    7,8,9,

    All I wont to do is get rid of the comma's that do not seperate anything?( 1,3 is fine and does not need anything deleteing nor does 2,3)

    Any help would be great.

    Cheers

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Iif(right(FieldName,1) = ",",Left(fieldname,len(fieldname)-1),fieldname)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select iif(right(number,1)=',',left(number,1,len(number)-1),number)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19
    Cheers for the help redneckGeek

    I have tried to use this but all I get is compile error Expected:=

    Cheers

    Ill order the scotch

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    to be pedantic "1, 2, 3," is a string, not a number
    using left$() and right$() will be faster.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What context are you trying to use it in (code, query, control source)?
    Inspiration Through Fermentation

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    newString = iif(whatever he said)
    currently using SS 2008R2

  8. #8
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19
    In a module.

    I cant compile either as it errs out on the Left expression?

    Cheers

  9. #9
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19
    sorry to double post but.

    The err message is "argument not optional"

    cheers

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Copy the line you put in your code, and paste it here. Sounds like
    it's just semantic error.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    newString = Iif(right$(oldString, 1) = ",", Left$(oldString, len(oldString)-1), oldString)

    ...is not missing any arguments.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19
    IIf(Right(jobsregion_cs, 1) = ",", Left(jobsregion_cs), Len(jobsregion_cs) - 1, jobsregion_cs)

  13. #13
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Update YourTableName Set yourfieldname = iif(right(yourfieldname ,1)=",",left(yourfieldname ,len(yourfieldname)-1),yourfieldname );

    replace YourTabbleName with the name of your table
    replace yourfieldname with the name of your field
    copy into a query and run

  14. #14
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by freemanj7
    IIf(Right(jobsregion_cs, 1) = ",", Left(jobsregion_cs), Len(jobsregion_cs) - 1, jobsregion_cs)
    You have a misplaced ')' in the 2nd argument. Move it from the end of ..._cs)
    to after the... -1
    Inspiration Through Fermentation

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sorry freemanj7 - that's plain wrong.

    check where all the parenthesis should be (mine is fastest cos i'm using left$() and right$(), but RNG & DC will also work.

    rudy's wont work ...looks like mid() syntax instead of left()

    izy
    currently using SS 2008R2

Posting Permissions

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