"BareFeetWare" wrote...
> On 27/10/2010, at 3:09 PM, jose isaias cabrera wrote:
>
>> I know I can do a bunch of sets, such as this one,
>>
>> UPDATE table1 set d1 = '2010-01-01'
>> where
>> d1 = '2010-1-1';
>>
>> but that is a lot of coding.
>
>
> Perhaps something like:
>
> create table table1
> ( id integer primary key
> , st text
> , ca text
> , d1 date
> , d2 date
> )
> ;
> insert into table1 (st, ca, d1, d2)
> values ('AA','BB','2010-1-1','2010-2-9')
> ;
> insert into table1 (st, ca, d1, d2)
> values ('BB','BB','2010-1-1', '2010-3-29')
> ;
> insert into table1 (st, ca, d1, d2)
> values ('CC','BB','2010-10-4','2010-5-13')
> ;
> insert into table1 (st, ca, d1, d2)
> values ('DD', 'BB','2010-1-10','2010-02-01')
> ;
>
> update table1
> set d1 = substr(d1, 1, 5) ||
> case when substr(d1, 7, 1) = '-' then '0' || substr(d1, 6, 2) else
> substr(d1, 6, 3) end ||
> case when substr(d1, -2, 1) = '-' then '0' || substr(d1, -1, 1) else
> substr(d1, -2, 2) end
> , d2 = substr(d2, 1, 5) ||
> case when substr(d2, 7, 1) = '-' then '0' || substr(d2, 6, 2) else
> substr(d2, 6, 3) end ||
> case when substr(d2, -2, 1) = '-' then '0' || substr(d2, -1, 1) else
> substr(d2, -2, 2) end
> ;
>
> select * from table1;
>
> which gives:
>
> 1,AA,BB,2010-01-01,2010-02-09
> 2,BB,BB,2010-01-01,2010-03-29
> 3,CC,BB,2010-10-04,2010-05-13
> 4,DD,BB,2010-01-10,2010-02-01
>
>> I thought that perhaps there would be an easier regular expression call
>> within the DB engine.
>
> I wish there was some regex functionality built into SQLite, but alas
> there is not.
thanks, Tom. Yes, I wish there was a regex functionality also.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
