Hello there
I have table with field which is a text field that provide numbers and text
I would like to drop all the records that on the current field has text and
leave only the records with the number on the field
For this i've tried to use the convertion process but it gave me an error
when the data is text
Is there a way to know if the data is text or number so i can deal with it?
any help would be useful
roy@.atidsm.co.il
' 050-7709399"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in
news:#Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl:
> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an
> error when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
Try ISNUMERIC(expression)|||Although ISNUMERIC will provide the desired results in many cases, it will
also consider some obscure numeric values to be numeric. You can use LIKE
to test for integer digits. The example shows some differences between
ISNUMERIC and LIKE.
CREATE TABLE MyTable
(
MyNumber varchar(10)
)
GO
INSERT INTO MyTable VALUES('')
INSERT INTO MyTable VALUES(' ')
INSERT INTO MyTable VALUES('1')
INSERT INTO MyTable VALUES(' 2')
INSERT INTO MyTable VALUES('3 ')
INSERT INTO MyTable VALUES('1D')
INSERT INTO MyTable VALUES('0D1')
INSERT INTO MyTable VALUES('0E1')
SELECT *
FROM MyTable
WHERE ISNUMERIC(MyNumber) = 0
SELECT *
FROM MyTable
WHERE MyNumber LIKE '%[^0-9]%' OR
DATALENGTH(MyNumber) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...
> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and
> leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an error
> when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
> any help would be useful
> --
>
> roy@.atidsm.co.il
> ' 050-7709399
>|||ISNUMERIC also interprets '.', '-' and '+' as numeric.
Dan Guzman wrote:
> Although ISNUMERIC will provide the desired results in many cases, it
> will also consider some obscure numeric values to be numeric. You
> can use LIKE to test for integer digits. The example shows some
> differences between ISNUMERIC and LIKE.
> CREATE TABLE MyTable
> (
> MyNumber varchar(10)
> )
> GO
> INSERT INTO MyTable VALUES('')
> INSERT INTO MyTable VALUES(' ')
> INSERT INTO MyTable VALUES('1')
> INSERT INTO MyTable VALUES(' 2')
> INSERT INTO MyTable VALUES('3 ')
> INSERT INTO MyTable VALUES('1D')
> INSERT INTO MyTable VALUES('0D1')
> INSERT INTO MyTable VALUES('0E1')
> SELECT *
> FROM MyTable
> WHERE ISNUMERIC(MyNumber) = 0
> SELECT *
> FROM MyTable
> WHERE MyNumber LIKE '%[^0-9]%' OR
> DATALENGTH(MyNumber) = 0
>
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...
Showing posts with label numbers. Show all posts
Showing posts with label numbers. Show all posts
Tuesday, February 14, 2012
converting sql_variant column type to int
Hi,
I've got a table which has got a sql_variant column (sql server instance is 2000). the current data in that column are all numbers so can be converted to int.
I want to change the column type from sql_variant to int for that column. I have tried
I've got a table which has got a sql_variant column (sql server instance is 2000). the current data in that column are all numbers so can be converted to int.
I want to change the column type from sql_variant to int for that column. I have tried
alter table mytable alter column mycolumn int
and it complains
Disallowed implicit conversion from data type sql_variant to data type int, table 'mydatabase.dbo.mytable ', column 'mycolumn '. Use the CONVERT function to run this query.
I can do this task using EM but what that does is creating a new table temp table with int column type transferring data , dropping the original table and renaming the temp table to initial name.
therefore I need T-SQL command
Thanks
Really, I think I would suggest the same thing that you describe the EM doing -- drop the constraints on the original table, create the new table, copy in the data and then drop the old table. Can we get second and third opinions here?|||problem: this is template for a table in production server, which can not be dropped and also there are many constraints that points to this table. so using EM is not an option.
|||I would start by scripting out the table from the EM and massaging the generated script. Again, I'm not sure that you are going to be able to get out of creating a new version of the table and I would like additional opinions on this.|||
See if this helps.
create table dbo.t1 (
c1 sql_variant null
)
go
insert into dbo.t1 values(1)
go
alter table dbo.t1
add c2 int
go
update dbo.t1
set c2 = cast(c1 as int)
go
alter table dbo.t1
drop column c1
go
exec sp_rename 'dbo.t1.c2', 'c1', 'COLUMN'
go
select *
from dbo.t1
go
drop table dbo.t1
go
If there are indexes or constraints referencing column [c1], you have to drop them first and recreating them after renaming new column.
AMB
|||good solution, but column order is important for me. I think I can got to syscolumn table and change the column order there but this won't work on 2005|||Kolf,
> but column order is important for me
Sorry about it, that is the beauty about relational dbs, the position of the column is not important at all. If that is the case, there is not other option than creating a new table, move all data, drop constraints refrencing the table, drop old table, recreate constraints and indexes. That is what" Enterprise Manager" or "Management Studio" does.
AMB
Friday, February 10, 2012
Converting Numbers to a SSN Format
I inherited a db that allowed for multiple types of social security numbers
to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
(this latter being a pseudo based on initials of the client and birth date),
which could also be ABC021987.
I need to update the fields in the system so they all have the format of
###-##-#### (with the # being alpha-numeric)
I know the update syntax and how to manipulate to upper or lower case, but
cannot find an example like what I need.
Any suggestions?Why do you need to actually update the data? This means you need to update
it whenever you touch it, because the very next update or insert could
introduce new unformatted values. I would prefer a computed column or a
view.
Anyway, SQL Server does not support input mask. You can use regular string
parsing / concatenation or STUFF(). The latter is less code but tougher to
perfect/debug so I will show the former.
SELECT LEFT(col, 3) + '-' + SUBSTRING(col, 4, 2) + '-' + RIGHT(col, 4)
FROM (SELECT col = '555667777') x;
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:99B8C96B-AE61-4612-BAA4-C471FE3A3DB9@.microsoft.com...
>I inherited a db that allowed for multiple types of social security numbers
>to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
>(this latter being a pseudo based on initials of the client and birth
>date), which could also be ABC021987.
> I need to update the fields in the system so they all have the format of
> ###-##-#### (with the # being alpha-numeric)
> I know the update syntax and how to manipulate to upper or lower case, but
> cannot find an example like what I need.
> Any suggestions?|||Assuming the only extra character in the existing data is -, first
remove all the - characters and then put them where you want them.
CREATE TABLE #Demo (SSN varchar(15))
INSERT #Demo VALUES('555667777')
INSERT #Demo VALUES('555-66-7777')
INSERT #Demo VALUES('ABC-02-1987')
SELECT SUBSTRING(REPLACE(SSN,'-',''),1,3) + '-' +
SUBSTRING(REPLACE(SSN,'-',''),4,2) + '-' +
SUBSTRING(REPLACE(SSN,'-',''),6,4)
FROM #Demo
I assume you will know how to change this into an UPDATE if required.
Roy Harvey
Beacon Falls, CT
On Tue, 28 Aug 2007 14:39:04 -0400, "JOHN HARRIS"
<harris1113@.fake.com> wrote:
>I inherited a db that allowed for multiple types of social security numbers
>to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
>(this latter being a pseudo based on initials of the client and birth date),
>which could also be ABC021987.
>I need to update the fields in the system so they all have the format of
>###-##-#### (with the # being alpha-numeric)
>I know the update syntax and how to manipulate to upper or lower case, but
>cannot find an example like what I need.
>Any suggestions?|||SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
as SSN, Clients.First_Name + ' ' + Clients.Last_Name
FROM Clients
It recodes the SSN as I need it. How do I get this result to the UPDATE
function inside the main db?
Possibly:
Update Clients
Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OcOp1La6HHA.484@.TK2MSFTNGP06.phx.gbl...
> Why do you need to actually update the data? This means you need to
> update it whenever you touch it, because the very next update or insert
> could introduce new unformatted values. I would prefer a computed column
> or a view.
> Anyway, SQL Server does not support input mask. You can use regular
> string parsing / concatenation or STUFF(). The latter is less code but
> tougher to perfect/debug so I will show the former.
> SELECT LEFT(col, 3) + '-' + SUBSTRING(col, 4, 2) + '-' + RIGHT(col, 4)
> FROM (SELECT col = '555667777') x;
>
>
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:99B8C96B-AE61-4612-BAA4-C471FE3A3DB9@.microsoft.com...
>>I inherited a db that allowed for multiple types of social security
>>numbers to be entered, from examples like 555667777 to 555-66-7777 to
>>ABC-02-1987 (this latter being a pseudo based on initials of the client
>>and birth date), which could also be ABC021987.
>> I need to update the fields in the system so they all have the format of
>> ###-##-#### (with the # being alpha-numeric)
>> I know the update syntax and how to manipulate to upper or lower case,
>> but cannot find an example like what I need.
>> Any suggestions?
>|||> It recodes the SSN as I need it. How do I get this result to the UPDATE
> function inside the main db?
> Possibly:
> Update Clients
> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
> 4)
Take a look at the UPDATE topic in Books Online. You are saying SET but you
are not telling SQL Server which column you are talking about.
Update Clients
Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
RIGHT(ssnum, 4)
You probably want a where clause on there, just in case you already have
rows that are in the desired format. This statement will affect all rows,
and if any contain dashes, now they will contain more.
A|||Yeah the WHERE Clause almost bit me. What type of where clause should I use
to not harm those entered correctly?
Something like:
Update Clients
Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
RIGHT(ssnum, 4)
WHERE ssnum not like '___-__-____'
'
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uIrLUr5BIHA.912@.TK2MSFTNGP05.phx.gbl...
>> It recodes the SSN as I need it. How do I get this result to the UPDATE
>> function inside the main db?
>> Possibly:
>> Update Clients
>> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
>> 4)
>
> Take a look at the UPDATE topic in Books Online. You are saying SET but
> you are not telling SQL Server which column you are talking about.
>
> Update Clients
> Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
> RIGHT(ssnum, 4)
>
> You probably want a where clause on there, just in case you already have
> rows that are in the desired format. This statement will affect all rows,
> and if any contain dashes, now they will contain more.
> A
>|||Well, you can always run a SELECT (not an UPDATE) and check and make sure
that the LIKE and NOT LIKE versions of that query would affect the row(s)
you expect.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:82851662-342B-4866-8130-B2AC00ACB84C@.microsoft.com...
> Yeah the WHERE Clause almost bit me. What type of where clause should I
> use to not harm those entered correctly?
> Something like:
> Update Clients
> Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
> RIGHT(ssnum, 4)
> WHERE ssnum not like '___-__-____'
> '
to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
(this latter being a pseudo based on initials of the client and birth date),
which could also be ABC021987.
I need to update the fields in the system so they all have the format of
###-##-#### (with the # being alpha-numeric)
I know the update syntax and how to manipulate to upper or lower case, but
cannot find an example like what I need.
Any suggestions?Why do you need to actually update the data? This means you need to update
it whenever you touch it, because the very next update or insert could
introduce new unformatted values. I would prefer a computed column or a
view.
Anyway, SQL Server does not support input mask. You can use regular string
parsing / concatenation or STUFF(). The latter is less code but tougher to
perfect/debug so I will show the former.
SELECT LEFT(col, 3) + '-' + SUBSTRING(col, 4, 2) + '-' + RIGHT(col, 4)
FROM (SELECT col = '555667777') x;
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:99B8C96B-AE61-4612-BAA4-C471FE3A3DB9@.microsoft.com...
>I inherited a db that allowed for multiple types of social security numbers
>to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
>(this latter being a pseudo based on initials of the client and birth
>date), which could also be ABC021987.
> I need to update the fields in the system so they all have the format of
> ###-##-#### (with the # being alpha-numeric)
> I know the update syntax and how to manipulate to upper or lower case, but
> cannot find an example like what I need.
> Any suggestions?|||Assuming the only extra character in the existing data is -, first
remove all the - characters and then put them where you want them.
CREATE TABLE #Demo (SSN varchar(15))
INSERT #Demo VALUES('555667777')
INSERT #Demo VALUES('555-66-7777')
INSERT #Demo VALUES('ABC-02-1987')
SELECT SUBSTRING(REPLACE(SSN,'-',''),1,3) + '-' +
SUBSTRING(REPLACE(SSN,'-',''),4,2) + '-' +
SUBSTRING(REPLACE(SSN,'-',''),6,4)
FROM #Demo
I assume you will know how to change this into an UPDATE if required.
Roy Harvey
Beacon Falls, CT
On Tue, 28 Aug 2007 14:39:04 -0400, "JOHN HARRIS"
<harris1113@.fake.com> wrote:
>I inherited a db that allowed for multiple types of social security numbers
>to be entered, from examples like 555667777 to 555-66-7777 to ABC-02-1987
>(this latter being a pseudo based on initials of the client and birth date),
>which could also be ABC021987.
>I need to update the fields in the system so they all have the format of
>###-##-#### (with the # being alpha-numeric)
>I know the update syntax and how to manipulate to upper or lower case, but
>cannot find an example like what I need.
>Any suggestions?|||SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
as SSN, Clients.First_Name + ' ' + Clients.Last_Name
FROM Clients
It recodes the SSN as I need it. How do I get this result to the UPDATE
function inside the main db?
Possibly:
Update Clients
Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OcOp1La6HHA.484@.TK2MSFTNGP06.phx.gbl...
> Why do you need to actually update the data? This means you need to
> update it whenever you touch it, because the very next update or insert
> could introduce new unformatted values. I would prefer a computed column
> or a view.
> Anyway, SQL Server does not support input mask. You can use regular
> string parsing / concatenation or STUFF(). The latter is less code but
> tougher to perfect/debug so I will show the former.
> SELECT LEFT(col, 3) + '-' + SUBSTRING(col, 4, 2) + '-' + RIGHT(col, 4)
> FROM (SELECT col = '555667777') x;
>
>
> "JOHN HARRIS" <harris1113@.fake.com> wrote in message
> news:99B8C96B-AE61-4612-BAA4-C471FE3A3DB9@.microsoft.com...
>>I inherited a db that allowed for multiple types of social security
>>numbers to be entered, from examples like 555667777 to 555-66-7777 to
>>ABC-02-1987 (this latter being a pseudo based on initials of the client
>>and birth date), which could also be ABC021987.
>> I need to update the fields in the system so they all have the format of
>> ###-##-#### (with the # being alpha-numeric)
>> I know the update syntax and how to manipulate to upper or lower case,
>> but cannot find an example like what I need.
>> Any suggestions?
>|||> It recodes the SSN as I need it. How do I get this result to the UPDATE
> function inside the main db?
> Possibly:
> Update Clients
> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
> 4)
Take a look at the UPDATE topic in Books Online. You are saying SET but you
are not telling SQL Server which column you are talking about.
Update Clients
Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
RIGHT(ssnum, 4)
You probably want a where clause on there, just in case you already have
rows that are in the desired format. This statement will affect all rows,
and if any contain dashes, now they will contain more.
A|||Yeah the WHERE Clause almost bit me. What type of where clause should I use
to not harm those entered correctly?
Something like:
Update Clients
Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
RIGHT(ssnum, 4)
WHERE ssnum not like '___-__-____'
'
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uIrLUr5BIHA.912@.TK2MSFTNGP05.phx.gbl...
>> It recodes the SSN as I need it. How do I get this result to the UPDATE
>> function inside the main db?
>> Possibly:
>> Update Clients
>> Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum,
>> 4)
>
> Take a look at the UPDATE topic in Books Online. You are saying SET but
> you are not telling SQL Server which column you are talking about.
>
> Update Clients
> Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
> RIGHT(ssnum, 4)
>
> You probably want a where clause on there, just in case you already have
> rows that are in the desired format. This statement will affect all rows,
> and if any contain dashes, now they will contain more.
> A
>|||Well, you can always run a SELECT (not an UPDATE) and check and make sure
that the LIKE and NOT LIKE versions of that query would affect the row(s)
you expect.
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:82851662-342B-4866-8130-B2AC00ACB84C@.microsoft.com...
> Yeah the WHERE Clause almost bit me. What type of where clause should I
> use to not harm those entered correctly?
> Something like:
> Update Clients
> Set ssnum = LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' +
> RIGHT(ssnum, 4)
> WHERE ssnum not like '___-__-____'
> '
Converting Numbers
Hi Everyone,
We have a database that has numbers and we want to CONVERT all the numbers
to 0. How would we go about writing a script that can do such a thing.
Thanks in advance for everyones help.
Jay :)If you actually want to change the values stored in the database, use an
UPDATE statement
Check it out in The Books OnLIne or in any SQL book...
"James Mueller" wrote:
> Hi Everyone,
> We have a database that has numbers and we want to CONVERT all the numbers
> to 0. How would we go about writing a script that can do such a thing.
> Thanks in advance for everyones help.
> Jay :)|||More info needed.
Do you want to change all numeric data type columns from all tables to 0?
If so:
See INFORMATION_SCHEMA.COLUMNS
which returns information about each column
including tableName, columnName and column type.
A cursor, some dynamic SQL and you're done.
"James Mueller" <JamesMueller@.discussions.microsoft.com> wrote in message
news:AD68E5C6-8CEC-4BAD-AD66-850E3C8DC1DD@.microsoft.com...
> Hi Everyone,
> We have a database that has numbers and we want to CONVERT all the numbers
> to 0. How would we go about writing a script that can do such a thing.
> Thanks in advance for everyones help.
> Jay :)|||Yes we had various values ranging from 1 - 65,000. We needed to setup each
one back to 0. We used CBretana's advice and used the UPDATE function. Thi
s
will work for us because we only need to change it one time and that is when
the database and tables are initially created.
Thanks for everyones help.
Newbie (Jay)
"Raymond D'Anjou" wrote:
> More info needed.
> Do you want to change all numeric data type columns from all tables to 0?
> If so:
> See INFORMATION_SCHEMA.COLUMNS
> which returns information about each column
> including tableName, columnName and column type.
> A cursor, some dynamic SQL and you're done.
> "James Mueller" <JamesMueller@.discussions.microsoft.com> wrote in message
> news:AD68E5C6-8CEC-4BAD-AD66-850E3C8DC1DD@.microsoft.com...
>
>
We have a database that has numbers and we want to CONVERT all the numbers
to 0. How would we go about writing a script that can do such a thing.
Thanks in advance for everyones help.
Jay :)If you actually want to change the values stored in the database, use an
UPDATE statement
Check it out in The Books OnLIne or in any SQL book...
"James Mueller" wrote:
> Hi Everyone,
> We have a database that has numbers and we want to CONVERT all the numbers
> to 0. How would we go about writing a script that can do such a thing.
> Thanks in advance for everyones help.
> Jay :)|||More info needed.
Do you want to change all numeric data type columns from all tables to 0?
If so:
See INFORMATION_SCHEMA.COLUMNS
which returns information about each column
including tableName, columnName and column type.
A cursor, some dynamic SQL and you're done.
"James Mueller" <JamesMueller@.discussions.microsoft.com> wrote in message
news:AD68E5C6-8CEC-4BAD-AD66-850E3C8DC1DD@.microsoft.com...
> Hi Everyone,
> We have a database that has numbers and we want to CONVERT all the numbers
> to 0. How would we go about writing a script that can do such a thing.
> Thanks in advance for everyones help.
> Jay :)|||Yes we had various values ranging from 1 - 65,000. We needed to setup each
one back to 0. We used CBretana's advice and used the UPDATE function. Thi
s
will work for us because we only need to change it one time and that is when
the database and tables are initially created.
Thanks for everyones help.
Newbie (Jay)
"Raymond D'Anjou" wrote:
> More info needed.
> Do you want to change all numeric data type columns from all tables to 0?
> If so:
> See INFORMATION_SCHEMA.COLUMNS
> which returns information about each column
> including tableName, columnName and column type.
> A cursor, some dynamic SQL and you're done.
> "James Mueller" <JamesMueller@.discussions.microsoft.com> wrote in message
> news:AD68E5C6-8CEC-4BAD-AD66-850E3C8DC1DD@.microsoft.com...
>
>
Converting Numbers
Hello there
I have numbers that i would like to present it as standart currency: 0.00
and still has it as number
So far i could do this only by convert it to text. and it's not good because
i'm export it afterword to Excel.
I've tried to convert it to Decimal(10,2) and it didn't do the job
Is there another way to do this?
..
roy@.atidsm.co.il
: 03-5611606
' 050-7709399Why don't you deal with formatting in Excel post importing? If you need to
automate the process, you can use a vba script that formats the result.
T-SQL is not really intended for formatting, rather for data manipulation.
If you must do this with T-SQL, the STR function allows you to specify
length and decimal places, but it results in a character string.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:eAJXE7OnFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hello there
> I have numbers that i would like to present it as standart currency: 0.00
> and still has it as number
> So far i could do this only by convert it to text. and it's not good
> because
> i'm export it afterword to Excel.
> I've tried to convert it to Decimal(10,2) and it didn't do the job
> Is there another way to do this?
> --
>
> ..
> roy@.atidsm.co.il
> : 03-5611606
> ' 050-7709399
>|||When importing to Excel, you really don't need to format the data - or rathe
r
*shouldn't* format it.
If you let Excel format the values, the data can't end up being corrupted in
any way, and can be used further - using all the capabilities of Excel
(graphs, pivot tables, filtered views,...). But only if data gets to Excel i
n
its native form.
Plus: Excel's formatting capabilities by far exceed those provided by SQL
Server.
ML|||I am not sure what the problem is. So I am just throwing this in the air:
CONVERT(MONEY, Col_name)
--
Cathy B
"ML" wrote:
> When importing to Excel, you really don't need to format the data - or rat
her
> *shouldn't* format it.
> If you let Excel format the values, the data can't end up being corrupted
in
> any way, and can be used further - using all the capabilities of Excel
> (graphs, pivot tables, filtered views,...). But only if data gets to Excel
in
> its native form.
> Plus: Excel's formatting capabilities by far exceed those provided by SQL
> Server.
>
> ML
I have numbers that i would like to present it as standart currency: 0.00
and still has it as number
So far i could do this only by convert it to text. and it's not good because
i'm export it afterword to Excel.
I've tried to convert it to Decimal(10,2) and it didn't do the job
Is there another way to do this?
..
roy@.atidsm.co.il
: 03-5611606
' 050-7709399Why don't you deal with formatting in Excel post importing? If you need to
automate the process, you can use a vba script that formats the result.
T-SQL is not really intended for formatting, rather for data manipulation.
If you must do this with T-SQL, the STR function allows you to specify
length and decimal places, but it results in a character string.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:eAJXE7OnFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Hello there
> I have numbers that i would like to present it as standart currency: 0.00
> and still has it as number
> So far i could do this only by convert it to text. and it's not good
> because
> i'm export it afterword to Excel.
> I've tried to convert it to Decimal(10,2) and it didn't do the job
> Is there another way to do this?
> --
>
> ..
> roy@.atidsm.co.il
> : 03-5611606
> ' 050-7709399
>|||When importing to Excel, you really don't need to format the data - or rathe
r
*shouldn't* format it.
If you let Excel format the values, the data can't end up being corrupted in
any way, and can be used further - using all the capabilities of Excel
(graphs, pivot tables, filtered views,...). But only if data gets to Excel i
n
its native form.
Plus: Excel's formatting capabilities by far exceed those provided by SQL
Server.
ML|||I am not sure what the problem is. So I am just throwing this in the air:
CONVERT(MONEY, Col_name)
--
Cathy B
"ML" wrote:
> When importing to Excel, you really don't need to format the data - or rat
her
> *shouldn't* format it.
> If you let Excel format the values, the data can't end up being corrupted
in
> any way, and can be used further - using all the capabilities of Excel
> (graphs, pivot tables, filtered views,...). But only if data gets to Excel
in
> its native form.
> Plus: Excel's formatting capabilities by far exceed those provided by SQL
> Server.
>
> ML
Converting negative numbers to positive
Hi All
I have a table with column Col2 with negative and positive numbers. I would
like to query the col2 and make sure all the negative numbers are converted
to positive in the resultset.
eg
Tb1:
Col1 Col2
-- --
a -1
b -2
c 3
select col2 from tb1 where col1 ='a'
Resultset:
Col2
--
1
How can i do this? Thank you in advance.
5 years experience with SQL Server 2000 and SAP BW/SEM> I would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
Try:
SELECT ABS(col2)
FROM tb1
WHERE col1 = 'a'
Hope this helps.
Dan Guzman
SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
> Hi All
> I have a table with column Col2 with negative and positive numbers. I
> would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
> eg
> Tb1:
> Col1 Col2
> -- --
> a -1
> b -2
> c 3
> select col2 from tb1 where col1 ='a'
> Resultset:
> Col2
> --
> 1
> How can i do this? Thank you in advance.
>
>
>
>
>
>
>
>
>
> 5 years experience with SQL Server 2000 and SAP BW/SEM|||On Wed, 24 May 2006 16:41:02 -0700, MittyKom wrote:
>Hi All
>I have a table with column Col2 with negative and positive numbers. I woul
d
>like to query the col2 and make sure all the negative numbers are converte
d
>to positive in the resultset.
Hi MittyKom,
SELECT ABS(col2)
FROM tbl
Hugo Kornelis, SQL Server MVP|||MittyKom wrote:
> 5 years experience with SQL Server 2000 and SAP BW/SEM
5 years of experience and you don't know how to convert a negative
number into a positive one?|||Perhaps the OP was mostly on the administration side and/or specialized in
some other SQL Server non-development role. One could guess that there
would be a specialized function for this task but it's sometimes difficult
to find answers in the Books Online when you don't know what to look for.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lim" <blackcap80@.hotmail.com> wrote in message
news:1148517832.138537.174900@.i40g2000cwc.googlegroups.com...
> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||To Chris Lim
I am new to sql programming my friend. If you cant help keep your mouth
shhhhhhh. OK.... I have been working on sql server admin only. Enjoy your
day.
"Chris Lim" wrote:
> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||Thank you Hugo and Dan. It worked.
"Dan Guzman" wrote:
> Try:
> SELECT ABS(col2)
> FROM tb1
> WHERE col1 = 'a'
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
> news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
>
>
I have a table with column Col2 with negative and positive numbers. I would
like to query the col2 and make sure all the negative numbers are converted
to positive in the resultset.
eg
Tb1:
Col1 Col2
-- --
a -1
b -2
c 3
select col2 from tb1 where col1 ='a'
Resultset:
Col2
--
1
How can i do this? Thank you in advance.
5 years experience with SQL Server 2000 and SAP BW/SEM> I would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
Try:
SELECT ABS(col2)
FROM tb1
WHERE col1 = 'a'
Hope this helps.
Dan Guzman
SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
> Hi All
> I have a table with column Col2 with negative and positive numbers. I
> would
> like to query the col2 and make sure all the negative numbers are
> converted
> to positive in the resultset.
> eg
> Tb1:
> Col1 Col2
> -- --
> a -1
> b -2
> c 3
> select col2 from tb1 where col1 ='a'
> Resultset:
> Col2
> --
> 1
> How can i do this? Thank you in advance.
>
>
>
>
>
>
>
>
>
> 5 years experience with SQL Server 2000 and SAP BW/SEM|||On Wed, 24 May 2006 16:41:02 -0700, MittyKom wrote:
>Hi All
>I have a table with column Col2 with negative and positive numbers. I woul
d
>like to query the col2 and make sure all the negative numbers are converte
d
>to positive in the resultset.
Hi MittyKom,
SELECT ABS(col2)
FROM tbl
Hugo Kornelis, SQL Server MVP|||MittyKom wrote:
> 5 years experience with SQL Server 2000 and SAP BW/SEM
5 years of experience and you don't know how to convert a negative
number into a positive one?|||Perhaps the OP was mostly on the administration side and/or specialized in
some other SQL Server non-development role. One could guess that there
would be a specialized function for this task but it's sometimes difficult
to find answers in the Books Online when you don't know what to look for.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lim" <blackcap80@.hotmail.com> wrote in message
news:1148517832.138537.174900@.i40g2000cwc.googlegroups.com...
> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||To Chris Lim
I am new to sql programming my friend. If you cant help keep your mouth
shhhhhhh. OK.... I have been working on sql server admin only. Enjoy your
day.
"Chris Lim" wrote:
> MittyKom wrote:
> 5 years of experience and you don't know how to convert a negative
> number into a positive one?
>|||Thank you Hugo and Dan. It worked.
"Dan Guzman" wrote:
> Try:
> SELECT ABS(col2)
> FROM tb1
> WHERE col1 = 'a'
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
> news:ACE007EE-E918-4CB6-A59C-8318CAC4B059@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)