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?
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.
In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.
Thanks for the info.
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.
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
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...
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.
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?
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.
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.
How to get rid of the zeros in money type,
i.e. only show dollars, no cents?
I use:
CONVERT(varchar, Price, 1) AS Price
It gives me the result with 2 digits decimals. like 123,456.00
I only need the dollar part, how to get rid of the zeros, Thanks!
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 RelatedI 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!
I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).
If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.
How can I cast so it is still money? by default the entries look like 100.0000.
They must remain a money datatype.
I want to strip off the last three characters from an item number. The only thing is tht every item number is not the same lenght. The last three characters of this number are packing codes that I do not need. Fore example I can have all these numbers:
EFJ50701033
EFW1546066
RFM4925156
70561033
89541899
How would I remove the last three characters and only remain with whatever is in front of those three characters?
Any help is greatly appreciated!
Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.
View 16 Replies View RelatedHas anyone had to deal with removing punctuation (commas, apostrophes,etc.) from a column? What is the most efficient way to have thesecharacters automatically removed from the column?
View 3 Replies View RelatedHi everyone,
I have a sql quey that selectes phoneNumbers from the database. Problem is some phone numbers have hypens in it and some doesn't. Is there any way in sql so that I can remove hyphens from the phone numbers
some numbers are like this
213-456-9999
and some are
2136789999
Please let me know if this is possible.
Thanks.
I am trying to Strip off the Numbers witha Delimited List and just retain the Name of the Persons. but unable to do it. is there any function or code to do that in SQL
932908` James Fleser,935992` Prakash Sinha
Stripping off Numbers for the Above and Just retain the Names..
thanks
Question - if you had to completely strip all permissions from all databases in an instance and reset them, assuming you have metadata to support rebuilding the permissions, what steps would you follow? I can handle the iterating through each database, but at the database level, what steps would you take?
The reason I inquire on this is I currently have a job that I inherited that does just this. But it's buggy and was also written in for SQL Server 2000. With some of the changes in 2005, a few bugs have crept in, etc. And I would like to confirm my thoughts this. Or, if your opinion is why are you wasting your time on this? Then that's fine to and I'll review any constructive comments you may have.
Hi All,
I'm having trouble coming up with a function that will allow me to display only the date and time from a string in the following format:
JSMITH 1/1/2006 1:00:00AM
I've tried using substr with a negative position value, but since the date and time for each instance can be different, with it being anywhere from 18-21 characters, if the length is anything less than 20-21 characters, it will return part of the end of the username. What function can I use to only retrieve the date and time?
Hi
I have a field that starts with numbers and then has a description after if, eg.
"123 This is the description for string 123"
1. How would I go about stripping out the first instance of number to leave the string as "This is the description for string 123"
thanks.
Bill
I'm trying to strip out the First & Last Name from an Email Address. The email address is formatted as "FirstName.LastName@emailaddress.com" but sometimes a middle initial is used (ex: "First.M.Last@emailaddress.com").
I can locate the '@' sign and the first '.' period(from left to right) but need to find the first '.' from the left of the '@' sign.
I'm doing this in a view.
Here's some sample data and what I've tried:
CREATE TABLE dbo.Table1
(EmailAddr VARCHAR(255))
INSERT INTO dbo.Table1 (EmailAddr)
SELECT 'Andy.Smith@gmaddr.com'
UNION ALL
SELECT 'Betty.Lee-Jones@gmaddr.com'
[Code] .....
I have a table that contains an nvarchar column of data. The data is actually a monetary value; sometimes with a decimal point sometimes without.
My problem is that I need to strip the decimal portion of the string if it exists. From a select statement I can use:
SUBSTRING(DW_OBP_ORD_TMP_IC.VALUE,1,LEN(VALUE)-(LEN(VALUE)-CHARINDEX('.',DW_OBP_ORD_TMP_IC.VALUE)
if a decimal point exists. But if one does not the CharIndex comes back 0 and my equation does not work correctly.
I need to pull the data from the table, along with many other fields. How do I do this without using a cursor? I have millions of rows so need a solution that will be quick.
thanks in advance,
Marilyn
Hi,
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
I have a filename as follows:
123_20151016_3152_AIRHtest1.txt
What I would like to extract from this fill name is the "3152" only. What is the correct way to do this?
Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
View 2 Replies View RelatedHi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))
View 1 Replies View RelatedI 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 RelatedWould 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
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
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.
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