SQL Server 2000, Truncating Trailing Zeros

Jan 28, 2008

I'm uploading data from a CSV file into SQL Server 2000 using an upload routine in C# (ASP.NET 2.0). I'm using an OleDbDataAdapter to select all the data in the CSV file into a DataTable. I then use a SQLBulkCopy to copy all the data into my SQL Server table. The CSV file maps exactly to the fields in the SQL Server database and I've used a custom delimiter of a '~' (tilda) that is declared in a schema.ini file.

The import works fine and my SQL Server table is fully populated with all the data. However, one source field in the CSV file is getting truncated in my SQL Server table. It is a field containing a value e.g. 32,000, 64,500 but this is getting truncated and losing the trailing zeros in SQL Server i.e. 32,000 (CSV) becomes 32 (SQL Server) and 64,500 becomes 65,5.

I've tried mapping the source field to a varchar, nvarchar and text field, but all data types have the same problem with truncating the trailing zeros (I only need to display the data - no calculations required) and the ',' in the number in the CSV file has prevented me from mapping this to an integer.

Also, locally the code works fine and my local database is SQL Server 2005, but the remote host is using SQL Server 2000 and this is where the problem occurs.

Any help would be appreciated! Thanks

View 5 Replies


ADVERTISEMENT

SQL Server 2014 :: How To Remove Trailing Zeros From A Decimal (type) Value

Mar 16, 2014

I would like to 'drop' some trailing zeros from a decimal value, e.g.: 50.000000, and I am wondering how to go about this?

The value is definitely of decimal type, and in this instance I know that I want to eliminate exactly six (6) zeros.

View 4 Replies View Related

Trailing Zeros

Jun 27, 2006

Hi All,

Can anybody tell me how to remove the trailing zeros from the numeric field?

The data looks like this:

1.0000000
24.0000000
2356.0000000
61.0000000

It should look like this:

1
24
2356
61

Thanks.

View 3 Replies View Related

How To Add Trailing Zeros 2 Decimals

May 18, 2008

In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.

Thanks for the info.

View 1 Replies View Related

How To Add Trailing Zeros If There Is No Decimals

May 19, 2008

How can i add two trailing decimals if there is no decimals:

for example if it is just 1, then make it 1.00

if it is 1.12 then leave it as it is.

can you please help.

select cast(ordernumber as varchar(10)) + '00' from ordertable

Thanks for the info.

View 1 Replies View Related

Remove Trailing Zeros From Text Field?

Mar 10, 2014

in our database is saved by 6 decimal places, whether the value has no values ??in decimal position. Field type is of type nvarchar.

How do I remove these "laggards spirit" in the best and smartest way.

Ex:

100.000000 will be 100
100.001000 will be 100.001
100.000001 will be be 100.000001
100.100000 will be be 100.1
and so on...

View 3 Replies View Related

How To Remove Trailing Zeros From Decimal (type)

Mar 16, 2014

I would like to 'drop' some trailing zeros from a decimal value, e.g.: 50.000000, and I am wondering how to go about this?

The value is definitely of decimal type, and in this instance I know that I want to eliminate exactly six (6) zeros.

View 1 Replies View Related

T-SQL (SS2K8) :: Getting Undesirable Trailing Zeros After Casting To Decimal

Sep 7, 2014

The following line of SQL returns the first screen shot below.

((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS 'Gross Profit %'

I now want to limit the decimal places to two (2), and one might think that using a CAST operation here is the solution, as follows.

CAST(((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS decimal(15,2)) AS 'Gross Profit %'

However in actuality I am still seeing too many decimal places, but the extra ones have changed to ZEROS!

Ideally I would like to have no extra trailing zeros. How to achieve this?

View 2 Replies View Related

Export From SQL Money To DT_CY Stripping Off Trailing Zeros

Sep 28, 2007



I am exporting from a SQL money field to a flat file. The datatype is set to DT_CY in SSIS. I need two decimal places in the flat file but instead I am getting 1.1 instead of 1.10.

Should I be using a different datatype or is there a way to set the number of decimals on the DT_CY datatype?

View 1 Replies View Related

Maintain Leading Zeros When Exporting To .csv Using SQL Server 2000 DTS

Jan 13, 2008

I am trying to export the result of a select into a .csv file using SQL Server 2000 DTS. The data for varchar fields has leading zeroes in the database, which is very much required in the csv file.

But, the .csv file trims the leading zeroes. How do we force to maintain the same data as in source?

I had used Text File Destination Connection as the destination, with the below options
File Extension: .csv
File Format: Delimited
File Type: ANSI
Text Qualifier: Double Quotes ("")
Row Delimiter: {CR}{LF}
Column Delimiter: comma

Source Data: 0123
Target Data (Requirement): 0123

The data in .csv: 123 (This is the issue)

When I open this file in a Text Editor, I do see the data in double quotes..."0123".

Thanks in advance.

View 6 Replies View Related

Cursed With Trailing Whitespace Or Trailing 0's

May 14, 2006

I have a databound textbox that is used to store a decimal value.

If my sql table stores this column as a decimal(2,2), then all of the numbers entered into the field will automatically put decimal places in that I don't want. For example, 45 becomes 45.00... 34.5 becomes 34.50.

If I set the sql table to nchar(10) and the dataset to system.string (max length of -1), then the number looks the way I would like it, however after a datatable update I end up with trailing whitespace after the number - filling up the rest of the unused 10 characters. For example, "45" becomes "45 " (8 spaces afterwards).

Does anybody know how I can fix this? I would prefer to store the numbers in SQL as a string (nchar(10))... but I don't know how to get rid of that darned whitespace. I would like to remove it at the database level and not at the client level if at all possible.

Thanks!

View 1 Replies View Related

Optins Of Trailing Database Change In Sql Server

Oct 28, 2006

Hi All,

I am in need of some option using which i can trail the changes made to my database. Specially i want to track all the DDL and DMl changes.

I know about the external tools provided by lumigent and some other companies to do this but i was just wondering if we have some option within sql server.

I don't need extensive report as provided by lumigent.

Just getting somethin on DDL and DML will help. 

Even help on some work arounds to do same will be appreciated.

TIA

Prashant

 

View 12 Replies View Related

Truncating Data On A Remote Server?

Jul 15, 2013

I am trying to truncate data from one server to the other (remote server)

it works from query analyzer like this:

exec RemoteServerName.DatabaseName.DBO.sp_executesql "truncate table DBO.tablename"

it does NOT work from query analyzer like this:

Declare @cmd varchar(2000)
set @cmd = @servername + '.' + @databasename + '_Archive'+ '.' + @schema + '.' + 'sp_executesql' + ' ' + '"' +'truncate table' + ' ' + @schema + '.' + @tablename + '"'
exec xp_cmdshell @cmd

Produced this error:

'RemoteServerName.DatabaseName.DBO.sp_executesql' is not recognized as an internal or external command, operable program or batch file.

View 4 Replies View Related

SQL Server 2012 :: Padding Zeros After Decimal

Jan 29, 2014

I have a table like

[A]-------------[B]
[0]-------------[0.012345]
[1]-------------[0.002345]
[0.2145]------[0.1457]

I need both columns A and B to be padded with zeros at the end upto 9th place after decimal so the output should look like:

[A]------------------------[B]
[0.000000000]-----------[0.012345000]
[1.000000000]-----------[0.002345670]
[0.214500000]-----------[0.145700000]

Note: total number of digits = 10 not including '.'decimal

I tried this but just getting 11 zeros.. it work when i want zeros in the left but not on the right

select right(REPLICATE('0', 11) + CAST([table] AS VARCHAR(11)),11)

from #table

View 4 Replies View Related

SQL Server 2012 :: INT Or BIGINT With Leading Zeros

Nov 5, 2015

Looking at an execution plan the conversion of NVARCHAR(15) to BIGINT is a big yellow exclamation NO NO. However, the numbers in the NVARCHAR(15) have leading zeros.

Technically speak 0123456789 is not an INTEGER or BIGINT, the performance of my Stored Procs is there any way to allow leading zeros in a BIGINT Field?

View 5 Replies View Related

SQL Server 2K Truncating Text Fields At 255 Chars

Jul 20, 2005

I'm running SQL Server 2K (sp3a) and when I run the following query (inquery analyzer):SELECT id, LEN(ForeignWord) as Length, ForeignWordFROM WordsWHERE Language ='Thai' and LEN(ForeignWord) > 300ORDER BY Length descI receive the following results:id Length ForeignWord------- ----------- -----------34756 445 เ&#35...truncated at 25534839 412 เ&#36...truncated at 25537613 350 โ&#35...truncated at 25537808 315 โ&#35...truncated at 25538140 315 โ&#35...truncated at 255(The ForeignWord field is defined as varchar(1000))Note that even though the server says that the lengths are in excess of255 characters, the results are all truncated at 255 characters.I read that SQL Server 6.5 and 7.0 had some issues related to nativeaccess versus OleDb or ODBC, but this is Server 2K patched with thelatest updates.Any idea why my varchar fields are being truncated?-- RickHere is my table definition:CREATE TABLE [Words] ([ID] [bigint] IDENTITY (1, 1) NOT NULL ,[Language] [varchar] (50) NOT NULL ,[Module] [int] NOT NULL ,[Lesson] [int] NOT NULL ,[EnglishWord] [varchar] (1000) NOT NULL ,[ForeignWord] [varchar] (1000) NOT NULL ,[Note] [varchar] (2000) NULL ,[Military] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Military]DEFAULT (0),[Supplemental] [tinyint] NOT NULL CONSTRAINT [DF_Table1_Supplemental] DEFAULT (0),[SoundFileName] [varchar] (1000) NULL)

View 4 Replies View Related

SQL Server 2008 :: SSMS Truncating PowerShell Output?

Jun 3, 2015

When I execute the following command, I get the output truncated to 79 characters, including three dots (as an ellipsis, I suppose).

EXEC master..xp_cmdshell 'powershell.exe "Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255"'When I execute the core command directly in Powershell, whether the text or ISE version, it works correctly, with or without the out-string -width command.

Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255What does it take to get SSMS to not truncate my output strings?

View 6 Replies View Related

SQL Server 2008 :: Adding Zeros Into Date Column

Oct 1, 2015

I have a date column (not datetime) in SQL Server and the dates are in the format yyyy-mm-dd

I need to replace the date values with zero's so it looks like 0000-00-00

I can currently get the current date in to the column like this:

UPDATE TestTable_1
set datecolumn = CONVERT(varchar(10), GETDATE(), 10)

But instead of GETDATE() I need to enter the zero's like this: 0000-00-00

How can I do this?

View 7 Replies View Related

SSIS Leading Zeros Gone After Migration Of Mainframe To SQL Server

May 22, 2007

I am migrating mainframe data to SQL Server 2005 and have found that from a mainframe character field with leading zeros for example the value of 00023 to a SQL Server column defined as varchar (5) the resulting column value is 23 not 00023. I need the leading zeros because these are account ids, etc. So the value is 00023 not 23. Is this some setting in SQL Server 2005 that needs to be changed or what? This is not a numeric field on the mainframe or a numeric column for SQL Server.

View 11 Replies View Related

Trailing 0's

Jul 20, 2005

I have a column defined as DECIMAL(10,6). I want to display it as astring but I do not want the trailing zeros. I cannot seem to get CASTor CONVERT or STR to exclude the zeros.Examples:45.340000 --> 45.3427.700000 --> 27.755.000000 --> 55Is there a function that will do this or do I need to write my own?

View 5 Replies View Related

Remove Trailing Zero

Jun 11, 2008

Would someone mind helping me with formatting a string please??
I have a column DECIMAL(4,2).  In a stored proc I am selecting this field and converting it to varchar so I can append certain characters to it (this is really irrelevant to my question).  However, before appending the characters, I need to remove zeros after the decimal point.
Examples:
3.00 should be 3
3.20 should be 3.2
3.05 should be 3.05
etc
 

View 6 Replies View Related

Trailing Spaces

Apr 28, 2003

If I run SELECT Len(' ') it returns 0, if SELECT Len('a ') it returns 1
I need this to return the correct length including the space that on the end. I thought it was an ansi_padding problem but even turning padding on results in a 0 length. Any ideas? Thanks!

Todd

View 2 Replies View Related

Delete Trailing ''

Aug 20, 2007



Hi,
I need to delete trailing slashes ('') from values in a specified column. Something like what TRIM does for spaces, I want to do it for trailing slashes. I have column values such as Rajat, Rajneesh, Ankush, Sudheer ... etc. As a result, I need to have the column values as Rajat, Rajneesh, Ankush, Sudheer ...

Hope the question is clear. Please help me at the earliest. Thanks a lot in advance.

View 3 Replies View Related

Trailing Spaces - Such A Pain...

Dec 31, 2006

Hi All...  I'm using a SQL Server 2005 database.  I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out.  I guess I can understand why...  But it's a pain dealing with it.   As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet.  What's the easiest/best way to get rid of those spaces.  Geez, it'd be real cool if I could put something in the SELECT statement.  Any thoughts?  Thanks much!!  -- Curt
 

View 1 Replies View Related

DT_Str And Trailing Whitespace?

Apr 26, 2007

I have a OLE DB Source going to a flat file destination. My source is a sql variable with "select * from tablename" which have varchar datatypes. Yet I'm getting trailing spaces at the end of some of my columns (for instance, my address column).

I've checked the data by doing a "select Max(Len(address)) from tablename" and the max is only like 34 chars, yet each of them have 100 chars total.

Taking a look at my flat file connection, the outputColumnWidth is 100, and datatype is string [DT_STR]. Am I crazy? What's the problem here? Is the DT_STR datatype the equivalent of char, and not varchar?

Any help, of course, will be appreciated.

View 8 Replies View Related

About Trailing Space In The Record

May 28, 2007

Hi, there;
We know that: Both select * from mytable where column1="data" and select * from mytable where column1="data " give us same result. (Please note the spaces in second query) This means the trailing space doesn't affect the query result.

How can I make SQL to return different result?



Thanks.

View 6 Replies View Related

How To Retain Trailing Zero On Money Value?

Oct 11, 2006

Simple scenario. I have a DB table with a money column. SSIS package with a data flow task which has an OLE DB source and flat file destination. I want the single money column from the database into the flat file.

If a (monetary) value in the table is 123.50, the I want 123.50 to appear in the file, not 123.5. Any ideas how I do this? I've tried all available data types, set the scale to 2 where available but nothing has an effect.

Any thoughts?

Greg.

View 1 Replies View Related

Trailing Spaces Added To All Fields

Mar 15, 2006

For some reason, there are extra trailing spaces being added to all my
data as it is placed in the db.  I am collecting information from,
processing a TRIM-like function in javascript, and then again in the
ASP.net code before it is being placed in the db. 

The extra spaces are causing problems with my application.

Any help would be greatly appreciated.

View 3 Replies View Related

Move Trailing Spaces To Front

Aug 29, 2001

I have a column that is varchar(12) that the data was entered left justified
such as '12345 ' with trailing spaces. I need to move the number to be right justified to link with another table so it looks like ' 12345'. I looked at the right command and could not find a solution. An ideas?
Thanks

View 1 Replies View Related

How To Get Values From Column With Trailing Spaces

Jul 23, 2013

Performing security audit using command to retrieve data from Active Director about security groups and drop results into local tbl for analysis.

EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'

Problem is the col created to store result is varchar(1000) and can hold 1-3 values (loginIDs) per row with lots of trailing/white space.

E.g. (EmpID101, EmpID250 EmpID10)

Is there a technique to extract the needed value (loginIDs) from col?

View 4 Replies View Related

Export Data Without Trailing Spaces

Jul 23, 2005

Hello,when I export data from a table to a text file, I get trailing spacesif the data type in char. (This dosen't happen if the data type isvarchar). I can get rid of the spaces by using the trim() function onevery signle column. here is an example:DTSDestination("first_name") = DTSSource("last_name")My question is:Is there any easier way to get ride of the training spaces for allcolumns when exporing a table? It is too time consuming if I have totype trim() for every single column in the table.Thank you in advance,Eddy

View 1 Replies View Related

Getting Rid Of Trailing Zeroes From A Decimal Output

Feb 1, 2006

HiI use SqlServer 2000I am doing a select and sending the results, which is a cast() intodecimal (9,3), in an email to various other users of our system.Problem is that a number like 95.2 is display as 95.200. Is there anyway I can trim it so that it will display 95.2 ?David

View 1 Replies View Related

How To Concatenate Strings That Have Trailing Spaces?

Jul 20, 2005

I am trying to export data from a SQLServer database into a text fileusing a stored procedure. I want to be able to read it and debug iteasily; therefore, I want all the columns to indent nicely. This meansI need to append trailing spaces to a text string (such as "Test1 ")or append leading space in front of a text string that contains anumber (such as " 12.00"). Now, the stored procedure works fine whenI run it in Query Analyzer. But it doesn't work correctly when I runit using ISQL - All the columns are not indented. I am wondering whyit doesn't work in ISQL.This is what I want, and this is also what I get when I run the storedprocedure using Query Analyzer:Test1 , 2,Test1.txt , 1.00, 1.00Test22 , 2,Test22.txt , ,Test333 , 2,Test333.txt , 30.00, 30.00This is what I get if I run the stored procedure using ISQL(isql -S myserver -E -w 556 -h-1 -n -d mydb -Q "exec MyTest"):Test1, 2,Test1.txt, 1.00, 1.00Test22, 2,Test22.txt, ,Test333, 2,Test333.txt, 30.00, 30.00You can see that the result from ISQL has the following differences:1. It puts a space in front of each row.2. It appends enough spaces at the end of each line to makethe line length to be exactly 61 characters.3. It gets rid of the trailing space from each column.4. It leaves only one blank space if the column has nothingbut a serie of spaces.The following is the stored procedure that I am testing:create procedure MyTestasset nocount oncreate table #Test(Field1 varchar(10) null,Field2 varchar( 5) null,Field3 varchar(20) null,Field4 varchar(10) null,Field5 varchar(10) null)insert into #Test values( "Test1 ", " 2","Test1.txt ", " 1.00", " 1.00" )insert into #Test values( "Test22 ", " 2","Test22.txt ", " ", " " )insert into #Test values( "Test333 ", " 2","Test333.txt ", " 30.00", " 30.00" )select Field1 + "," +Field2 + "," +Field3 + "," +Field4 + "," +Field5from #Testdrop table #TestgoStrangely, the differences #3 and #4 only show up when I use theSELECT statement on a table. They don't show up when I use SELECTstatements to show constant text strings or string variables, likethis:set nocount onselect "Test1 " + "," +" 2" + "," +"Test1.txt " + "," +" 1.00" + "," +" 1.00"select "Test22 " + "," +" 2" + "," +"Test22.txt " + "," +" " + "," +" "select "Test333 " + "," +" 2" + "," +"Test333.txt " + "," +" 30.00" + "," +" 30.00"The result is like the following if I use constant text strings orstring variables:Test1 , 2,Test1.txt , 1.00, 1.00Test22 , 2,Test22.txt , ,Test333 , 2,Test333.txt , 30.00, 30.00I need to run it from ISQL because that is how I run _all_ my otherstored procedures. I don't want to do anything differently justbecause I need to run this stored procedure.Thanks in advance for any suggestion.Jay Chan

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved