substring(param,3)

joon

[b]myTable1[/b]                 

[COLOR=Blue]n    city [/COLOR] 
[COLOR=Red]1[/COLOR]    Tokyo 
2    New York 
5    Peking 
6    Nagoya 
7    Paris 
[COLOR=Red]41[/COLOR]   Chicago

[b]myTable4[/b]

[COLOR=Blue]param [/COLOR] 
 c=4 
 c=2 
 n=[COLOR=Red]41&k=5[/COLOR] 
 c=1 
 n=5 
 c=2 
 c=2 
 n=[COLOR=Red]1&cate=6[/COLOR] 
 c=2 
 n=7 
 c=1

I have data in myTables like the above.
The following code produces the following result.

[b]code[/b]

select city
from myTable1

join myTable4 on [COLOR=Red]substring(param,3)[/COLOR]=myTable1.n
where left(param,1)='n'
order by n

[b]result[/b]

[COLOR=Red]Tokyo[/COLOR]
Peking
Paris
[COLOR=Red]Chicago[/COLOR]

I like to know why Tokyo and Chicago are retrieved.

In my think,

Peking which has "5" in the column n is retrieved because there is n=5 in myTable4.
Paris which has "7" in the column n is retrieved because there is n=7 in myTable4.

But Tokyo which has "1" in the column n is retrieved although the substringparam,3) value "1&cate=6" is different from 1.

And Chicago which has "41" in the column n is retrieved although the substringparam,3) value "41&k=5" is different from 41.

Can you explaiin why Tokyo and Chicago are retrieved?
or
What's wrong in my think?

stewartship

Sorry could you explain a little more on what it is you are trying to do?

i am a little confused as substring(param,3) extracts the first three characters from param (i.e. par but) but you are then comparing this with myTable1.n which as far as i can tell are all numbers

and then again left(param,1)='n' but this would extract p from param and then compare with n!

I might have this all confused but i was pretty certain that was what the substring function did. Apologies if i have got it wrong.

joon

stewartship wrote:
Sorry could

i am a little confused as substring(param,3) extracts the first three characters from param (i.e. par but)

Oh I didn't say param is a column name of myTable4.

It's values are like the following.
(Third record and eightth record have long values.)

c=4
c=2
n=41&k=5
c=1
n=5
c=2
c=2
n=1&cate=6
c=2
n=7
c=1

Let's remove "c" because SQL says left(param,1)='n'
Then the following will be remained.

n=41&k=5
n=5
n=1&cate=6
n=7

Now let's make substring(param,3) because SQL says substring(param,3)=myTable1.n
I guess it will be like the following.

41&k=5
5
1&cate=6
7

Now let's see myTable.n which has the same value with among the just above.

The record COLOR=Red Peking[/COLOR] and the record COLOR=Red Paris[/COLOR] will be retrieved.

But I don't know why COLOR=Red Tokyo[/COLOR] and COLOR=Red Chicago[/COLOR] are retrieved.

41&k=5 is different from B Chicago[/B] and 1&cate=6 is also different from B Tokyo[/B].

The only the first part of 41&k=5 are same with B Chicago[/B].

The only the first part of 1&cate=6 are same with B Tokyo[/B].

Do you see why Tokyo and Chicago are retrieved although only the first part of their values are same with N value of myTable1.

Or
Do you see something wrong in my development?