How to insert this string?

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

How to insert this string?

Bart Smissaert
Have a  table like this:

create table sql_replace(string_old text, string_new text)

The idea is to do automatic string replacements in SQL.
Say we have this SQL:

select * from table1 where a in(xxx)

then after the string replacement it should be this:

select * from table1 where a in('A', 'B', 'C')

Having some  trouble inserting the 2 strings, xxx and 'A', 'B', 'C'
in the table. Tried various constructions, but getting errors. I can see
this will
be a lot easier to do in code with place holders and bound values, but in
this
case I need to do it with direct SQL in an Android phone app.

Any advice how to do this in direct SQL?

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

Re: How to insert this string?

R Smith-2
On 2018/07/27 10:38 AM, Bart Smissaert wrote:

> Have a  table like this:
>
> create table sql_replace(string_old text, string_new text)
>
> The idea is to do automatic string replacements in SQL.
> Say we have this SQL:
>
> select * from table1 where a in(xxx)
>
> then after the string replacement it should be this:
>
> select * from table1 where a in('A', 'B', 'C')
>
> Having some  trouble inserting the 2 strings, xxx and 'A', 'B', 'C'
> in the table. Tried various constructions, but getting errors. I can see
> this will
> be a lot easier to do in code with place holders and bound values, but in
> this
> case I need to do it with direct SQL in an Android phone app.
>
> Any advice how to do this in direct SQL?

This question is not clear.

what does xxx mean? is it an identifier? a constant? a string?
Whatever it is, once resolved, is it a set of strings, or a single string?

There is no way to change "...WHERE identifier IN (identifier)" into
"...WHERE identifier IN (identifier, identifier, ...)" via string
replacement or any other method.

If 'A,B,C' is meant to be a single string, then the "instr()" function
would get better results than "IN ()".

It's possible that I'm simply completely wrong about what you mean
though, could you perhaps give 2 examples of the exact syntax you intend
before and after the replacement (i.e. what exactly will "xxx" be in
your example)?


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

Re: How to insert this string?

Bart Smissaert
xxx is an identifier. It has no meaning at all in SQL and needs to be
replaced by the app.
I have given the SQL string before and after the replacement:

select * from table1 where a in(xxx)

then after the string replacement it should be this:

select * from table1 where a in('A', 'B', 'C')

The problem is how to get the string:
'A', 'B', 'C'
in the table.

RBS


On Fri, Jul 27, 2018 at 10:02 AM, R Smith <[hidden email]> wrote:

> On 2018/07/27 10:38 AM, Bart Smissaert wrote:
>
>> Have a  table like this:
>>
>> create table sql_replace(string_old text, string_new text)
>>
>> The idea is to do automatic string replacements in SQL.
>> Say we have this SQL:
>>
>> select * from table1 where a in(xxx)
>>
>> then after the string replacement it should be this:
>>
>> select * from table1 where a in('A', 'B', 'C')
>>
>> Having some  trouble inserting the 2 strings, xxx and 'A', 'B', 'C'
>> in the table. Tried various constructions, but getting errors. I can see
>> this will
>> be a lot easier to do in code with place holders and bound values, but in
>> this
>> case I need to do it with direct SQL in an Android phone app.
>>
>> Any advice how to do this in direct SQL?
>>
>
> This question is not clear.
>
> what does xxx mean? is it an identifier? a constant? a string?
> Whatever it is, once resolved, is it a set of strings, or a single string?
>
> There is no way to change "...WHERE identifier IN (identifier)" into
> "...WHERE identifier IN (identifier, identifier, ...)" via string
> replacement or any other method.
>
> If 'A,B,C' is meant to be a single string, then the "instr()" function
> would get better results than "IN ()".
>
> It's possible that I'm simply completely wrong about what you mean though,
> could you perhaps give 2 examples of the exact syntax you intend before and
> after the replacement (i.e. what exactly will "xxx" be in your example)?
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] How to insert this string?

Hick Gunter
In reply to this post by Bart Smissaert
Insert into sql_replace values ('xxx','''A'',''B'',''C''');

Note: All single quotes. To include a single quote in a single quoted string, it needs to be doubled.

Not sure what you are trying to do here. Build SQL queries on the fly?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Bart Smissaert
Gesendet: Freitag, 27. Juli 2018 10:38
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [EXTERNAL] [sqlite] How to insert this string?

Have a  table like this:

create table sql_replace(string_old text, string_new text)

The idea is to do automatic string replacements in SQL.
Say we have this SQL:

select * from table1 where a in(xxx)

then after the string replacement it should be this:

select * from table1 where a in('A', 'B', 'C')

Having some  trouble inserting the 2 strings, xxx and 'A', 'B', 'C'
in the table. Tried various constructions, but getting errors. I can see this will be a lot easier to do in code with place holders and bound values, but in this case I need to do it with direct SQL in an Android phone app.

Any advice how to do this in direct SQL?

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] How to insert this string?

Bart Smissaert
Yes, that works indeed. I made a silly mistake and that is that the replace
took already place in the table insertion SQL!
Idea is that users can make a shortcut in the SQL and not type out a long
string every time.
All sorted now.

RBS

On Fri, Jul 27, 2018 at 10:25 AM, Hick Gunter <[hidden email]> wrote:

> Insert into sql_replace values ('xxx','''A'',''B'',''C''');
>
> Note: All single quotes. To include a single quote in a single quoted
> string, it needs to be doubled.
>
> Not sure what you are trying to do here. Build SQL queries on the fly?
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von Bart Smissaert
> Gesendet: Freitag, 27. Juli 2018 10:38
> An: General Discussion of SQLite Database <sqlite-users@mailinglists.
> sqlite.org>
> Betreff: [EXTERNAL] [sqlite] How to insert this string?
>
> Have a  table like this:
>
> create table sql_replace(string_old text, string_new text)
>
> The idea is to do automatic string replacements in SQL.
> Say we have this SQL:
>
> select * from table1 where a in(xxx)
>
> then after the string replacement it should be this:
>
> select * from table1 where a in('A', 'B', 'C')
>
> Having some  trouble inserting the 2 strings, xxx and 'A', 'B', 'C'
> in the table. Tried various constructions, but getting errors. I can see
> this will be a lot easier to do in code with place holders and bound
> values, but in this case I need to do it with direct SQL in an Android
> phone app.
>
> Any advice how to do this in direct SQL?
>
> RBS
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to insert this string?

R Smith-2
In reply to this post by Bart Smissaert
On 2018/07/27 11:15 AM, Bart Smissaert wrote:
> xxx is an identifier. It has no meaning at all in SQL and needs to be
> replaced by the app.

Ok, so your app needs to replace xxx with 'A', 'B', 'C' then?

Well, that depends on what language your App is?

For example, assuming the query 'SELECT * FROM table1 WHERE a IN (xxx);'
is in variable q:

Most languages have some standard function akin to:
  str_replace(q, "xxx", "'A', 'B', 'C'");       - if the language uses
double-quotes to quote strings (like VB or PHP), or
  str_replace(q, 'xxx', '''A'', ''B'', ''C'''); - if the language uses
single-quotes (like most other).
(I assume you already knew this already and the question is more towards
the next bit, but I'm mentioning it since it was asked, and in case you,
or someone else reading this, did not).

To get the string 'A', 'B', 'C' into a field in a table (from where you
might query it to replace it with your app as above), that would be:

INSERT INTO t(SearchStr) VALUES ('''A'', ''B'', ''C''');

which is essentially the same as 'A', 'B', 'C' where every single quote
is duplicated and then all of it is enclosed with an added pair of
single quotes.

Note that if this is a string inside your single-quote string quoting
program language, it has to be escaped again, which essentially repeats
the above process to yield something like:

sQuery = 'INSERT INTO t(SearchStr) VALUES (''''''A'''', ''''B'''',
''''C'''''');';

Though your programming language should have some form of QuotedStr()
function. If not, this should be easy to translate to your language of
choice:

function QuotedStr(inputStr) string {
   s = inputStr;
   for(i = len(s); i > 0; i--) {
     if ( s[i] = Char(39) )  { insert(s, i, ''''); }
   }
   QuotedStr = '''' + s + '''';
}

Note that '''' and Char(39) both denote a single single-quote character
in string format in a language that uses single quotes to quote strings.
This could make life easier in that you can simply say something like:

sQuery = 'INSERT INTO t(SearchStr) VALUES (' + QuotedStr('''A'', ''B'',
''C''') + ');';

or even (if you prefer bloat over obscurity):

sQuery = 'INSERT INTO t(SearchStr) VALUES (' + QuotedStr( QuotedStr('A')
+ ', ' + QuotedStr('B') + ',  '+ QuotedStr('C') ) + ');';

Hope that makes some sense...
Ryan

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

Re: How to insert this string?

Bart Smissaert
Thanks for that. A QuotedString function will be useful indeed and will add
that.
In this case my language is B4A, which is similar to the old VB6.
Very good and much recommended.

RBS


On Fri, Jul 27, 2018 at 11:32 AM, R Smith <[hidden email]> wrote:

> On 2018/07/27 11:15 AM, Bart Smissaert wrote:
>
>> xxx is an identifier. It has no meaning at all in SQL and needs to be
>> replaced by the app.
>>
>
> Ok, so your app needs to replace xxx with 'A', 'B', 'C' then?
>
> Well, that depends on what language your App is?
>
> For example, assuming the query 'SELECT * FROM table1 WHERE a IN (xxx);'
> is in variable q:
>
> Most languages have some standard function akin to:
>  str_replace(q, "xxx", "'A', 'B', 'C'");       - if the language uses
> double-quotes to quote strings (like VB or PHP), or
>  str_replace(q, 'xxx', '''A'', ''B'', ''C'''); - if the language uses
> single-quotes (like most other).
> (I assume you already knew this already and the question is more towards
> the next bit, but I'm mentioning it since it was asked, and in case you, or
> someone else reading this, did not).
>
> To get the string 'A', 'B', 'C' into a field in a table (from where you
> might query it to replace it with your app as above), that would be:
>
> INSERT INTO t(SearchStr) VALUES ('''A'', ''B'', ''C''');
>
> which is essentially the same as 'A', 'B', 'C' where every single quote is
> duplicated and then all of it is enclosed with an added pair of single
> quotes.
>
> Note that if this is a string inside your single-quote string quoting
> program language, it has to be escaped again, which essentially repeats the
> above process to yield something like:
>
> sQuery = 'INSERT INTO t(SearchStr) VALUES (''''''A'''', ''''B'''',
> ''''C'''''');';
>
> Though your programming language should have some form of QuotedStr()
> function. If not, this should be easy to translate to your language of
> choice:
>
> function QuotedStr(inputStr) string {
>   s = inputStr;
>   for(i = len(s); i > 0; i--) {
>     if ( s[i] = Char(39) )  { insert(s, i, ''''); }
>   }
>   QuotedStr = '''' + s + '''';
> }
>
> Note that '''' and Char(39) both denote a single single-quote character in
> string format in a language that uses single quotes to quote strings.
> This could make life easier in that you can simply say something like:
>
> sQuery = 'INSERT INTO t(SearchStr) VALUES (' + QuotedStr('''A'', ''B'',
> ''C''') + ');';
>
> or even (if you prefer bloat over obscurity):
>
> sQuery = 'INSERT INTO t(SearchStr) VALUES (' + QuotedStr( QuotedStr('A') +
> ', ' + QuotedStr('B') + ',  '+ QuotedStr('C') ) + ');';
>
> Hope that makes some sense...
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users