FW: Equivelant for lpad in Informix ? IDS7.31

Posted in 2005

Dirk Moolman — Tue, 25 Jan 2005 14:23:48 +0200 — source: Usenet: comp.databases.informix

This is a multi-part message in MIME format. ------_=_NextPart_001_01C502D8.B8E4FBB6 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Oracle has an "lpad" function, to add characters to the left of a string - what is the equivelant of this function in Informix ? Cannot find lpad function in the SQL manuals ... =20 The problem is, I don't want to concatenate, ||, the result must always be to characters long. =20 If cl_m_d_no is 0, I want 00 If cl_m_d_no is 9, I want 09 If cl_m_d_no is 11, it stays 11 =20 =20 Oracle select below: =20 SELECT "cl_bt_no" ,"cl_no" ,"cl_item_no" ,"cl_operator_code" ,substr("cl_scheme",3) cl_scheme ,"cl_dr_no" ,"cl_m_no" ,"cl_d_no" =20 ,rtrim("cl_m_no")||lpad("cl_d_no",2,'0') cl_m_d_no ,"cl_rams_no" ,"cl_dt_service_in" ,"cl_dt_service_out" ,"cl_dt_payment" ,"cl_dt_claim_rcvd" ,"cl_dt_processed" ,"cl_ta_code" ,"cl_units" ,"cl_m_plan" ,"cl_dr_plan" ,"cl_ta_rule" ,"cl_run_no" ,"cl_sequence" ,"cl_tooth_no" ,"dr_name" =20 ,"cl_pay_ind" =20 ,"cl_supp_ref_no"=20 ,"cl_reject_code" ,"cl_remark_code" ,"cl_booking_no" ,"cl_claim_amount" ,"cl_tariff_amount" ,"cl_benefit_amount" ,"cl_members_amount" ,"cl_discount_amount" ,"cl_status" ,"cl_diag_code" ,"cl_levy_amount" ,"cl_iteration" ,"cl_auth_no" ,"cl_scheme_amount" =20 FROM "claim"@medclear , "doctor"@medclear=20 WHERE "cl_dr_no" =3D "dr_no" =20 =20 ------_=_NextPart_001_01C502D8.B8E4FBB6 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html xmlns:o=3D"urn:schemas-microsoft-com:office:office" = xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns=3D"http://www.w3.org/TR/REC-html40">; <head> <meta http-equiv=3DContent-Type content=3D"text/html; = charset=3Dus-ascii"> <meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)"> <style>  </style> </head> <body lang=3DEN-US link=3Dblue vlink=3Dpurple> <div class=3DSection1> Oracle has an “lpad” function, to add characters to = the left of a string – what is the equivelant of this function in = Informix ?    Cannot find lpad function in the SQL manuals = …<o:p></o:p> <o:p> </o:p>= The problem is, = I don’t want to concatenate, ||, the result must always be to characters = long.<o:p></o:p> <o:p> </o:p>= If cl_m_d_no is = 0, I want 00<o:p></o:p> If cl_m_d_no is = 9, I want 09<o:p></o:p> If cl_m_d_no is = 11, it stays 11<o:p></o:p> <o:p> </o:p> <o:p> </o:p>= Oracle select = below:<o:p></o:p> <o:p> </o:p></p= > SELECT "cl_bt_no"<o:p></o:p>       = ,"cl_no"<o:p></o:p>       = ,"cl_item_no"<o:p></o:p>       = ,"cl_operator_code"<o:p></o:p>       ,substr("cl_scheme",3) cl_scheme<o:p></o:p> <span = st

↪ replying to Dirk Moolman

Doug Lawry — Tue, 25 Jan 2005 15:31:20 -0000 — source: Usenet: comp.databases.informix

Try not to post MIME, Dirk :-) A cleaned up version follows. Looks like you need to upgrade: LPAD is there in version 9. -- Regards, Doug Lawry www.douglawry.webhop.org "Dirk Moolman" <DirkM@mxgroup.co.za> wrote in message news:1106662226.b5a997af030137130b3480a9499796a4@teranews... > > Oracle has an "lpad" function, to add characters to the left of a string > - what is the equivelant of this function in Informix ? Cannot find > lpad function in the SQL manuals ... > > The problem is, I don't want to concatenate, ||, the result must always > be to characters long. > > If cl_m_d_no is 0, I want 00 > If cl_m_d_no is 9, I want 09 > If cl_m_d_no is 11, it stays 11 > > Oracle select below: > > SELECT "cl_bt_no" > ,"cl_no" > ,"cl_item_no" > ,"cl_operator_code" > ,substr("cl_scheme",3) cl_scheme > ,"cl_dr_no" > ,"cl_m_no" > ,"cl_d_no" > ,rtrim("cl_m_no")||lpad("cl_d_no",2,'0') cl_m_d_no > ,"cl_rams_no" > ,"cl_dt_service_in" > ,"cl_dt_service_out" > ,"cl_dt_payment" > ,"cl_dt_claim_rcvd" > ,"cl_dt_processed" > ,"cl_ta_code" > ,"cl_units" > ,"cl_m_plan" > ,"cl_dr_plan" > ,"cl_ta_rule" > ,"cl_run_no" > ,"cl_sequence" > ,"cl_tooth_no" > ,"dr_name" > ,"cl_pay_ind" > ,"cl_supp_ref_no" > ,"cl_reject_code" > ,"cl_remark_code" > ,"cl_booking_no" > ,"cl_claim_amount" > ,"cl_tariff_amount" > ,"cl_benefit_amount" > ,"cl_members_amount" > ,"cl_discount_amount" > ,"cl_status" > ,"cl_diag_code" > ,"cl_levy_amount" > ,"cl_iteration" > ,"cl_auth_no" > ,"cl_scheme_amount" > FROM "claim"@medclear > , "doctor"@medclear > WHERE "cl_dr_no" = "dr_no"