2

I have a SQL Server Compact (3.5) database with a nvarchar column with lots of data that looks like 000000000011070876. I'm trying to copy that data to another column that is a BIGINT, using the CONVERT function.

My first try was:

UPDATE Mobile_Reservation 
SET SAPNo = CONVERT(BIGINT, ItemNumber)

If I run this query in SQL Server 2008 R2, it works fine. 000000000011070876 becomes 11070876. Unfortunately, in SQL Server CE, it becomes 0. Apparently it cannot handle the leading zeros. But it will turn 000000004000010576 into 40, which I assumed meant it was only looking at the first 10 digits. Then I tried:

UPDATE Mobile_Reservation 
SET SAPNo = CONVERT(BIGINT, SUBSTRING(ItemNumber, 8, 10))

With a start index of 8, I assumed it would start just before the 4 (The first 8 digits are always 0s, but may be more than 8 0s). This worked somewhat better, but not successfully. 000000000011070876 became 1107 and 000000004000010576 became 40000105.

Then I tried the hardcoded string:

UPDATE Mobile_Reservation 
SET SAPNo = CONVERT(BIGINT, '4000010576')

And this worked fine, which confused me even more. I tried a few different combinations of strings, and the logic it seems to use is: for every leading 0 in the string, a char from the other end is removed. '1234' becomes 1234, but '01234' becomes 123. But it's not a hard fast rule, because 04000010576 becomes 40000105, which means the single leading 0 is removing two digits from the end...

Is this a problem with SQL Server CE's implementation of CONVERT, or perhaps something else I'm not noticing? Any thoughts on how to fix this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cost
  • 4,420
  • 8
  • 48
  • 80
  • I noticed in the [documentation](http://msdn.microsoft.com/en-us/library/ms174450(v=sql.110).aspx) that convert on nvarchar can impose a 30 character maximum. This kind of jives with your observation that every left-padding 0 chops a character off the right -- what is the size of your nvarchar field? Is it only 18? or something bigger. Documentation on [update](http://msdn.microsoft.com/en-gb/library/ms174122.aspx) mentions that nchar fields can be padded to their defined length -- which is a weird thing to say -- but I wonder if some under the hood string manipulations are messing you up. – Ryan B. Nov 04 '14 at 22:58
  • @Greenspark That's certainly a good thought, there's definitely something strange happening under the hood.. If I have some time I might take a look at the SQL CE library to see how the actual Convert function is handled. – cost Nov 05 '14 at 00:19

2 Answers2

2

I wound up solving this problem with:

UPDATE Mobile_Reservation 
SET SAPNo = CONVERT(BIGINT, REPLACE(LTRIM(REPLACE(ItemNumber, '0', ' ')), ' ', '0'))

Not the nicest solution, but it works.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
cost
  • 4,420
  • 8
  • 48
  • 80
0

There is an implicit conversion between bigint and nvarchar() -- what about just trying 

UPDATE Mobile_Reservation SET SAPNo = ItemNumber

If not that, then sometimes you also can get away with something like

UPDATE Mobile_Reservation SET SAPNo = 0 + ItemNumber

I'm sorry I don't have SQL CE to test on.

Ryan B.
  • 3,575
  • 2
  • 20
  • 26
  • Unfortunately SQL CE is considerably less.. nice than full SQL Server. The implicit conversions fail in the same way – cost Nov 05 '14 at 00:17