Scientific notation when exporting SQL to Excel

问题:

I have a SQL statement which is exporting data to Excel. The problem is that large numbers get converted to scientific notation when exported to Excel. To avoid that I am using char(39), i.e. single quotes around the number. In addition, also checking for null value so using ISNULL.

ISNULL(char(39) + Rtrim([NumberCol]) + char(39), '')

[NumberCol] has char datatype

So now if NumberCol is a numeric value I get '000123456789' format which is what I want. But when it is Null then I get ''. I do not want to display '' in Excel rather blank. Please let me know how to do this.

回答1:

The results when null are in the right part of the function. You are specifying the two tick marks as your returned value if NumberCol is Null:

ISNULL(char(39) + Rtrim([NumberCol]) + char(39), '')

If you don't want the two tick marks, you can use a space with char(32):

ISNULL(char(39) + Rtrim([NumberCol]) + char(39), char(32))

Visually, the space will look like an empty cell in Excel.