Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Syntax

Posted on 2014-11-24
11
Medium Priority
?
197 Views
Last Modified: 2014-11-24
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.
0
Comment
Question by:mounty95
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40462619
I personally don't like to use SQL Server for formatting. For be a database it's only for store data and the formatting should be with the application layer.
Anyway, you can use RIGHT function:
insert into table (number)
 values (RIGHT('00'+ CONVERT(VARCHAR,"$Excel Column(1)$"),3)) 

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40462623
>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?
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40462632
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 59

Expert Comment

by:HainKurt
ID: 40462639
table (number)

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

Expert Comment

by:Scott Pletcher
ID: 40462656
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
0
 

Author Comment

by:mounty95
ID: 40462683
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.
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40462688
the code I posted ^^^ is what you are trying to get... ugly but it should work as long as your input is correct...
0
 

Author Comment

by:mounty95
ID: 40462783
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.
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40462833
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)
0
 

Author Comment

by:mounty95
ID: 40462908
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?
0
 
LVL 59

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 40462956
you said you are using this:

insert into table (number)
values ("$Excel Column(1)$")

if this works, then this one should work too:

insert into table (number)
values (substring('00' + cast ("$Excel Column(1)$" as varchar) + '000', charindex('.', '00' + cast ("$Excel Column(1)$" as varchar))-2, 6)

i forgot to replace some test variable here :)
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question