Joining a float field on an nvarchar field

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/

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/

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.

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/

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))

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;

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources