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/