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
BareFeetWare
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
