CODEWITHSUNDEEP
sqlsql-servert-sql
RazorKillBen
RazorKillBen

Reputation: 573

How can I store a bigint as an nvarchar in a SQL Server table without scientific notation?

I need to store a numeric value in a SQL table as a string (due to some IDs containing non-numeric characters) but I am having trouble achieving this without it being stored with scientific notation, which wouldn't be a problem if I could adjust this in the view, but it loses the last few numbers when doing so.

I have tried (like you would with a query) casting it into a bigint and then an nvarchar in the INSERT INTO statement, but that doesn't affect how it is stored.

This is what I'm trying to achieve:

CREATE TABLE [dbo].[customer_comp](
    [customer_ref] [nvarchar](50) NULL,
    [customer_name] [nvarchar](255) NULL
) ON [PRIMARY]

INSERT INTO [customer_comp] 
    (customer_ref, customer_name)
VALUES
    ('199000243', 'John Smith'),
    ('CR-222-3X0D', 'Jane Smith');

Expected results:

+--------------+---------------+
| customer_ref | customer_name |
+--------------+---------------+
| 199000243    | John Smith    |
| CR-222-3X0D  | Jane Smith    |
+--------------+---------------+

Results I keep getting:

+--------------+---------------+
| customer_ref | customer_name |
+--------------+---------------+
| 1.9900e+003  | John Smith    |
| CR-222-3X0D  | Jane Smith    |
+--------------+---------------+

In the event that I'm going about this in completely the wrong way, I'm storing it this way as some values are stored in an Alpha Numeric format such as 'AAA-NNNA-NNAA' and some values are all numeric 'NNNNNNNN' so require the column to accept nvarchar.

Upvotes: 0

Views: 893

Answers (1)

RazorKillBen
RazorKillBen

Reputation: 573

Just incase anyone stumbles across this in future, you can quite simply INSERT INTO without casting the data in anyway. Casting the numeric value into a bigint and then into a nvarchar after like the below will cause the scientific notation to be stored:

CAST(CAST(customer_ref as bigint) as nvarchar(50))

Upvotes: 1

Related Questions