Joining Tables on Different Data Formats

Transact-SQL
1

I'm trying to join a table on a number that is in a number format, number (9,0) with a table with the number in varchar format, varchar (10,0). The biggest problem I'm encountering is that the varchar number has a hyphen before the last two digits (i.e., 999999-99). I'm not getting an error, but the script I'm using returns no results. Any ideas how to fix this? Thanks!

select *
from rp.r_bills rb

join st.a_assistant aa
on to_char(rb.ga30_nbr, '99999999') = replace(aa.ga_30, '-', '')
and rb.liab_amt = aa.net_result

2

Look for fields not in format 999999-99:

select *
  from rp.r_bills
 where isnumeric(replace(aa.ga_30,'-',''))=0
3

I got this error message

"ISNUMERIC": invalid identifier
00904. 00000 - "%s: invalid identifier"

4

Which version of Microsoft SQL Server are you running?

5

I'm actually using Oracle SQL Developer. I'm pretty new to SQL, so I wasn't sure which category to select.

6

Bit crude, but this might find a non-numeric value:

select *
  from rp.r_bills
 where replace(aa.ga_30,'-','') LIKE '%[^0-9]%'

It will give false-positive for a leading minus sign, or an embedded decimal point (which, given the "hyphen" substitution I presume there won't be one)

7

I'm still struggling. I think I'm getting a bit confused. The where statement is using the second table that isn't joined yet. I'm sure I'm missing a step here.

8

As this is a Microsoft SQL Server forum you are probably going to be better off on an Oracle forum where folk will be able to suggest the correct syntax for the string manipulation you need to perform.

9

Would you happen to know of a good one?

10

You may just be a digit short in your format, as it only shows 8 digits not 9:

select *
from rp.r_bills rb

join st.a_assistant aa
on to_char(rb.ga30_nbr, '999999999') = replace(aa.ga_30, '-', '')
and rb.liab_amt = aa.net_result

11

Is there some reason not to change that hyphen to a decimal point, and then convert the string to NUMERIC, and then making the comparison, rather than converting numeric to string for the comparison?

I suppose numeric-to-string and compare-string avoids any issue with the string value NOT being a valid numeric