quotes around text in returned data?

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

quotes around text in returned data?

Richard Boyd

Hi,

Is there anyway to get Sqlite to add 'quotes' around text strings when it is
returning data from a select command?

For example:

I have a table called table0 which is created using
CREATE TABLE table0 (col1 INTEGER, col2 TEXT, col3 INTEGER);

I have a row in the table
1|this is a text string|2

Now to insert that I need something like:
INSERT INTO table0 values(1,'this is a text string',2);

When I go to retrieve this data using
SELECT * from table0;
I get:
1|this is a text string|2
Which I suppose is correct. But what happens if I want to put this row into
another table???  I need to parse it and add in the quotes (' ') around the
text string??

The problem I have is that I retrieve data from a remote database then pass
over a network to be received and placed into a local table. OF course I get
an error because Sqlite complains about the string.

Can anyone help with this problem???

Thanks in advance.
R.


Reply | Threaded
Open this post in threaded view
|

Re: quotes around text in returned data?

D. Richard Hipp
"Richard Boyd" <[hidden email]> wrote:
> Hi,
>
> Is there anyway to get Sqlite to add 'quotes' around text strings when it is
> returning data from a select command?
>

Use the built-in quote() function.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

RE: quotes around text in returned data?

Richard Boyd

>>Use the built-in quote() function.
--

Just what I'm after (huge sigh of relief) Is it sqlite3_quote?

I had a search through the documentation and didn't come up with anything.
Obviously I didn't look hard enough. I'll go back and look again.

Thanks very much for the quick response and apologies if this is a common
question.
R.

Reply | Threaded
Open this post in threaded view
|

RE: quotes around text in returned data?

Richard Boyd
In reply to this post by D. Richard Hipp


>>Use the built-in quote() function.
--

Ok I think I found it. (http://www.sqlite.org/lang_expr.html)
I'm not really familiar with this method of calling a function in an
expression. Is there away to have it only quote the TEXT columns and not the
INTEGER columns, or does it do this anyway by default?

Ideally I'd like the entire row returned with TEXT strings in quotes but
everything else left as normal.

SELECT * from Table0 quote(??some expression in here??);
????

Sorry, I'm a bit lost.  Are there any code snippets on the usage?

Thanks again.
R.



Reply | Threaded
Open this post in threaded view
|

Re: quotes around text in returned data?

D. Richard Hipp
In reply to this post by Richard Boyd
"Richard Boyd" <[hidden email]> wrote:

> >>Use the built-in quote() function.
> --
>
> Ok I think I found it. (http://www.sqlite.org/lang_expr.html)
> I'm not really familiar with this method of calling a function in an
> expression. Is there away to have it only quote the TEXT columns and not the
> INTEGER columns, or does it do this anyway by default?
>
> Ideally I'd like the entire row returned with TEXT strings in quotes but
> everything else left as normal.
>
> SELECT * from Table0 quote(??some expression in here??);
> ????
>

SELECT quote(column1), quote(column2), ... FROM table0;

The quote function leaves number unchanged.  It only quotes TEXT
and BLOB data.

If you are using the command-line client, you can also do this:

   .mode insert table_x
   select * from table0;

The above will output SQL containing INSERT statements for
table_x.

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: quotes around text in returned data?

Dennis Cote
In reply to this post by Richard Boyd
Richard Boyd wrote:

>  
>
>>>Use the built-in quote() function.
>>>      
>>>
>--
>
>Ok I think I found it. (http://www.sqlite.org/lang_expr.html)
>I'm not really familiar with this method of calling a function in an
>expression. Is there away to have it only quote the TEXT columns and not the
>INTEGER columns, or does it do this anyway by default?
>
>Ideally I'd like the entire row returned with TEXT strings in quotes but
>everything else left as normal.
>
>SELECT * from Table0 quote(??some expression in here??);
>????
>
>Sorry, I'm a bit lost.  Are there any code snippets on the usage?
>
>Thanks again.
>R.
>
>
>
>
>  
>
Richard,

Try this:

select int_col, quote(text_col), quote(other_text_col) from table;

HTH

Dennis Cote
Reply | Threaded
Open this post in threaded view
|

RE: quotes around text in returned data?

Marcus Welz
In reply to this post by Richard Boyd
If your table is defined as

CREATE TABLE table0 (col1 INTEGER, col2 TEXT, col3 INTEGER)

I believe the correct syntax would be:

SELECT col1, quote(col2) AS col2, col3 FROM table0;


Instead of * you list the columns, separated by comma. Since you're using
quote() around col2, you probably need to specify "AS col2" since otherwise
it will not know what to name that column (if you're using names to access
them).

-----Original Message-----
From: Richard Boyd [mailto:[hidden email]]
Sent: Thursday, November 03, 2005 6:02 PM
To: [hidden email]
Subject: RE: [sqlite] quotes around text in returned data?



>>Use the built-in quote() function.
--

Ok I think I found it. (http://www.sqlite.org/lang_expr.html)
I'm not really familiar with this method of calling a function in an
expression. Is there away to have it only quote the TEXT columns and not the
INTEGER columns, or does it do this anyway by default?

Ideally I'd like the entire row returned with TEXT strings in quotes but
everything else left as normal.

SELECT * from Table0 quote(??some expression in here??);
????

Sorry, I'm a bit lost.  Are there any code snippets on the usage?

Thanks again.
R.



Reply | Threaded
Open this post in threaded view
|

RE: quotes around text in returned data?

Richard Boyd
In reply to this post by Dennis Cote

Thanks all!

I'll give those a go.

-Richard.