Adding a string in front of what is already in a field

classic Classic list List threaded Threaded
7 messages Options
jic
Reply | Threaded
Open this post in threaded view
|

Adding a string in front of what is already in a field

jic

Greetings.

I would like some help with this scenario... DB name OpenJobs.

id,pid,spid,notes
100, 24,32,'this is a test'
101, 24,32,'a different note'
102, 24,32,'yet, another different note'
...

What I dould like to do is to update each of those notes by adding a string
to the front of data contained in the notes.  The final DB data would look
like this:

id,pid,spid,notes
100, 24,32,'string to add in front\r\nthis is a test'
101, 24,32,'string to add in front\r\na different note'
102, 24,32,'string to add in front\r\nyet, another different note'
...

I have come up with this:

UPDATE OpenJobs SET notes = 'string to add in front\r\n' ||
      (SELECT notes from OpenJobs where spid = 32) WHERE spid = 32;

But I don't think it's going to work.

Any help would be greatly appreciated.

thanks.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a string in front of what is already in a field

Puneet Kishor-2
On Sun, Oct 10, 2010 at 11:05 PM, jose isaias cabrera
<[hidden email]> wrote:

>
> Greetings.
>
> I would like some help with this scenario... DB name OpenJobs.
>
> id,pid,spid,notes
> 100, 24,32,'this is a test'
> 101, 24,32,'a different note'
> 102, 24,32,'yet, another different note'
> ...
>
> What I dould like to do is to update each of those notes by adding a string
> to the front of data contained in the notes.  The final DB data would look
> like this:
>
> id,pid,spid,notes
> 100, 24,32,'string to add in front\r\nthis is a test'
> 101, 24,32,'string to add in front\r\na different note'
> 102, 24,32,'string to add in front\r\nyet, another different note'
> ...
>
> I have come up with this:
>
> UPDATE OpenJobs SET notes = 'string to add in front\r\n' ||
>      (SELECT notes from OpenJobs where spid = 32) WHERE spid = 32;
>

UPDATE OpenJobs
SET notes = 'string to add in front\r\n' || notes
WHERE spid = 32;

> But I don't think it's going to work.
>
> Any help would be greatly appreciated.
>
> thanks.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: Adding a string in front of what is already in a field

jic

"P Kishor" wrote...

> On Sun, Oct 10, 2010 at 11:05 PM, jose isaias cabrera
> <[hidden email]> wrote:
>>
>> Greetings.
>>
>> I would like some help with this scenario... DB name OpenJobs.
>>
>> id,pid,spid,notes
>> 100, 24,32,'this is a test'
>> 101, 24,32,'a different note'
>> 102, 24,32,'yet, another different note'
>> ...
>>
>> What I dould like to do is to update each of those notes by adding a
>> string
>> to the front of data contained in the notes. The final DB data would look
>> like this:
>>
>> id,pid,spid,notes
>> 100, 24,32,'string to add in front\r\nthis is a test'
>> 101, 24,32,'string to add in front\r\na different note'
>> 102, 24,32,'string to add in front\r\nyet, another different note'
>> ...
>>
>> I have come up with this:
>>
>> UPDATE OpenJobs SET notes = 'string to add in front\r\n' ||
>> (SELECT notes from OpenJobs where spid = 32) WHERE spid = 32;
>>
>
> UPDATE OpenJobs
> SET notes = 'string to add in front\r\n' || notes
> WHERE spid = 32;

That easy, huh?  I knew it.

thanks,

josé

>> But I don't think it's going to work.
>>
>> Any help would be greatly appreciated.
>>
>> thanks.
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> -----------------------------------------------------------------------
> Assertions are politics; backing up assertions with evidence is science
> =======================================================================
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a string in front of what is already in a field

Igor Tandetnik
In reply to this post by Puneet Kishor-2
P Kishor <[hidden email]> wrote:
> UPDATE OpenJobs
> SET notes = 'string to add in front\r\n' || notes
> WHERE spid = 32;

Note that SQLite doesn't understand C-style escapes. '\r' is a string consisting of two characters, a backslash and a letter r. If you want to insert CRLF pair, you'd need something like this:

SET notes = 'string to add in front' || cast(x'0d0a' as text) || notes

Or, you can use a parameter in place of the string literal, and bind a string to it in your program. Such a string can contain any characters you want.
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a string in front of what is already in a field

Puneet Kishor-2
José,

Please note Igor's very important cautionary note below --

On Mon, Oct 11, 2010 at 7:05 AM, Igor Tandetnik <[hidden email]> wrote:
> P Kishor <[hidden email]> wrote:
>> UPDATE OpenJobs
>> SET notes = 'string to add in front\r\n' || notes
>> WHERE spid = 32;
>
> Note that SQLite doesn't understand C-style escapes. '\r' is a string consisting of two characters, a backslash and a letter r. If you want to insert CRLF pair, you'd need something like this:
>
> SET notes = 'string to add in front' || cast(x'0d0a' as text) || notes
>

So, if you are using a programming language, you can do like so

    UPDATE OpenJobs
    SET notes = ? || notes
    WHERE spid = ?

and then, in your application (for example, Perl code below; note, use
of double quotes)

    $sth->execute( "string to add in front\r\n", 32);

Or, in the command line sqlite3 program, you can simply hit "enter" on
your keyboard and then close-single-quote the string.

sqlite > UPDATE OpenJobs SET notes = 'string to add in front
sqlite > ' || notes WHERE spid = 32;


> Or, you can use a parameter in place of the string literal, and bind a string to it in your program. Such a string can contain any characters you want.
> --
> Igor Tandetnik
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Adding a string in front of what is already in a field

jose isaias cabrera


"P Kishor" wrote...


> José,
>
> Please note Igor's very important cautionary note below --
>
> On Mon, Oct 11, 2010 at 7:05 AM, Igor Tandetnik <[hidden email]>
> wrote:
>> P Kishor <[hidden email]> wrote:
>>> UPDATE OpenJobs
>>> SET notes = 'string to add in front\r\n' || notes
>>> WHERE spid = 32;
>>
>> Note that SQLite doesn't understand C-style escapes. '\r' is a string
>> consisting of two characters, a backslash and a letter r. If you want to
>> insert CRLF pair, you'd need something like this:
>>
>> SET notes = 'string to add in front' || cast(x'0d0a' as text) || notes
>>
>
> So, if you are using a programming language, you can do like so
>
>    UPDATE OpenJobs
>    SET notes = ? || notes
>    WHERE spid = ?
>
> and then, in your application (for example, Perl code below; note, use
> of double quotes)
>
>    $sth->execute( "string to add in front\r\n", 32);
>
> Or, in the command line sqlite3 program, you can simply hit "enter" on
> your keyboard and then close-single-quote the string.
>
> sqlite > UPDATE OpenJobs SET notes = 'string to add in front
> sqlite > ' || notes WHERE spid = 32;
>
>
>> Or, you can use a parameter in place of the string literal, and bind a
>> string to it in your program. Such a string can contain any characters
>> you want.

Yep.  Already working thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
jic
Reply | Threaded
Open this post in threaded view
|

Re: Adding a string in front of what is already in a field

jic
In reply to this post by Puneet Kishor-2
"P Kishor" wrote...


> José,
>
> Please note Igor's very important cautionary note below --
>
> On Mon, Oct 11, 2010 at 7:05 AM, Igor Tandetnik <[hidden email]>
> wrote:
>> P Kishor <[hidden email]> wrote:
>>> UPDATE OpenJobs
>>> SET notes = 'string to add in front\r\n' || notes
>>> WHERE spid = 32;
>>
>> Note that SQLite doesn't understand C-style escapes. '\r' is a string
>> consisting of two characters, a backslash and a letter r. If you want to
>> insert CRLF pair, you'd need something like this:
>>
>> SET notes = 'string to add in front' || cast(x'0d0a' as text) || notes
>>
>
> So, if you are using a programming language, you can do like so
>
>    UPDATE OpenJobs
>    SET notes = ? || notes
>    WHERE spid = ?
>
> and then, in your application (for example, Perl code below; note, use
> of double quotes)
>
>    $sth->execute( "string to add in front\r\n", 32);
>
> Or, in the command line sqlite3 program, you can simply hit "enter" on
> your keyboard and then close-single-quote the string.
>
> sqlite > UPDATE OpenJobs SET notes = 'string to add in front
> sqlite > ' || notes WHERE spid = 32;
>
>
>> Or, you can use a parameter in place of the string literal, and bind a
>> string to it in your program. Such a string can contain any characters
>> you want.

Yep.  Already tested and into production. :-)  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users