Re: [GENERAL] postgres function
On 10/14/15 8:38 AM, Ramesh T wrote:
Hi All,
Do we have function like regexp_substr in postgres..?
in oracle this function seach the - from 1 to 2 and return result,
regexp_substr(PART_CATG_DESC,'[^-]+', 1, 2)
Use regexp_split_to_array(string text, pattern text [, flags text ]):
SELECT regexp_split_to_array('1-2-3-4-5', '-');
regexp_split_to_array
---
{1,2,3,4,5}
If you just want one part of the array:
SELECT (regexp_split_to_array('1-2-3-4-5', '-'))[2];
regexp_split_to_array
---
2
(Note the extra ()s)
If that's not what you need then as David suggested please provide a few
input values and what you expect as your *final* output. IE: tell us
what you're ultimately trying to do, instead of just asking about regexp
matching. There may be a much better way to do it in Postgres than
whatever you were doing in Oracle.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres function
On 15/10/15 14:32, Ramesh T wrote:
> select position('-' in '123-987-123')
> position
> ---
> 4
> But I want second occurrence,
> position
> -
> 8
>
> plz any help..?
For instance:
# select char_length(substring('123-987-123' from '^[^-]*-[^-]*-'));
char_length
-
8
Best,
Torsten
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres function
yes David gave correct solution
but , the value I'm using and it's column in the table sometimes value
may be '123-987-123' or '123-987-123-13-87'
if pass like below must return else condiion 0,
select case when select split_part('123-987-123','-',4) >0
then 1 else 0 end
it's return error like integer need...
On Thu, Oct 15, 2015 at 8:50 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Oct 15, 2015 at 10:05 AM, Ramesh T
> wrote:
>
>> '123-987-123' it is not fixed some times it may be '1233-9873-123-098'
>> as you said it's fixed,
>>
>> changes the values in middle of the -
>>
>> sometimes times i need 1233 and 098 or 9873,first position i'll find
>> direct for second variable we don't know where it's end with -
>>
>> i.e ,
>> i need to find second postition of the variable between the '-'
>>
>>
>
> While I and others are likely inclined to provide you a working solution
> to do so you need to state your data and requirement more clearly. Given
> the apparent language dynamic I'd suggest supplying 5-10 example data
> values along with their expected result.
>
> Otherwise, regular expressions almost certainly will let you solve your
> problem (though, like Joe Conway indicated, split_part may be possible)
> once you learn how to construct them. regexp_matches(...) is the access
> point to using them.
>
> David J.
>
>
Re: [GENERAL] postgres function
On Thu, Oct 15, 2015 at 3:15 PM, Ramesh T
wrote:
> yes David gave correct solution
>
> but , the value I'm using and it's column in the table sometimes value
> may be '123-987-123' or '123-987-123-13-87'
>
>
So adapt the answer provided to match your data.
if pass like below must return else condiion 0,
>
> select case when select split_part('123-987-123','-',4) >0
> then 1 else 0 end
> it's return error like integer need...
>
>
I have no clue what you are trying to say here...
David J.
Re: [GENERAL] postgres function
On Thu, Oct 15, 2015 at 10:05 AM, Ramesh T wrote: > '123-987-123' it is not fixed some times it may be '1233-9873-123-098' > as you said it's fixed, > > changes the values in middle of the - > > sometimes times i need 1233 and 098 or 9873,first position i'll find > direct for second variable we don't know where it's end with - > > i.e , > i need to find second postition of the variable between the '-' > > While I and others are likely inclined to provide you a working solution to do so you need to state your data and requirement more clearly. Given the apparent language dynamic I'd suggest supplying 5-10 example data values along with their expected result. Otherwise, regular expressions almost certainly will let you solve your problem (though, like Joe Conway indicated, split_part may be possible) once you learn how to construct them. regexp_matches(...) is the access point to using them. David J.
Re: [GENERAL] postgres function
Well you could use
SELECT LENGTH(REGEXP_REPLACE('123-987-123', '(([^-]*-){2}).*', '\1'));
Not pretty, but it works.
Geoff
On 15 October 2015 at 15:05, Ramesh T wrote:
> '123-987-123' it is not fixed some times it may be '1233-9873-123-098'
> as you said it's fixed,
>
> changes the values in middle of the -
>
> sometimes times i need 1233 and 098 or 9873,first position i'll find
> direct for second variable we don't know where it's end with -
>
> i.e ,
> i need to find second postition of the variable between the '-'
>
>
>
> On Thu, Oct 15, 2015 at 6:32 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Thu, Oct 15, 2015 at 8:32 AM, Ramesh T
>> wrote:
>>
>>> select position('-' in '123-987-123')
>>> position
>>> ---
>>> 4
>>> But I want second occurrence,
>>> position
>>> -
>>> 8
>>>
>>> plz any help..?
>>>
>>>
>>
>> SELECT length((regexp_matches('123-987-123', '(\d{3}-\d{3}-)\d{3}'))[1])
>>
>>
>> David J.
>>
>>
>
Re: [GENERAL] postgres function
On 10/15/2015 07:05 AM, Ramesh T wrote:
> '123-987-123' it is not fixed some times it may be '1233-9873-123-098'
> as you said it's fixed,
>
> changes the values in middle of the -
>
> sometimes times i need 1233 and 098 or 9873,first position i'll find
> direct for second variable we don't know where it's end with -
>
> i.e ,
> i need to find second postition of the variable between the '-'
Are you looking for the position or the actual variable? If you really
want the latter you can do:
select split_part('123-987-123','-',2);
select split_part('1233-9873-123-098','-',2);
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] postgres function
select position('-' in '123-987-123')
position
---
4
But I want second occurrence,
position
-
8
plz any help..?
On Thu, Oct 15, 2015 at 12:54 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Oct 14, 2015 at 9:38 AM, Ramesh T
> wrote:
>
>> Hi All,
>> Do we have function like regexp_substr in postgres..?
>>
>> in oracle this function seach the - from 1 to 2 and return result,
>> regexp_substr(PART_CATG_DESC,'[^-]+', 1, 2)
>>
>
> Maybe one of the functions on this page will get you what you need.
>
> http://www.postgresql.org/docs/devel/static/functions-string.html
>
> David J.
>
>
>
>
Re: [GENERAL] postgres function
'123-987-123' it is not fixed some times it may be '1233-9873-123-098'
as you said it's fixed,
changes the values in middle of the -
sometimes times i need 1233 and 098 or 9873,first position i'll find
direct for second variable we don't know where it's end with -
i.e ,
i need to find second postition of the variable between the '-'
On Thu, Oct 15, 2015 at 6:32 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Thu, Oct 15, 2015 at 8:32 AM, Ramesh T
> wrote:
>
>> select position('-' in '123-987-123')
>> position
>> ---
>> 4
>> But I want second occurrence,
>> position
>> -
>> 8
>>
>> plz any help..?
>>
>>
>
> SELECT length((regexp_matches('123-987-123', '(\d{3}-\d{3}-)\d{3}'))[1])
>
>
> David J.
>
>
Re: [GENERAL] postgres function
On Thu, Oct 15, 2015 at 8:32 AM, Ramesh T
wrote:
> select position('-' in '123-987-123')
> position
> ---
> 4
> But I want second occurrence,
> position
> -
> 8
>
> plz any help..?
>
>
SELECT length((regexp_matches('123-987-123', '(\d{3}-\d{3}-)\d{3}'))[1])
David J.
Re: [GENERAL] postgres function
On Wed, Oct 14, 2015 at 9:38 AM, Ramesh T wrote: > Hi All, > Do we have function like regexp_substr in postgres..? > > in oracle this function seach the - from 1 to 2 and return result, > regexp_substr(PART_CATG_DESC,'[^-]+', 1, 2) > Maybe one of the functions on this page will get you what you need. http://www.postgresql.org/docs/devel/static/functions-string.html David J.
