Change field with numeric and alphanumeric to numeric

I have a text field with records that mostly contain numbers, but in some of the records the field is alphanumeric, such as P234032. I want to convert the field to numeric, and just allow any alphanumeric fields to be ignored, and become null.

I'm used to doing this in Microsoft Access - it just ignores those strings and they become null - how can I do this in SQL Server 2000?

Thanks,

RandyDoes this help

set nocount on
go
create table #test (col1 char(10), col2 char(10))
go
insert #test values ('BU89090', 'record 1' )
insert #test values ('GHJ9213', 'record 2' )
insert #test values ('79898', 'record 3' )
insert #test values ('89067', 'record 4' )
insert #test values ('09889067', 'record 5' )
go

select *
from #test
go
select convert(int, case when isnumeric(col1) = 1 then col1 else NULL end) as intCol1, col2
from #test
go
drop table #test

Output
col1 col2
---- ----
BU89090 record 1
GHJ9213 record 2
79898 record 3
89067 record 4
09889067 record 5

intCol1 col2
---- ----
NULL record 1
NULL record 2
79898 record 3
89067 record 4
9889067 record 5|||achorozy,

Thanks for the reply! I haven't tried this yet, but will have a chance first thing tomorrow morning, and will let you know. The SQL Server 2000 course that I just completed was pretty thorough, but seems like it didn't cover the nitty gritty things that I need to know right now.

This is sure a different animal than MS Access!

Thanks again,

Randy

Certain numeric fields not read from the Excel file when using a Excel file source.

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

Try doing a data conversion of this column in the transformation phase using the Derived Column Transformation or the Data Conversion Transformation. Hope that works for you.|||

It has to do with a registry setting. I got a response from some other forum and I tried it and it worked.

|||I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.|||

Al C. wrote:

I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.

Can you set up the Excel connector to import all fields as text fields, and then later convert them to numerics using a derived column transformation?|||

Phil,

TFYR. I verified in the Excel Source (Advanced Editor) that it is using DT_WSTR in both the External columns and Output columns. When I click the Preview button it shows NULL in the first two rows. If I add a DataViewer it also shows nulls. Evidently the first two rows are considered text even though the values are numeric. I can see the difference when I use the formula auditing tool and the first two rows align to the left (indicating text) and the others align to the right (numeric). So what I have gathered is that Excel samples the first nn rows to determine the datatype and since most values are numeric it deems the datatype of the column to be numeric and so it replaces the first two values with nulls. If I modify the Excel connection string and add 'IMEX=1' (import mode=1) to the extended properties, then it works OK.

Certain numeric fields not read from the Excel file when using a Excel file source.

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

Try doing a data conversion of this column in the transformation phase using the Derived Column Transformation or the Data Conversion Transformation. Hope that works for you.|||

It has to do with a registry setting. I got a response from some other forum and I tried it and it worked.

|||I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.|||

Al C. wrote:

I am having a similar issue. I am importing an Excel sheet from a customer of ours into a SQL Server 2005 table. The first column is text and imports OK. The 2nd column has numbers, but the first two rows import as nulls. If I open the sheet and retype the numbers exactly as they appear, then the sheet imports OK. Each time they send us this sheet I have to retype the numbers in the first two rows. If I import the sheet into Access (using the Access Import wizard), Access does not have a problem and the first two numbers show up OK in Access (without my having to retype them first). How were you able to resolve this issue? Thanks.

Can you set up the Excel connector to import all fields as text fields, and then later convert them to numerics using a derived column transformation?|||

Phil,

TFYR. I verified in the Excel Source (Advanced Editor) that it is using DT_WSTR in both the External columns and Output columns. When I click the Preview button it shows NULL in the first two rows. If I add a DataViewer it also shows nulls. Evidently the first two rows are considered text even though the values are numeric. I can see the difference when I use the formula auditing tool and the first two rows align to the left (indicating text) and the others align to the right (numeric). So what I have gathered is that Excel samples the first nn rows to determine the datatype and since most values are numeric it deems the datatype of the column to be numeric and so it replaces the first two values with nulls. If I modify the Excel connection string and add 'IMEX=1' (import mode=1) to the extended properties, then it works OK.

cast('0.5' as numeric) + cast('0.5' as numeric) = 2 ???

Hi folks,
I want to talk about data types. I was of the impression that numeric
was a fixed data type. Why does sql 2005 exhibit the above behaviour
when casting to this datatype from a string?
Cheers,
Alex
The NUMERIC data type has precision and scale. Since you did not specify
scale in your CAST, the default scale of 0 is used. In that case the number
gets rounded to the decimal point and each CAST results in 1 for total of 2.
To CAST correctly you can specify scale 1:
SELECT CAST('0.5' AS NUMERIC(5, 1)) + CAST('0.5' AS NUMERIC(5, 1))
This will correctly return 1.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

cast('0.5' as numeric) + cast('0.5' as numeric) = 2 ???

Hi folks,
I want to talk about data types. I was of the impression that numeric
was a fixed data type. Why does sql 2005 exhibit the above behaviour
when casting to this datatype from a string?
Cheers,
AlexThe NUMERIC data type has precision and scale. Since you did not specify
scale in your CAST, the default scale of 0 is used. In that case the number
gets rounded to the decimal point and each CAST results in 1 for total of 2.
To CAST correctly you can specify scale 1:
SELECT CAST('0.5' AS NUMERIC(5, 1)) + CAST('0.5' AS NUMERIC(5, 1))
This will correctly return 1.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

CAST Money ?

I have tried to use the following statement (SQL Server 2005) where XXX is a
numeric field but there is no $ sign appears.
CAST (XXX,Money)
In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is there
any better way to handle it AND why the first statement doesn't work ?
Thanks
PeterPeter
No, but do you think there is only '$' on the earth?
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3Sw0%23YsHHA.1060@.TK2MSFTNGP06.phx.gbl...
>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
> CAST (XXX,Money)
> In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is
> there any better way to handle it AND why the first statement doesn't work
> ?
> Thanks
> Peter
>|||Peter,
Hi
the answer is NO. The best thing is to format it at the client side
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3Sw0%23YsHHA.1060@.TK2MSFTNGP06.phx.gbl...
>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
> CAST (XXX,Money)
> In this way, I try to use '$' + CAST (XXX, varchar) and it is OK. Is
> there any better way to handle it AND why the first statement doesn't work
> ?
> Thanks
> Peter
>|||>I have tried to use the following statement (SQL Server 2005) where XXX is
>a numeric field but there is no $ sign appears.
And why should it? Wouldn't the English be a little angry if they had to
then run REPLACE to put a pound symbol instead of a dollar sign?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Cast Error

Sorry, I'm not sure where to post this question.
This query has a problem:
SELECT CAST(SpecValue AS Numeric) AS Expr1
FROM dbo.tblSpecification
WHERE (IsNumeric(SpecValue) = 1) AND (SpecValue Like N'%,%')
(btw SpecValue nvarchar(50))
How do I explicitly Cast a string like "10,000" into numeric?
Thanks
--
Patget rid of the comma
declare @.v varchar(49)
select @.v ='10,000'
select convert(numeric(15,4),replace(@.v,',','')
),
convert(numeric(15,4),convert(money,@.v))
Denis the SQL Menace
http://sqlservercode.blogspot.com/