Re: [sqlite] Concatenating literals with column values

[Dave Wellman]

Hi Clemens,

Thanks for the pointer although I'm doing this the other way around. I'm
casting an INTEGER to a TEXT value.

I think Michael is probably on the right track here, this is to do with
operator precedence.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: 31 May 2013 17:43
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Concatenating literals with column values

Dave Wellman wrote:
> Why do I need the "()" around my calculation?

<http://www.sqlite.org/lang_expr.html#castexpr> says:
| When casting a TEXT value to INTEGER, the longest possible prefix of 
| the value that can be interpreted as an integer number is extracted 
| from the TEXT value and the remainder ignored. Any leading spaces in 
| the TEXT value when converting from TEXT to INTEGER are ignored. If 
| there is no prefix that can be interpreted as an integer number, the 
| result of the conversion is 0.

So the result of 'forty-two'+0 is 0.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concatenating literals with column values

[Dave Wellman]

Resend from correct account.


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: David Wellman [mailto:david.well...@ward-analytics.com] 
Sent: 31 May 2013 18:05
To: 'General Discussion of SQLite Database'
Subject: RE: [sqlite] Concatenating literals with column values

Hi Michael,

Ok, you caught me out. I actually only tried it in one (Teradata) which is
the dbms that I've worked with most over the last few years. That is
certainly not completely 'standard' in terms of what syntax it supports-
which is probably true of most dbms's - but I didn't think this area would
be different.

Anyhow, I now have sql that gives me the answer that I'm expecting.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR Registered company number: 3917021 Registered in
England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Black
Sent: 31 May 2013 17:37
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concatenating literals with column values

Your statement doesn't even work in MySQLas || is a logical operator there.
And Oracle complains about your original query:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||stepid+5,'STEPID'||5 from seqtable; select stepid
,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5,'STEPID'||5 from seqtable
   * ERROR at line 1:
ORA-01722: invalid number

And Oracle works with parentheses just like SQLite does:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||(stepid+5),'STEPID'||5 from seqtable; 

STEPID 'STEPID'||STEPID STEPID+5
-- -- --
'STEPID'||(STEPID+5)   'STEPID
-- ---
 5 STEPID510
STEPID10   STEPID5


So I'm not sure what "other" databases you're talking about.

What you're seeing is operator precedence.  || has the highest precedence so
you need the parentheses to override that.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Wellman
Sent: Friday, May 31, 2013 11:08 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concatenating literals with column values

Hi Richard,
Many thanks, that works.

Why do I need the "()" around my calculation? (apart from 'because that
makes it work' !) I've used other dbms's and don't need them there.

Cheers,
Dave



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concatenating literals with column values

[Clemens Ladisch]

Dave Wellman wrote:
> Why do I need the "()" around my calculation?

 says:
| When casting a TEXT value to INTEGER, the longest possible prefix of
| the value that can be interpreted as an integer number is extracted
| from the TEXT value and the remainder ignored. Any leading spaces in
| the TEXT value when converting from TEXT to INTEGER are ignored. If
| there is no prefix that can be interpreted as an integer number, the
| result of the conversion is 0.

So the result of 'forty-two'+0 is 0.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concatenating literals with column values

[Michael Black]

Your statement doesn't even work in MySQLas || is a logical operator there.
And Oracle complains about your original query:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||stepid+5,'STEPID'||5 from seqtable; 
select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5,'STEPID'||5
from seqtable
   *
ERROR at line 1:
ORA-01722: invalid number

And Oracle works with parentheses just like SQLite does:
SQL> select stepid  ,'STEPID'||stepid  ,stepid+5
,'STEPID'||(stepid+5),'STEPID'||5 from seqtable; 

STEPID 'STEPID'||STEPID STEPID+5
-- -- --
'STEPID'||(STEPID+5)   'STEPID
-- ---
 5 STEPID510
STEPID10   STEPID5


So I'm not sure what "other" databases you're talking about.

What you're seeing is operator precedence.  || has the highest precedence so
you need the parentheses to override that.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dave Wellman
Sent: Friday, May 31, 2013 11:08 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concatenating literals with column values

Hi Richard,
Many thanks, that works.

Why do I need the "()" around my calculation? (apart from 'because that
makes it work' !) I've used other dbms's and don't need them there.

Cheers,
Dave



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concatenating literals with column values

[Simon Slavin]

On 31 May 2013, at 4:38pm, "Dave Wellman"  wrote:

> select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5
> ,'STEPID'||5 
> 
> from seqnumber;
> 
> 
> 
> A   B   CD E
> 
> 5|STEPID5|10|5|STEPID5

Interesting:

SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE myTable (stepint INTEGER, steptext TEXT);
sqlite> INSERT INTO myTable VALUES (5,'5');
sqlite> SELECT * FROM myTable;
sqlite> SELECT 'hello mum'||stepint+5,'hello mum'||steptext+5 FROM myTable;
5|5
sqlite> SELECT ('hello mum'||stepint)+5,('hello mum'||steptext)+5 FROM myTable;
5|5
sqlite> SELECT 'hello mum'||(stepint+5),'hello mum'||(steptext+5) FROM myTable;
hello mum10|hello mum10
sqlite> SELECT 'hello mum'+stepint,'hello mum'+steptext FROM myTable;
5|5
sqlite> SELECT 'hello mum'+'hello mum' FROM myTable;
0

Okay, so the '+' operator, when either of the operators is text, considers the 
text to evaluate to 0.  Not sure what I expected.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concatenating literals with column values

[Dave Wellman]

Hi Richard,
Many thanks, that works.

Why do I need the "()" around my calculation? (apart from 'because that
makes it work' !) I've used other dbms's and don't need them there.

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: 31 May 2013 16:46
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Concatenating literals with column values

On Fri, May 31, 2013 at 11:38 AM, Dave Wellman
<dwell...@ward-analytics.com>wrote:

> Running the following SQL does not seem to give consistent results.
> select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5
> ,'STEPID'||5
>
>
Maybe for column D you intended to say:   'STEPID'||(stepid+5)

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concatenating literals with column values

[Richard Hipp]

On Fri, May 31, 2013 at 11:38 AM, Dave Wellman
wrote:

> Running the following SQL does not seem to give consistent results.
> select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5
> ,'STEPID'||5
>
>
Maybe for column D you intended to say:   'STEPID'||(stepid+5)

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Concatenating literals with column values

[Dave Wellman]

Hi,

 

Running the following SQL does not seem to give consistent results.

 

If I concatenate a literal with a column containing a numeric value then I
get expected results. (answers B and E below)

 

If however I concatenate the literal with a numeric literal added to the
column, I just get the numeric literal as my result.  (answer D below)

 

These two do not seem consistent with each other.

 

(A) (B) (C)  (D)
(E)

select stepid  ,'STEPID'||stepid  ,stepid+5  ,'STEPID'||stepid+5
,'STEPID'||5 

from seqnumber;

 

A   B   CD E

5|STEPID5|10|5|STEPID5

 

 

It is the result D that is not what I'm expecting and does not seem
consistent with the other results. I'm expecting a result of STEPID10.

 

The StepID column is defined as INTEGER and contains the value 5.

 

I am using 3.7.16.2.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion

Tel: +44 (0) 118 9740191

Fax: +44 (0) 118 9740192

www:   http://www.ward-analytics.com

 

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR

Registered company number: 3917021 Registered in England and Wales.

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users