Re: String Manipulation

Sam,

I get nothing. I just updated recently but the only version number I
can find is 8.3. I know its at least 8.3.4 but should be more.

Christine

At 03:58 PM 12/06/2009, you wrote:
>On Fri, Jun 12, 2009 at 03:35:44PM -0700, Christine Penner wrote:
> > The problem with making it a numeric field is that I have seen things
> > like A123, #123a or 23-233. This is only here to make most sorting
> > work better, not perfect. It all depends on how they enter the data.
> > Wont the different formats make it harder to convert to a number?
>
>The first thing is to define what you want it to do; pick some values
>and define what the output should be and go from there. If you've got
>say, "A123", "#125a" and "12-7" and you want them in that order then I'd
>strip out any non-numeric digits, convert it to a number and then sort
>on that. regexp_replace is your friend here.
>
> > I tried your suggestion and haven't had any luck. For a quick test I did
> > this:
> > select b_lot_or_st_no, substring('1a','^[0-9]+') as TEST from F_BUILDINGS
>
>What do you get back for simple things like:
>
> SELECT substring('1a','^[0-9]+');
>
>I'd expect you to get '1' back out. If you're not getting this out then
>you'll need to say which version of PG you're using as functions like
>this get added with each major version. Most useful docs for you are
>in:
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>
>You can get to older releases quickly by replacing "current" with things
>like "8.2" and "7.4".
>
>--
> Sam http://samason.me.uk/
>
>--
>Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general