Thread: removing a portion of text

  hi,
  I have a column with full of data like ATB-OO  NCK-TT .... how can I
easily remove the "-" ? it seems that the "-" is allways the fourth letter.

 thanks,
 Pere
--
View this message in context: http://www.nabble.com/removing-a-portion-of-text-tp20067248p20067248.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Oct 20, 2008 at 04:21:31AM -0700, pere roca wrote:
>   I have a column with full of data like ATB-OO  NCK-TT .... how can I
> easily remove the "-" ?

If you just want to remove all the dashes then I'd use a regex;
something like:

  UPDATE table SET data = regexp_replace(data, '-', '', 'g');

This says, modify the table replacing every instance (the 'g' option) of
a dash with an empty string in the "data" column.

> it seems that the "-" is allways the fourth letter.

another option in that case would be to use a couple of calls to
substring() instead of the regex call:

  substring(data FROM 1 FOR 3) || substring(data FROM 4);

The manual page[1] contains more complete descriptions of the functions
that operate on text.  If you haven't come across regexs before then I'd
recommend playing around with them, they're very useful and appear in
lots of places--most text editors will allow you to search and replace
using them, doing much more interesting things that I did above.


  Sam

 [1] http://www.postgresql.org/docs/current/static/functions-string.html

--- On Mon, 10/20/08, pere roca <peroc79@gmail.com> wrote:

> From: pere roca <peroc79@gmail.com>
> Subject: re[GENERAL] moving a portion of text
> To: pgsql-general@postgresql.org
> Date: Monday, October 20, 2008, 11:21 AM
> hi,
>   I have a column with full of data like ATB-OO  NCK-TT
> .... how can I
> easily remove the "-" ? it seems that the
> "-" is allways the fourth letter.
>
>  thanks,
>  Pere
> --
> View this message in context:
> http://www.nabble.com/removing-a-portion-of-text-tp20067248p20067248.html
> Sent from the PostgreSQL - general mailing list archive at
> Nabble.com.
>
>
with fixed length

select substring(tex1,1,3) ||  substring(tex1,5) from t1

with variable length

select substring(tex1,1,strpos(tex1,'-'::varchar)-1) ||  substring(tex1,strpos(tex1,'-'::varchar)+1) from t1

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com