FW: Equivelant for lpad in Informix ? IDS7.31

Posted in 2005

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

This is a multi-part message in MIME format. ------_=_NextPart_001_01C502DD.4B5809FD Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Don't worry, found the answer to lpad ! =20 =20 select substr( '00' || cl_d_no, -2 ) =20 =20 =20 ________________________________ From: Dirk Moolman=20 Sent: 25 January 2005 02:24 PM To: informix-list@iiug.org Subject: FW: Equivelant for lpad in Informix ? IDS7.31 =20 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_01C502DD.4B5809FD Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html xmlns:v=3D"urn:schemas-microsoft-com:vml" = 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> Don’t worry, found the answer = to lpad !<o:p></o:p> <o:p> </o:p> <o:p> </o:p> select substr( ‘00’ || cl_d_no, -2 )<o:p></o:p> <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> <div> <div class=3DMsoNormal align=3Dcenter style=3D'text-align:center'> <hr size=3D2 width=3D"100%" align=3Dcenter tabindex=3D-1> </div> From: Dirk = Moolman Sent: 25 January 2005 = 02:24 PM To: = informix-list@iiug.org Subject: FW: Equivelant = for lpad in Informix ? IDS7.31<o:p></o:p> </div> <o:p> </o:p> Oracle has an “lpad” function, to add characters to = the left of a string – what is the equ