Position in Integer 1

[WILLIEWANKA]

Why does the following statement return 3 instead of 1?


select index(123456,1);

 

[BillDHS]

select index('123456','1') ; returns 1. I'm guessing has something to do with it being a number rather than a character and the numeric sign is adding two digits.

 

[WILLIEWANKA]

Yeah, I thought the same, but try

select index(123456,3);

It returns 0...an even weirder result.

 

[dnoeth]

This is a nice one ;-)

Index/Position only works with strings, so there's an automatic typecast. 123456 is an integer, whereas 1 is a byteint.

sel format(123456), format(1);

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

Format(123456) Format(1)
------------------------------ ------------------------
-(10)9 -(3)9


So let's see how the casted string looks like:

sel '''' || 123456 || '''', '''' || 1 || '''';

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

((''''||123456)||'''') ((''''||1)||'''')
---------------------- -----------------
' 123456' ' 1'

So your query is equal to:

sel position(' 1' in ' 123456') as x,
position(' 3' in ' 123456') as y;

*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.

x y
------ ------
3 0

qed

I'll definitly use that example in my trainings, it's weirder than my usual ones

select cast(127 as char(3)), 127 (char(3));
select cast(128 as char(3)), 128 (char(3));

Dieter

 

[tdatgod]

So in reality because of the implicit type cast and the Leading Blanks it is searching for the STRING

SPACE ONE in the string
SPACE SPACE SPACE ONE TWO THREE FOUR FIVE SIX

Which is at position 3. But when you search for the string

SPACE THREE

it returns 0 because there is no SPACE THREE in the string.

Got it.

So does it give the expected answer if you use TRIM or does that require the explicit cast and therefore spoil the weirdness of the example?

 

[dnoeth]

Just add some spaces

SPACE SPACE SPACE ONE in the string
SPACE SPACE SPACE SPACE SPACE ONE TWO THREE FOUR FIVE SIX

1 -> byteint -> format '-(3)9 ' -> char(4)
12345 -> smallint -> format '-(5)9' -> char(6)
123456 -> integer -> format '-(10)9' -> char(11)

And yes, if you trim both strings it will work as expected...

Dieter

 

[WILLIEWANKA]

Thanks, the logic makes sense now.