Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Joining a float field on an nvarchar field

Author  Topic 

blodzoom
Starting Member

28 Posts

Posted - 2015-01-22 : 11:44:44
I have Table1.AccountNumber nvarchar(50), Table2.AccountNumber float

As the title says... I feel like this should be easy. I tried
[code]ON Table1.AccountNumber = Convert(nvarchar,Table2.AccountNumber)[/Code]

No dice. I know that everything from table2 is in table1.

This can't be this hard, right?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-22 : 12:08:48
convert(nvarchar(50),...)

If that doesn't work, then please show us some sample data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-22 : 12:09:24
Also, you could instead convert t1.accountnumber to float.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

blodzoom
Starting Member

28 Posts

Posted - 2015-01-22 : 12:34:18
Thanks for the replies.

I can't convert t1 to float because some of the account numbers in that field have alpha chars.

I see now that the problem is converting the float to varchar gives me: 2.91791e+013 instead of 29179086100003, so that's why it's not matching. I don't know how to fix it though.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-22 : 12:46:36
Try converting to decimal first then nvarchar.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

blodzoom
Starting Member

28 Posts

Posted - 2015-01-22 : 12:48:46
It looks like I found a solution. Thank you for helping me drill further into the problem.

LTRIM(Str(t2.AccountNumber, 50, 0))
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-22 : 12:53:01
I suspect your real problem is that floats are imprecise numbers with a precision of 7 digits.

For the number given number I seem to be able to get the following to work:

DECLARE @f float = 29179086100003;

SELECT @f AS F
,CAST(CAST(@f AS bigint) AS varchar(20)) AS CF;
Go to Top of Page

blodzoom
Starting Member

28 Posts

Posted - 2015-01-22 : 13:17:05
That also seems to work. I will go with your solution since it makes more sense to me.

Thanks a lot for your help.
Go to Top of Page
   

- Advertisement -