Quitar CR/LF de campo char o varchar

Posted in 2015

Mauricio Rapari — Wed, 05 Aug 2015 13:06:57 — source: IIUG Forums & Mailing Lists

Hola necesito quitar de campos varchar y char los caracteres especiales CR/LF en Oracle y sqlserver tengo la funcion chr pero en informix sql no encuentro su equivalente alguien tubo este problema? Queiro ejecutar un sql en informix como el siguiente. update nombre_tabla set nombre_del_campo = replace(nombre_del_campo, chr(13)||chr(10), ' ') where nombre_del_campo like '%' || chr(13)||chr(10) || '%'; Desde ya muchas Gracias Mauricio Rapari

↪ replying to Mauricio Rapari

Art Kagel — Wed, 05 Aug 2015 13:15:15 — source: IIUG Forums & Mailing Lists

Maurico: This should work exactly as you have it. Informix supports both the REPLACE() and CHR() functions and they work just the same as they do in Oracle. What version of Informix do you have? (It's always a good idea to tell us that info in case our anser has to be version specific.) Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. 2015-08-05 8:06 GMT-05:00 Mauricio Rapari <mrapari@aguasbonaerenses.com.ar>: > Hola necesito quitar de campos varchar y char los caracteres especiales > CR/LF en Oracle y sqlserver tengo la funcion chr pero en informix sql no > encuentro su equivalente alguien tubo este problema? > > Queiro ejecutar un sql en informix como el siguiente. > > update nombre_tabla > set nombre_del_campo = replace(nombre_del_campo, > > chr(13)||chr(10), ' ') > where nombre_del_campo like '%' || chr(13)||chr(10) || '%'; > > Desde ya muchas Gracias > > Mauricio Rapari > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113fe4285d8c2e051c9031e7

↪ replying to Art Kagel

Mauricio Rapari — Wed, 05 Aug 2015 14:39:06 — source: IIUG Forums & Mailing Lists

My Version is IBM Informix Dynamic Server Version 11.50.FC5 Thank you Art Kangel! -----Mensaje original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Art Kagel Enviado el: miércoles, 05 de agosto de 2015 10:15 a.m. Para: ids@iiug.org Asunto: Re: Quitar CR/LF de campo char o varchar [35564] Maurico: This should work exactly as you have it. Informix supports both the REPLACE() and CHR() functions and they work just the same as they do in Oracle. What version of Informix do you have? (It's always a good idea to tell us that info in case our anser has to be version specific.) Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. 2015-08-05 8:06 GMT-05:00 Mauricio Rapari <mrapari@aguasbonaerenses.com.ar>: > Hola necesito quitar de campos varchar y char los caracteres especiales > CR/LF en Oracle y sqlserver tengo la funcion chr pero en informix sql no > encuentro su equivalente alguien tubo este problema? > > Queiro ejecutar un sql en informix como el siguiente. > > update nombre_tabla > set nombre_del_campo = replace(nombre_del_campo, > > chr(13)||chr(10), ' ') > where nombre_del_campo like '%' || chr(13)||chr(10) || '%'; > > Desde ya muchas Gracias > > Mauricio Rapari > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113fe4285d8c2e051c9031e7 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mauricio Rapari

Art Kagel — Wed, 05 Aug 2015 14:46:26 — source: IIUG Forums & Mailing Lists

OK, 11.50 is a problem. REPLACE() is there but no CHR() function. You can try using the "C"-ish notation for carriage return and newline which are '\\\\r' and'\\ ' respectively and see if that will work. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 5, 2015 at 9:39 AM, Mauricio Rapari < mrapari@aguasbonaerenses.com.ar> wrote: > My Version is IBM Informix Dynamic Server Version 11.50.FC5 > > Thank you Art Kangel! > > -----Mensaje original----- > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Art > Kagel > Enviado el: miércoles, 05 de agosto de 2015 10:15 a.m. > Para: ids@iiug.org > Asunto: Re: Quitar CR/LF de campo char o varchar [35564] > > Maurico: > > This should work exactly as you have it. Informix supports both the > REPLACE() and CHR() functions and they work just the same as they do in > Oracle. What version of Informix do you have? (It's always a good idea to > tell us that info in case our anser has to be version specific.) > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > 2015-08-05 8:06 GMT-05:00 Mauricio Rapari <mrapari@aguasbonaerenses.com.ar > >: > > > Hola necesito quitar de campos varchar y char los caracteres especiales > > CR/LF en Oracle y sqlserver tengo la funcion chr pero en informix sql no > > encuentro su equivalente alguien tubo este problema? > > > > Queiro ejecutar un sql en informix como el siguiente. > > > > update nombre_tabla > > set nombre_del_campo = replace(nombre_del_campo, > > > > chr(13)||chr(10), ' ') > > where nombre_del_campo like '%' || chr(13)||chr(10) || '%'; > > > > Desde ya muchas Gracias > > > > Mauricio Rapari > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a113fe4285d8c2e051c9031e7 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113fe4287442a8051c917771

↪ replying to Art Kagel

Paul Watson — Wed, 05 Aug 2015 14:55:35 — source: IIUG Forums & Mailing Lists

You could also use the regexp datablade, that will work but will require a change in the SQL Cheers Paul > OK, 11.50 is a problem. REPLACE() is there but no CHR() function. You can > try using the "C"-ish notation for carriage return and newline which are > '\\\\r' and'\\ ' respectively and see if that will work. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Wed, Aug 5, 2015 at 9:39 AM, Mauricio Rapari < > mrapari@aguasbonaerenses.com.ar> wrote: > >> My Version is IBM Informix Dynamic Server Version 11.50.FC5 >> >> Thank you Art Kangel! >> >> -----Mensaje original----- >> De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] En nombre de Art >> Kagel >> Enviado el: miércoles, 05 de agosto de 2015 10:15 a.m. >> Para: ids@iiug.org >> Asunto: Re: Quitar CR/LF de campo char o varchar [35564] >> >> Maurico: >> >> This should work exactly as you have it. Informix supports both the >> REPLACE() and CHR() functions and they work just the same as they do in >> Oracle. What version of Informix do you have? (It's always a good idea >> to >> tell us that info in case our anser has to be version specific.) >> >> Art >> >> Art S. Kagel, President and Principal Consultant >> ASK Database Management >> www.askdbmgt.com >> >> Blog: http://informix-myview.blogspot.com/ >> >> Disclaimer: Please keep in mind that my own opinions are my own opinions >> and do not reflect on the IIUG, nor any other organization with which I >> am >> associated either explicitly, implicitly, or by inference. Neither do >> those opinions reflect those of other individuals affiliated with any >> entity with which I am affiliated nor those of the entities themselves. >> >> 2015-08-05 8:06 GMT-05:00 Mauricio Rapari >> <mrapari@aguasbonaerenses.com.ar >> >: >> >> > Hola necesito quitar de campos varchar y char los caracteres >> especiales >> > CR/LF en Oracle y sqlserver tengo la funcion chr pero en informix sql >> no >> > encuentro su equivalente alguien tubo este problema? >> > >> > Queiro ejecutar un sql en informix como el siguiente. >> > >> > update nombre_tabla >> > set nombre_del_campo = replace(nombre_del_campo, >> > >> > chr(13)||chr(10), ' ') >> > where nombre_del_campo like '%' || chr(13)||chr(10) || '%'; >> > >> > Desde ya muchas Gracias >> > >> > Mauricio Rapari >> > >> > >> > >> > >> >> >> > ******************************************************************************* >> > Forum Note: Use "Reply" to post a response in the discussion forum. >> > >> > >> >> --001a113fe4285d8c2e051c9031e7 >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --001a113fe4287442a8051c917771 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians