Char Vs Int Issue (Like predicate confusion)

[logic4fun]

hi all,
i have the following two tables

Tab A with a single column X CHAR(4)
-----
100
100a
200
200b

and

TAB B with two columns X INT, Y CHAR(10)
-----------------------------------
100 junk1
200 junk2

i need to join the above two tables to get the following output

X.TAB A --- Y.TAB B
100 -- junk1
100a -- junk1
200 -- junk2
200b -- junk2


so basically 100 and 100a is same for joing and 200 and 200b are same coz i only need to consider the integer portion..but in the table it is declared as char and as we all know i cant cast it to int so that i can get integer part. somehow i need to make work in the query..using like predicate..

Any Suggestions...

thanks in advance
logic4fun

 

[rac2]

Exactly how to do that will depend on the functions available with your particular RDBMS. With MS SQL Server here are some ways to match up characters and numbers.

DECLARE @x CHAR(4)
SET @x = '100a'

IF CONVERT(INT,SUBSTRING(@x,1,3)) = CAST('100' AS INT)
SELECT @x, SUBSTRING(@x,1,3),CONVERT(INT,SUBSTRING(@x,1,3))
...
JOIN TabB ON
    CONVERT( INT, SUBSTRING(TabA.X,1,3) ) = TabB.X

 

[logic4fun]

there is not a compulsion that the digits are exactly 3 or 4 it can be 100 or 1000 or 10..so substring may not be right choice..and also..i am using DB2

thanks
logic4fun

 

[PruSQLer]

I think there may be a way to get what you want but first a question - how can you have '1000a' in a CHAR(4) field? It sounds like you're saying that in your 2nd note.

 

[logic4fun]

SORRY...there is no 1000 ..It is 4 Characters only and the Extra field Such as a or b will be always in the end.

Hope i am clear.

 

[logic4fun]

My bad..i meant No 1000a

 

[JamesLean]

Will there only ever be a single letter after the number? If so, then this solution works on SQL Server - not sure about DB2, you'll have to try it:

select a.x, b.y
from #a a join #b b on
  case
    when isnumeric(right(rtrim(a.x), 1)) = 0 then left(a.x, len(a.x) - 1)
    else rtrim(a.x)
  end = cast(b.x as varchar)

--James

 

[MarcLodge]

Hi Logic,
You could try:

SELECT A.X , B.Y
FROM TABA A, TABB B
WHERE SUBSTR(A.X,1,3) = SUBSTR(CHAR(B.Y),1,3)

which I think would give you what you want.

Marc

 

[PruSQLer]

I tried the below code and it worked. It uses a Table Expression, ever use one? They're easy. You can't use the LIKE predicate for this problem.

With Temp (field1, field2)
As (
select field1
, rtrim(translate(field1,'' ,'ab') )
From tableA )

Select temp.field1
, b.field2
from temp
, tableb b
where temp.field2 = char(b.field1 )

 

[logic4fun]

Thanks Guys..I could make it to work..by using ur suggestions..
thanks
logic4fun