On Tue, Feb 24, 2009 at 8:13 PM, BenJones12345
<benjpjo...@googlemail.com> wrote:
>
> Hi all
>
> I'm very much a beginner with sqlite3 and and I'm completely stumped with
> using the replace function.
>
> What I have is a field (TheOldField) with values like:
>
> HM1
> HP4
> HM3
> HM2
>
> and I need to replace all "P" with "+" and all "M" with "-", and put the
> result into a new field (TheNewField). All the documentation everywhere
> suggests I should use the replace function. So what I'm trying to do is
> (just for the P's at first):
>
> UPDATE TheTable
> set TheNewField = replace(TheOldField, "P", "+");
>
> but doing this I get the error message
>
> "SQL error: no such function: replace"
>
> Which I dont understand. Can anybody tell me what I'm doing wrong or offer
> an alternative solution?
>
dunno, but it works for me. In any case, do use single quotes for literals.
sqlite> CREATE TABLE foo (a, b);
sqlite> INSERT INTO foo (a) VALUES ('HM1');
sqlite> INSERT INTO foo (a) VALUES ('HP4');
sqlite> INSERT INTO foo (a) VALUES ('HM3');
sqlite> INSERT INTO foo (a) VALUES ('HM2');
sqlite> .m col
sqlite> .h on
sqlite> SELECT * FROM foo;
a b
---------- ----------
HM1
HP4
HM3
HM2
sqlite> UPDATE foo SET b = Replace(a, 'P', '+') WHERE a LIKE '%P%';
sqlite> UPDATE foo SET b = Replace(a, 'M', '-') WHERE a LIKE '%M%';
sqlite> SELECT * FROM foo;
a b
---------- ----------
HM1 H-1
HP4 H+4
HM3 H-3
HM2 H-2
--
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
Sent from: Madison WI United States.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
