Solved

Getting rid of exponential notation in queries

Posted on 2011-03-01
3
631 Views
Last Modified: 2012-05-11
I have a data table that has a customer number in it.  This is a number with many digits in it. I'm storing it as a number because that takes less space than a character string.

When I query CUST_ID I get data that looks like this:

1.5004770527e+011

What I want to see is

15004770527

How can I get this? I am using SQL Server 2008

Thanks
0
Comment
Question by:cipriano555
  • 3
3 Comments
 
LVL 10

Expert Comment

by:dwe761
Comment Utility

select CAST(CUST_ID as varchar(20))
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
By the way, you didn't mention what data type CUST_ID is.  If you chose BIGINT, you probably wouldn't have this problem.  However, if you want all Cust_IDs to be 11 characters with leading zeros, it is common to cast as varchar to add the leading zeros.
0
 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
Comment Utility
You must have it declared as Numeric or Real or Float.
In that case, you'll need to cast it as BIGINT.  
These examples should clarify.

declare @x real
set @x = 15004770527
select @x as x
select CAST(@x as bigint)
select CAST(@x as varchar(20))

go
declare @x bigint
set @x = 15004770527
select @x as x
select CAST(@x as varchar(20))
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now