trim function - any sql experts out there???

Posted in 2003

DarrenJacobs@carmax.com — Wed, 12 Feb 2003 17:04:58 — source: IIUG Forums & Mailing Lists

Does anyone know why the statements below return the same thing? I'm running 9.21 on HPUX 11. What I would like to do is get the information either side of the comma. I have used "both" as well as "leading" in the function. niether works Thanks in advance MODIFY: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R = Redraw CTRL-X = Delete character CTRL-D = Delete rest of line ----------------------- ben8exp@bentley -------- Press CTRL-W for Help -------- select consumer_name from ps_cx_ar_aging consumer_name BROOKS,TRACY HOPSON,ROY MCCARTY,KEVIN BLAIR,JOHN BAUM,ERIC FOUCAULT,STEPHANIE EVANS,CHRIS PROCTOR SMITH,MICHAEL MODIFY: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R = Redraw CTRL-X = Delete character CTRL-D = Delete rest of line ----------------------- ben8exp@bentley -------- Press CTRL-W for Help -------- select trim( trailing ',' from consumer_name) from ps_cx_ar_aging (expression) BROOKS,TRACY HOPSON,ROY MCCARTY,KEVIN BLAIR,JOHN BAUM,ERIC FOUCAULT,STEPHANIE EVANS,CHRIS PROCTOR SMITH,MICHAEL

↪ replying to DarrenJacobs@carmax.com

Jonathan — Wed, 12 Feb 2003 18:03:20 — source: IIUG Forums & Mailing Lists

Because the data does not have any trailing commas? -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | "DarrenJacob...."| | | <DarrenJacobs@car| | | max.com> | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 02/12/2003 09:04 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: trim function - any sql experts out there??? [349] | | | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| Does anyone know why the statements below return the same thing? I'm running 9.21 on HPUX 11. What I would like to do is get the information either side of the comma. I have used "both" as well as "leading" in the function. niether works Thanks in advance MODIFY: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R = Redraw CTRL-X = Delete character CTRL-D = Delete rest of line ----------------------- ben8exp@bentley -------- Press CTRL-W for Help -------- select consumer_name from ps_cx_ar_aging consumer_name BROOKS,TRACY HOPSON,ROY MCCARTY,KEVIN BLAIR,JOHN BAUM,ERIC FOUCAULT,STEPHANIE EVANS,CHRIS PROCTOR SMITH,MICHAEL MODIFY: ESC = Done editing CTRL-A = Typeover/Insert CTRL-R = Redraw CTRL-X = Delete character CTRL-D = Delete rest of line ----------------------- ben8exp@bentley -------- Press CTRL-W for Help -------- select trim( trailing ',' from consumer_name) from ps_cx_ar_aging (expression) BROOKS,TRACY HOPSON,ROY MCCARTY,KEVIN BLAIR,JOHN BAUM,ERIC FOUCAULT,STEPHANIE EVANS,CHRIS PROCTOR SMITH,MICHAEL

↪ replying to DarrenJacobs@carmax.com

Mark D. Stock — Wed, 12 Feb 2003 23:41:09 — source: IIUG Forums & Mailing Lists

DarrenJacob.... wrote: > Does anyone know why the statements below return the same thing? I'm > running 9.21 on HPUX 11. Because you don't have any trailing commas? > What I would like to do is get the information either side of the comma. I > have used "both" as well as "leading" in the function. niether works > select trim( trailing ',' from consumer_name) from ps_cx_ar_aging Perhaps you misunderstand what "trailing" and "leading" mean? They mean "at the end" and "at the beginning". Your commas are in the middle...., somewhere. It depends on what you are trying to do, which is not immediately clear from your post, but have you tried something like: SELECT REPLACE(consumer_name, ",", " ") consumer_name FROM ps_cx_ar_aging Otherwise, write a stored procedure to extract what you want. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

↪ replying to Mark D. Stock

paul@oninit.com — Thu, 13 Feb 2003 06:27:23 — source: IIUG Forums & Mailing Lists

"Mark D. Stock" wrote: > > DarrenJacob.... wrote: > > Does anyone know why the statements below return the same thing? I'm > > running 9.21 on HPUX 11. > > Because you don't have any trailing commas? > > > What I would like to do is get the information either side of the comma. I > > have used "both" as well as "leading" in the function. niether works > > select trim( trailing ',' from consumer_name) from ps_cx_ar_aging > > Perhaps you misunderstand what "trailing" and "leading" mean? They mean > "at the end" and "at the beginning". Your commas are in the middle...., > somewhere. > > It depends on what you are trying to do, which is not immediately clear > from your post, but have you tried something like: > > SELECT REPLACE(consumer_name, ",", " ") consumer_name > FROM ps_cx_ar_aging > > Otherwise, write a stored procedure to extract what you want. Or install the regexp bladelet. This should be a part of the standard install!!! -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #