Thread: Spliting a string in plpgsql

Hi,
I'm writing a function in plpgsql and i need to do the following:

I have a string in the following format.

mail.yahoo.com

In this string, i need to figure out the number of dots in it and split the string into two
on last but one dot.

Is there any way to accomplish this.
Please let me know

Thanks
~Jas

Jasbinder Singh Bali <jsbali@gmail.com> schrieb:

> Hi,
> I'm writing a function in plpgsql and i need to do the following:
>
> I have a string in the following format.
>
> _m_a_i_l_._y_a_h_o_o_._c_o_m
>
> In this string, i need to figure out the number of dots in it and split the

Number of dots:

test=*# select length(regexp_replace('mail.yahoo.com','[^\.]','','g'));
 length
--------
      2
(1 row)


> string into two
> on last but one dot.

I'm not sure if i understand you correctly, perhaps with split_part(),
see
http://www.postgresql.org/docs/current/interactive/functions-string.html


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

> Jasbinder Singh Bali <jsbali@gmail.com> schrieb:
>> I'm writing a function in plpgsql and i need to do the following:
>>
>> I have a string in the following format.
>>
>> mail.yahoo.com
>>
>> In this string, i need to figure out the number of dots in it and
split the
>> string into two on last but one dot.
>
> Number of dots:
>
> test=*# select
> length(regexp_replace('mail.yahoo.com','[^\.]','','g'));
>  length
> --------
>       2
> (1 row)

I think that this is the desired split:

test=> SELECT regexp_replace('mail.yahoo.com',
E'^(.*)\\.([^.]*\\.[^.]*)$', E'\\1');
 regexp_replace
----------------
 mail
(1 row)

test=> SELECT regexp_replace('mail.yahoo.com',
E'^(.*)\\.([^.]*\\.[^.]*)$', E'\\2');
 regexp_replace
----------------
 yahoo.com
(1 row)

Yours,
Laurenz Albe