I need HELP
"update product set price='1,99' where cod='001'"
I need COMMA... not DOT
In oracle i use "alter session set language='Brazil'"... but... in SQL SERVER???
Hello.
My database stores the decimals in Spanish format; "," (comma) as decimal separator.
I need to convert decimal nvarchar values (with comma as decimal separator) as a decimal or int.
Any Case using CAST or CONVERT, For Decimal or Int gives me the following error:
Error converting data type varchar to numeric
Any knows how to resolve.
Or any knows any parameter or similar, to indicate to the Cast or Convert, that the decimal separator is a comma instead a dot.
In Sql Server Express
I need use in field MONEY
"update product set price='1,23' where cod='001'"
i don´t use
"update product set price='1.23' where cod='001'"
"update product set price='1,99' where cod='001'"
I need COMMA... not DOT
In oracle i use "alter session set language='Brazil'"... but... in SQL SERVER???
"update product set price='1,99' where cod='001'"
I need COMMA... not DOT
In oracle i use "alter session set language='Brazil'"... but... in SQL SERVER???
i need help...
Hi
I am working on an application that uses ADO to retrieve data from SQL Server 2005. It may be used against various instances of databases and each instance contains floating point values that may used comma (',') or point ('.') as decimal. This is where the problem occurs. Depending on the database instance the strings containing floating point values also use comma or point as decimal separator.
Is there a way that by which I can force the database to return floating points with decimals separated by point? Doing a prelimimary google search, I found hints of including "Locale Identifier" in the connection string such as
Code Block
Provider=SQLOLEDB.1;Persist Security Info=True;Locale Identifier=0x0409;User ID......
but even if I specify US English locale, it does not remove the commas in floating point data.
Kind regards
Jens Ivar
I need a help in SQL Server 2000.
I am having a string variable in the format like -- (1,23,445,5,12)
I need to take single value at a time (like 1 for 1st, 23 for 2nd and so on) from the variable and update the database accordingly. This is like a FOR loop.
Can anyone help me out in splitting the variable using the comma separator...
I would like to update the final table with values into a comma separator as follows with examples:
I think I have the IDs set correctly for this example:
You can see from the final table that the code column can be a combination of more than one rows from the map tables...
create table #tblTax(TaxStatusID int, FullName varchar(20)
insert into #tblTax values(1, 'Taxable')
insert into #tblTax values(2, 'exempt')
create table #tblTypes(TypeID int, description varchar(100)
insert into #tblTypes values(1, 'cor')
insert into #tblTypes values(2, 'tyr')
[code]....
Looking at the above example, I would like to have the #tblMain as follows
#tblMain
ShoetNameLongNameClientNameTaxIDTypeID
======================================================================
A, B'dand, Barlow''johnson'133
G'mond''anderson'26
I'somelongcode''jacksons'21
A, B'dand, Barlow''smith'112
Hi,
I'm trying to import a semi-comma separated text file into a SQL db. I have a field in the text file that contains decimal number. As a decimal separator it's used a comma (15,35). When i use a DTS package to create a destination table and import all rows, the field is created as a float field. In this field the decimal comma is removed so the number in SQL becomes 1535. If I change the decimal separator to (.) i works OK. But I need to get it work with comma as decimal separator. In the DTS package the field form the text file is recognised as varchar (8000). Any ideas?
Ingar
I am using Excel 2013 64bit and use an english Excel version, but with a comma as a decimal seperator and semi-colon as a list seperator.
In Excel everything works fine, but PowerPivot does not properly recognize that I use a semi-colon for formulas.
PowerPivots lets me write formulas with the semi-colon and not the comma, so that is fine.
However, two issues appear:
the yellow smart formula help box that appears when you start typing a formula, thinks I have to use commas, so when I use semi-colons instead, it does not jump to the next parameter.This problem also causes parameters where I have to enter a table or field to not suggest me table and fields when I start typing.Sometimes the formula validation even throws me an exception, that my formula syntax is incorret, ebcause Id id not use a comma. However, commas also do not work. I have to do some weird playing around until it finally accepts my formula.
I hope this buggy behaviour gets fixed, but is there a way I can work around this without changing my formula/list seperator? I also do not want to use a German Excel version, because I am used to the english formulanames.
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places.
But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted.
How can I solve or get around this problem? (with jet if it possible)
iviczl
Hi all,
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g.
‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
Any help would be much appreciated
Thanks!
we defined a xml schema. ONe node was defined as decimal data type.
When the coming data of this node in this XML file is more than 999, then it will add a comma in the figure , looking like 1,024.00.
then SSIS can not regard this as a decimal.
Any experience to share ?
i dont wanna change the schema from decimal to string.
Thanks in advance
If I use msdb..sp_send_dbmail or save query results as text (using sqlcmd) and include the column headers I get the dashed separator line.
e.g.
custID, name
------, ------
1,bob
2,jamesI would like this
custID, name
1, bob
2 ,james
I found this method [URL] ....
::
sqlcmd -E -S (local) -d myDB -W -w 1024 -s "," -i "SELECT * FROM tblCust" | findstr /V /C:- /B > C: emp.csv
Can the same result be achieved sending as attachment with dbmail?
EXEC msdb..sp_send_dbmail @attach_query_result_as_file = 1I don't want to have to add column names as part of the query
Change the query to return column headers in resultset
SELECT 'CustID' as f1, 'name' as f2
UNION ALL
SELECT CAST(CustID as Varchar(10)), name FROM tblCustand set
msdb..sp_send_dbmail @query_result_header = 0
I have one sp which has param name as cordinatorname varchar(max)
In where condition of my sp i passed as
coordinator=(coordinatorname in (select ltrim(rtrim(value)) from dbo.fnSPLIT(@coordinatorname,',')))
But now my promblm is for @coordinatorname i have values as 'coorcinator1', 'coordinato2,inc'
So when my ssrs report taking these values as multiselect, comma seperated coordinator2,inc also has comma already.
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN,
it was never a problem in DTS 2000.
any work around.
Thanks,
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
View 2 Replies View RelatedHello!
I would like to cast (convert) data type decimal(24,4) to
decimal(21,4). I could not do this using standard casting function
CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable)
because of the following error: "Arithmetic overflow error converting
numeric to data type numeric." Is that because of possible loss of the
value?
Thanks for giving me any advice,
Ziga
I need to be able to export the data from a table to a text file using sqlcmd. I need to be able to use Ctrl-A as a column separator. Is this doable, and if so, how? I am assuming the this is a value used by the -s option.I need to use a batch file to run the sqlcmd utility.
View 6 Replies View RelatedHi,
I have a simple report with multiple grouping. I like to add a separator between groups.
Some data (Group #1)
================= <----- separator line or color row or something
Some data (Group #2)
=================
Thanks.
I have a file which contains comma separated columns. One of columns contains names of companies. Sometimes the names of the companies have a comma as part of the name. For those, the value is surrounded by double-quotes.
But it seems that SSIS ignores the double quotes and ONLY looks for the column separator. This causes my value to be split in half.
Traditionally, I thought parsers that deal with this type of import do not automatically take the first comma following the double-quote as the column separator but instead look for the first comma following the ending quote. (i.e. Look at how Excel performs imports...)
I cannot set the column separator of the column to double-quote comma since only those values that HAVE a comma in them are qualified.
Any ideas?
Here is sample fie content to see what I mean:
342123, Jason, 12345
21, Kim,4567
32.43, John Paul, 1245
23, "Mr. T", 98764
12, "Peter, Paul, Mary", 09643
The last entry should be imported as 12 in the first column, "Peter, Paul, Mary" in the second column and 09643 in the third but instead ends up as 12 in the first, "Peter in second column and Paul, Mary", 09643 in the last.
(Oddly enough, if I remove the first column of numbers the import works like it is supposed.)
Hi,
Can you tell me the query for selecting the amount with thousand separator format?
for example : select 1234.678 is need to show the output line 1,234.68
Format number thousands separator with point
I have this number in my database output result of query: 1013473
I need this output: 1.013.473
I would like to select a BIGINT type and get a formatted result with commas. Anyone have ideas?
declare @i bigint
set @i = 123456789
select @i
--Would like to get
123,456,789
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
View 6 Replies View Related
I need to store decimal values: decimal(20,15) in my SQL Server 2005 database.
I load data from flat file, convert it using Data Conversion Task to decimal(with scale: 15) and try to save it using OLE DB Destination.
It works fine for 4 digits after the decimal (like 1.1234), but always failes for more than 4 digits (1.12345).
Is the decimal limited to scale 4 ???
Thank you for your help!
Anna
Hi,
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format.
How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals
158203508540188236252EUR20BZK0030 Å“&
20060715 0001010100010101
Please help!
Thanks
Mirudhu
I have a database field which consists of a long string of values with some separator.
Ex:Â Injection^!@$#Medication
in my report i have to split the string as
Injection
Medication
till here i am able to display the results but in addition to that i have to display check box against these values like below
and this string can contain any no of values ex: if we have 4 values are separated within a string then i have to display 4 check boxes against 4 results.
Hi,
I'm trying to deploy my Web site to GoDaddy. They told me I have to export the SQL Server Express database to a comma delimited file and then upload that file. The export procedure is simple in Access but I don't see any way to do it in SQL Server or from Visual Web Developer or Visual Studio.
Also, I can ask them, but I assume I have to export each table separately and also export the ASPNETDB as well.
Thanks for the help
All,
I've a VB6 application that writes data to a SQL Server database using ADODB in the VB6 code. My market has been the United States. My application is written for the English language.
I've a new user in Germany. Unfortunately, every number that contains a decimal is now stored in the SQL Server database with the decimal dropped. For example, 29.15 gets stored as 2915. I've done some searching and found the culprit is probably the OS Regional Language setting. The Germany user is using German. So it seems ADODB is expressing "29.15" as "29,15", and SQL Server is having a fit when ADODB is trying to write to the database.
My client is reluctant to changing all their user profile language settings to German (or the other option, changing their German language setting decimal symbol option to "."). They fear other applications may be affected.
Can anyone suggest a solution on my software end? Does ADODB have some options of which I'm unaware that will ignore the regional language setting and express decimal-containing numbers simply as "."? Or is there a setting in SQL Server to compensate?
I appreciate all and any help. Thanx!
I need a query which results the output as comma separated
for example ,
select name from tablename;
say for example this produces the following,
Name
Jack
John
Smith
I need the follwoing,
Jack,John,Smith
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...