asked on # SQL Syntax

I have an insert statement that inserts values from an Excel spreadsheet:

insert into table (number)

values ("$Excel Column(1)$")

I would like to format the data from the spreadsheet to have leading zeros and following zeros (if that is the correct term) so that a value in the spreadsheet that appears as 2.02 would be stored in the number field as 02.020. The data field is specified as nvarchar so that it can hold the numbers and the period. I tried to format the column of cells in Excel but the formatting did not hold on the insert. The format (number,00.000) does not work in the SQL code.

Not sure what to do.

insert into table (number)

values ("$Excel Column(1)$")

I would like to format the data from the spreadsheet to have leading zeros and following zeros (if that is the correct term) so that a value in the spreadsheet that appears as 2.02 would be stored in the number field as 02.020. The data field is specified as nvarchar so that it can hold the numbers and the period. I tried to format the column of cells in Excel but the formatting did not hold on the insert. The format (number,00.000) does not work in the SQL code.

Not sure what to do.

Microsoft SQL ServerMicrosoft SQL Server 2008

>I would like to format the data from the spreadsheet to have leading zeros and following zeros

Spell out for us at Barney-level why you want this, and specifically why it has to be inserted into the table this way.

You can always write T-SQL code to format it with leading zeros for various reports, but it's not considered a best practice to store numbers with leading zeros as a varchar. Now if all you're doing is storing the values, and not performing any math on them, then different story.

>I tried to format the column of cells in Excel but the formatting did not hold on the insert.

Is data moving FROM Excel TO SQL Server, or the other way around?

Spell out for us at Barney-level why you want this, and specifically why it has to be inserted into the table this way.

You can always write T-SQL code to format it with leading zeros for various reports, but it's not considered a best practice to store numbers with leading zeros as a varchar. Now if all you're doing is storing the values, and not performing any math on them, then different story.

>I tried to format the column of cells in Excel but the formatting did not hold on the insert.

Is data moving FROM Excel TO SQL Server, or the other way around?

why this should be so ugly?

declare @n float = 2.03;

select substring('00' + cast (@n as varchar) + '000', charindex('.', '00' + cast (@n as varchar))-2, 6)

02.030

declare @n float = 2.03;

select substring('00' + cast (@n as varchar) + '000', charindex('.', '00' + cast (@n as varchar))-2, 6)

02.030

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

table (number)

so table column datatype is numeric? or varchar(6)?

so table column datatype is numeric? or varchar(6)?

REPLACE(STR([$Excel Column(1)$], 6, 3), ' ', '0')

Or, more fully:

insert into table1 (number)

values (REPLACE(STR([$Excel Column(1)$], 6, 3), ' ', '0'))

For example:

select [$Excel Column(1)$],REPLACE(STR([$Excel Column(1)$], 6, 3), ' ', '0')

from (

select 2.02 as [$Excel Column(1)$] union all

select 0.05 union all

select 21.3 union all

select 7

) as test_data

Or, more fully:

insert into table1 (number)

values (REPLACE(STR([$Excel Column(1)$], 6, 3), ' ', '0'))

For example:

select [$Excel Column(1)$],REPLACE(STR([$

from (

select 2.02 as [$Excel Column(1)$] union all

select 0.05 union all

select 21.3 union all

select 7

) as test_data

I have data in other databases where the data has been stored 00.000 and I need to be able to perform joins on this field.

The data is in an Excel spreadsheet and going to SQL.

I am not sure what "Barney-level" means, but i am simply trying to have the data in a format similar to other databases that I would join on.

The data is in an Excel spreadsheet and going to SQL.

I am not sure what "Barney-level" means, but i am simply trying to have the data in a format similar to other databases that I would join on.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

the code I posted ^^^ is what you are trying to get... ugly but it should work as long as your input is correct...

The column that the data is inserted into is nvarchase because the value is two digits, a period, and three digits with leading zeros and zeros on the end to make it three digits after the period.

HainKurt, not sure how to integrate your code into an insert.

HainKurt, not sure how to integrate your code into an insert.

should be like:

insert into table (number)

values (substring('00' + cast (@n as varchar) + '000', charindex('.', '00' + cast ("$Excel Column(1)$" as varchar))-2, 6)

insert into table (number)

values (substring('00' + cast (@n as varchar) + '000', charindex('.', '00' + cast ("$Excel Column(1)$" as varchar))-2, 6)

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

HainKurt, I am not sure I follow your logic. I get a substring function requires 3 arguments error when trying to copy your code into my example. I added a declare line before the insert command, but not sure how that applies to dynamically getting a value out of the spreadsheet.

Can you clarify please?

Can you clarify please?

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
Anyway, you can use RIGHT function:

Open in new window