Re: is_numeric() or extract_numeric() functions?

I guess my note wasn't clear enough. When I said that worked, I meant
using the double quotes when trying to use regular expressions in a
function. I placed the working function, called numeric_part(text), on
the cookbook web page -> that's what I wanted to convey in the first two
sentences of my response.

eg:
planroomsk=# select numeric_part('Testing on my 7.2.1 install');
numeric_part
--------------
721
(1 row)

Jeff Eckermann wrote:
> --- "Ron St.Pierre" <rstpierre(at)syscor(dot)com> wrote:
>
>>Thanks everyone, that worked and I now have a new
>>plpgsql function to
>>extract numbers from a string. I placed it in the
>>PL/pgSQL CookBook
>>(linked from the Users Lounge on the postgres home
>>page ->
>>http://www.brasileiro.net:8080/postgres/cookbook/)
>>if anyone wants it.
>
>
> You mean this _works_ for you??? I doesn't for me,
> and I don't see how it could. I see a return type
> mismatch, for starters: $1 ~ ''[0-9]'' will return
> boolean, whereas your function is defined to return an
> integer. Testing on my 7.2.1 installation gives this
> error:
> jeck=# select getnumber('1');
> NOTICE: Error occurred while executing PL/pgSQL
> function getnumber
> NOTICE: while casting return value to functions
> return type
> ERROR: pg_atoi: error in "t": can't parse "t"
>
>
>>Tom Lane wrote:
>>
>>
>>>"Ron St.Pierre" <rstpierre(at)syscor(dot)com> writes:
>>>
>>>
>>>
>>>>CREATE OR REPLACE FUNCTION getnumber(varchar(1))
>>
>>RETURNS integer AS '
>>
>>>> BEGIN
>>>> return ($1 ~ '[0-9]');
>>>> END;
>>>>' LANGUAGE 'plpgsql';
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>>I get the following error:
>>>> parse error at or near "["
>>>>
>>>>
>>>
>>>You need to double the quotes in the function body,
>>
>>viz
>>
>>> ...
>>> return ($1 ~ ''[0-9]'');
>>> ...
>>>
>>>Backslash-quote (\') is an alternative way.
>>>
>>> regards, tom lane
>>>
>>>---------------------------(end of
>>
>>broadcast)---------------------------
>>
>>>TIP 1: subscribe and unsubscribe commands go to
>>
>>majordomo(at)postgresql(dot)org
>>
>>>
>>>
>>
>>
>>--
>>Ron St.Pierre
>>Syscor R&D
>>tel: 250-361-1681
>>email: rstpierre(at)syscor(dot)com
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please
>>send an appropriate
>>subscribe-nomail command to majordomo(at)postgresql(dot)org
>>so that your
>>message can get through to the mailing list cleanly
>
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
>
>

--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre(at)syscor(dot)com