Numeric value with comma separator...

Hi,
In select statement how will i get the numeric values with comma separated
format .
Is there any sql function available.
Regards,
M. SubbaiahSomeone was asleep in their Database 101 class! What is the **most
fundamental** concept in tiered architecture? DISPLAY IS ALWAYS DONE
IN THE CLIENT SIDE!!
Can you please stop programming until you have read at least one book?|||Hi
As Celko pointed out yet you will be better of douing such reports on the
client side
However T-SQL has an ability to do that .
CREATE TABLE #Test (col INT NOT NULL)
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (10)
INSERT INTO #Test VALUES (20)
DECLARE @.st VARCHAR(20)
SET @.st=''
SELECT @.st=@.st+COALESCE(CAST(col AS VARCHAR(5)),'0')+','
FROM #test
SELECT LEFT(@.st,LEN(@.st)-1)
"Subbaiah" <subbaiah@.cspl.com> wrote in message
news:eqflLkeMGHA.3272@.tk2msftngp13.phx.gbl...
> Hi,
> In select statement how will i get the numeric values with comma separated
> format .
> Is there any sql function available.
> Regards,
> M. Subbaiah
>|||Hi Uri Dimant,
Thanks for your information.
I learned new sql function COALESCE( ) and the usage.
My posted query was ,
Suppose in sql table the value is 1234567.45
My out put wiill be 1,234,567.45
Can you please answer the above one.
Regards
M. Subbaiah
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi
> As Celko pointed out yet you will be better of douing such reports on the
> client side
> However T-SQL has an ability to do that .
> CREATE TABLE #Test (col INT NOT NULL)
> INSERT INTO #Test VALUES (1)
> INSERT INTO #Test VALUES (10)
> INSERT INTO #Test VALUES (20)
>
> DECLARE @.st VARCHAR(20)
> SET @.st=''
> SELECT @.st=@.st+COALESCE(CAST(col AS VARCHAR(5)),'0')+','
> FROM #test
> SELECT LEFT(@.st,LEN(@.st)-1)
>
>
>
> "Subbaiah" <subbaiah@.cspl.com> wrote in message
> news:eqflLkeMGHA.3272@.tk2msftngp13.phx.gbl...
>|||NO!
Display is ALWAYS done where it is most efficient to do it.
You DO NOT pull back 1 MILLION rows into your middle tier or client tier
only to grab page 2 of 10!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139978481.527767.63680@.z14g2000cwz.googlegroups.com...
> Someone was asleep in their Database 101 class! What is the **most
> fundamental** concept in tiered architecture? DISPLAY IS ALWAYS DONE
> IN THE CLIENT SIDE!!
> Can you please stop programming until you have read at least one book?
>|||If you want to cheat, use the money data type and convert:
declare @.someFloat money
set @.someFloat = 1234567.45
select convert(varchar(15), @.someFloat, 1)
Gives:
1,234,567.45
Cheers,
Stefan
http://www.fotia.co.uk
> Hi Uri Dimant,
> Thanks for your information.
> I learned new sql function COALESCE( ) and the usage.
> My posted query was ,
> Suppose in sql table the value is 1234567.45
> My out put wiill be 1,234,567.45
> Can you please answer the above one.
> Regards
> M. Subbaiah
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
>|||Hi
declare @.someDEC DECIMAL(18,2)
set @.someDEC = 1234567.45
SELECT CONVERT(VARCHAR,CAST(@.someDEC AS MONEY),1)
"Subbaiah" <subbaiah@.cspl.com> wrote in message
news:uqE9mUgMGHA.2668@.tk2msftngp13.phx.gbl...
> Hi Uri Dimant,
> Thanks for your information.
> I learned new sql function COALESCE( ) and the usage.
> My posted query was ,
> Suppose in sql table the value is 1234567.45
> My out put wiill be 1,234,567.45
> Can you please answer the above one.
> Regards
> M. Subbaiah
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OIp4qvfMGHA.3556@.TK2MSFTNGP10.phx.gbl...
>sql

Numeric value only for a character field

Hi
I have a character field (char ot varchar) that I want to force only to
contain numeric characters.
Can that be done by way of defining a constraint on the field ?
or by any other way in the field/table definition ?
What id the syntax ?
Anyone have examples ?

Thanks

David GreenbergOn Thu, 02 Aug 2007 10:05:40 +0200, David Greenberg wrote:

Quote:

Originally Posted by

>Hi
>I have a character field (char ot varchar) that I want to force only to
>contain numeric characters.
>Can that be done by way of defining a constraint on the field ?
>or by any other way in the field/table definition ?
>What id the syntax ?
>Anyone have examples ?

Hi David,

The obvious solution is to declare the column with one of the numeric
data types instead of char or varchar.

But if you insist on using character columns for numeric data, you can
add a CHECK constraint:

CREATE TABLE Example
(SomeCol char(9) CHECK (SomeCol NOT LIKE '%[^0-9]%'));

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Numeric to real type

Hi Everyone,

I hope this is not a silly one. Well, I have two tables - table 1 destination, table 2 source. Data is being imported from table 1 to table 2. For ages this has been working fine but now we get an error which just says can not insert value 1 (.i.e. user defined error message from error handling). I have been trying to run this whole thing bit by bit and detect the actual error but for some reason it doesn't show any error but only on live.

I could detect just one potential issue that value 1 in table1 is numeric defined with size 13 precision (20,10) and in table 2 it is defined as real. But I am not sure if conversion from numeric to real can be any problem as the SP didn't show me when I tried to run manually.

Can anyone double on my assumption that this might be a potential problem which doesn't seem to occur on test server as it might be because the data are different in test and live.

Thanks a ton in advance,
Sree

Quote:

Originally Posted by sreemati

Hi Everyone,

I hope this is not a silly one. Well, I have two tables - table 1 destination, table 2 source. Data is being imported from table 1 to table 2. For ages this has been working fine but now we get an error which just says can not insert value 1 (.i.e. user defined error message from error handling). I have been trying to run this whole thing bit by bit and detect the actual error but for some reason it doesn't show any error but only on live.

I could detect just one potential issue that value 1 in table1 is numeric defined with size 13 precision (20,10) and in table 2 it is defined as real. But I am not sure if conversion from numeric to real can be any problem as the SP didn't show me when I tried to run manually.

Can anyone double on my assumption that this might be a potential problem which doesn't seem to occur on test server as it might be because the data are different in test and live.

Thanks a ton in advance,
Sree

Well that should not be a problem because 1 is just a single digit number.
We would not be able to find the real reason with the help of user defined message.
Is it possible to find the original error message and POST it for my reference?|||

Quote:

Originally Posted by amitpatel66

Well that should not be a problem because 1 is just a single digit number.
We would not be able to find the real reason with the help of user defined message.
Is it possible to find the original error message and POST it for my reference?

Hi

I did not mean the Number 1 by value1, I just meant it in reference to a field value1. Yes, I know that I am trying to re-create the error and get the error message, hopefully I get lucky soon and than I will post it.

Thanks,
Sree|||

Quote:

Originally Posted by sreemati

Hi

I did not mean the Number 1 by value1, I just meant it in reference to a field value1. Yes, I know that I am trying to re-create the error and get the error message, hopefully I get lucky soon and than I will post it.

Thanks,
Sree

Then I assume there should be some problem with the data.
Ok, find out the exact error message and POST here for my reference!!

Numeric Representation of Data

In MS Access, for numeric fields, the decimal places shown can be defined as "Auto" meaning that the database will determine the number of decimal places to show based on the content of the field (i.e. 1.0, 0.75, 1.125).

In SQL Server for the same field, it appears that decimal precision is hard coded resulting in a fixed representation (i.e. 1.000, 0.750, 1.125)

Is there a way to make the decimal representation in SQL Server more like Access where trailing zeros are truncated?

See SQL Server 2005 Books Online topic:

float and real (Transact-SQL)

http://msdn2.microsoft.com/en-us/library/ms173773.aspx

|||

Perfect.

thanks for your help.

numeric overflow

Hi,
I'm trying to load a table from a dts job that selects all the records from
sybase table and imports it into an equivalent MS SQL 2000 table.
I have done this successfully for 16 tables.
However for one table I get the following error on insert:
Error at source for row number 38014.
Errors encountered for this task: 1
[DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Erro
r in
column 16.
[DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
I have checked the column definitions for the table and as far as I can
determine both the Sybase and MS table match. Column 16 is of money data
type. Any ideas causing the error?
Thanks!!gracie wrote:
> Hi,
> I'm trying to load a table from a dts job that selects all the
> records from sybase table and imports it into an equivalent MS SQL
> 2000 table.
> I have done this successfully for 16 tables.
> However for one table I get the following error on insert:
> Error at source for row number 38014.
> Errors encountered for this task: 1
> [DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Er
ror
> in column 16.
> [DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
> I have checked the column definitions for the table and as far as I
> can determine both the Sybase and MS table match. Column 16 is of
> money data type. Any ideas causing the error?
> Thanks!!
Have you looked at the data in row 38014 to see what is in the table?
David Gugick
Imceda Software
www.imceda.comsql

numeric overflow

Hi,
I'm trying to load a table from a dts job that selects all the records from
sybase table and imports it into an equivalent MS SQL 2000 table.
I have done this successfully for 16 tables.
However for one table I get the following error on insert:
Error at source for row number 38014.
Errors encountered for this task: 1
[DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Error in
column 16.
[DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
I have checked the column definitions for the table and as far as I can
determine both the Sybase and MS table match. Column 16 is of money data
type. Any ideas causing the error?
Thanks!!gracie wrote:
> Hi,
> I'm trying to load a table from a dts job that selects all the
> records from sybase table and imports it into an equivalent MS SQL
> 2000 table.
> I have done this successfully for 16 tables.
> However for one table I get the following error on insert:
> Error at source for row number 38014.
> Errors encountered for this task: 1
> [DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Error
> in column 16.
> [DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
> I have checked the column definitions for the table and as far as I
> can determine both the Sybase and MS table match. Column 16 is of
> money data type. Any ideas causing the error?
> Thanks!!
Have you looked at the data in row 38014 to see what is in the table?
--
David Gugick
Imceda Software
www.imceda.com

numeric overflow

Hi,
I'm trying to load a table from a dts job that selects all the records from
sybase table and imports it into an equivalent MS SQL 2000 table.
I have done this successfully for 16 tables.
However for one table I get the following error on insert:
Error at source for row number 38014.
Errors encountered for this task: 1
[DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Error in
column 16.
[DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
I have checked the column definitions for the table and as far as I can
determine both the Sybase and MS table match. Column 16 is of money data
type. Any ideas causing the error?
Thanks!!
gracie wrote:
> Hi,
> I'm trying to load a table from a dts job that selects all the
> records from sybase table and imports it into an equivalent MS SQL
> 2000 table.
> I have done this successfully for 16 tables.
> However for one table I get the following error on insert:
> Error at source for row number 38014.
> Errors encountered for this task: 1
> [DataDirect][ODBC Sybase Wire Protocol Driver]Numeric Overflow. Error
> in column 16.
> [DataDirect][ODBC Sybase Wire Protocol Driver]Error in row.
> I have checked the column definitions for the table and as far as I
> can determine both the Sybase and MS table match. Column 16 is of
> money data type. Any ideas causing the error?
> Thanks!!
Have you looked at the data in row 38014 to see what is in the table?
David Gugick
Imceda Software
www.imceda.com