[postgis-users] The use of substring()

[Andy Colson]

On 10/23/2011 03:02 PM, maduako ikechukwu wrote:
> Hi guys,
>
> I want to extract date information from the filename column of my
> raster table to update a new date_of_creation column
>
> but i don't have an idea of how to do this, using the substring() and
> casting it.
>
> Please could someone write for me a sample sql instruction on how
> this is done.
>
> Thanks a lot.
>
>
>
> -- Iyke Maduako

You dont give a lot of info, so I'll have to guess.

Assuming your data looks something like:

filename
-----------
high pass 1/1/2011.ecw
low pass 5/13/1999.ecw

I'd use something like:

update raster set date_of_creation = substring(filename from '\d+/\d+/\d+')::timestamp;


Of course, being from Germany, your date is probably 13/5/1999, and maybe the ::timestamp cast will convert it ok, but maybe not.

If, however, your date is in the form 5 apr 1999, that's a bit tougher.  But you didnt give any details!  Woot!  so I'm off the hook. :-)

-Andy