Author |
Topic |
madscientist
Starting Member
30 Posts |
Posted - 2008-10-09 : 18:02:16
|
Hello again,Is there any function in SQL Server that can find the datatype of a column? Problem is that I have an XML Mapper that imports the data into tables and the datatype of one field in one table named RISK varies from either a float to a varchar.The table is called RISK and the field is called BUREAU_NUMBER but sometimes its a float when it is all numbers i.e. 1234567.0000 and sometimes its a varchar when it has a numbers and letters i.e. 12345AAI want to find out whether it is a float and convert it first to an int and then a varchar.Thank you again for the help on this forum. |
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-09 : 18:13:02
|
It must be a varchar since some data can be 12345AA.to find out you can either use EM or select data_type from information_schema.columns where table_name = 'RISK' and column_name = 'BUREAU_NUMBER' |
|
|
madscientist
Starting Member
30 Posts |
Posted - 2008-10-09 : 18:53:56
|
Hello hanbinglThank you for your answer. Is there a way to convert the datatype to an INT.Thing is it comes in either as a float or a varchar after I run the XML Mapper i.e. sometimes the column can be of type float sometimes it can be of type varcharso when it is a float I want to convert it to an INTwhen it is a varchar I want to leave it as a varcharThank you once again |
|
|
madscientist
Starting Member
30 Posts |
Posted - 2008-10-09 : 19:16:09
|
I'm trying to code this but get syntax errors by the ALTER keywordUPDATE RISKSET BUREAU_NUMBER = CASE WHEN (SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'RISK' AND COLUMN_NAME = 'BUREAU_NUMBER') = 'float'THEN ALTER TABLE RISK ALTER COLUMN BUREAU_NUMBER INTELSE BUREAU_NUMBERENDI want it to work like that but don't know how to code it in the correct syntax |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 00:42:47
|
You cant alter column datatype inside case. what are you trying to do by converting the datatype to int if it contains numbers? is this for achieving correct sorting of results? |
|
|
madscientist
Starting Member
30 Posts |
Posted - 2008-10-10 : 12:09:21
|
Hello visakh16,I just can't have the datatype remain as a float. It can be either an int or a varchar but I have no control over what datatype the column has.The table is created from an xml extract. When the values in that column (BUREAU_NUMBER) contain numbers and letters, it comes in as a varchar. When the values in that column contain only numbers, it comes in as a float. That is when I want it to be an int.Thank you visakh16 for your response. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 12:44:56
|
quote: Originally posted by madscientist Hello visakh16,I just can't have the datatype remain as a float. It can be either an int or a varchar but I have no control over what datatype the column has.The table is created from an xml extract. When the values in that column (BUREAU_NUMBER) contain numbers and letters, it comes in as a varchar. When the values in that column contain only numbers, it comes in as a float. That is when I want it to be an int.Thank you visakh16 for your response.
but unless you've consistent numeric data coming from source, you cant make it int. better to leave it as varchar itself. |
|
|
madscientist
Starting Member
30 Posts |
Posted - 2008-10-10 : 13:02:19
|
Hi visakh16,Yes I understand to leave it as a varchar when it comes in as a varchar from the data source. But when it comes in as a float from the data source I cannot leave it as a float, I need to convert it to an int. I can't have the digits behind the decimal point.The issue is that the data source for that field is inconsistant and it creates an error when I run my coding. But I have no control over how it comes as I mentioned sometimes that field has characters mixed with numbers and sometimes its just numbers.Thank you again. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 13:05:11
|
quote: Originally posted by madscientist Hi visakh16,Yes I understand to leave it as a varchar when it comes in as a varchar from the data source. But when it comes in as a float from the data source I cannot leave it as a float, I need to convert it to an int. I can't have the digits behind the decimal point.The issue is that the data source for that field is inconsistant and it creates an error when I run my coding. But I have no control over how it comes as I mentioned sometimes that field has characters mixed with numbers and sometimes its just numbers.Thank you again.
thats why i told to leave it as varchar. and just when yourvalue comes as float just remove decimal part by string manipulation method i.e using substring functions just save only part before decimal point. hope that will give you what you want. |
|
|
madscientist
Starting Member
30 Posts |
Posted - 2008-10-10 : 13:10:48
|
Thank you visakh16I will try that. Is there a substring function that works on datatype float? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 13:13:52
|
quote: Originally posted by madscientist Thank you visakh16I will try that. Is there a substring function that works on datatype float?
you can use left() or substring() and cast it to varchar before string manipulating using cast or convert |
|
|
madscientist
Starting Member
30 Posts |
Posted - 2008-10-10 : 13:30:13
|
Hi visakh16,Sorry to bug you again.When I convert the float to a string for some of the larger values I get the exponential form which I cant have i.e. 9.10228e+008Is there a way to handle this? |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-10 : 19:36:34
|
I know ISNUMERIC has some problems but try this:UPDATE @RISKSET BUREAU_NUMBER =cast(cast(BUREAU_NUMBER as float) as int) where isnumeric(BUREAU_NUMBER)>0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-11 : 01:10:58
|
quote: Originally posted by hanbingl I know ISNUMERIC has some problems but try this:UPDATE @RISKSET BUREAU_NUMBER =cast(cast(BUREAU_NUMBER as float) as int) where isnumeric(BUREAU_NUMBER)>0
but this will ignore values with alpha characters altogether. i thought OP was asking for a way to store both the types of values to column with ignoring decimal portion in cases where numeric value contains decimal |
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-11 : 04:51:12
|
Correct, I thought OP wants to leave alpha characters as is and only convert float into INT. Mad, is the following what you wanted??'123123.123' converts to '123123''123123.123ABC' stays as '123123.123ABC''123ABC' stays as '123ABC''9.10228e+008' converts to '91022800''1.1.1' stays '1.1.1' |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-11 : 07:01:52
|
CASE WHEN ISNUMERIC(Col1) THEN CAST(CONVERT(INT, Col1) AS VARCHAR(20))ELSE CAST(Col1 AS VARCHAR(20))END E 12°55'05.63"N 56°04'39.26" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-10-14 : 05:28:10
|
quote: Originally posted by Peso CASE WHEN ISNUMERIC(Col1) THEN CAST(CONVERT(INT, Col1) AS VARCHAR(20))ELSE CAST(Col1 AS VARCHAR(20))END E 12°55'05.63"N 56°04'39.26"
As you know Isnumeric() is not reliableSELECT Isnumeric('12d3')MadhivananFailing to plan is Planning to fail |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-14 : 05:40:23
|
[code]CASE WHEN Col1 LIKE '%[^0-9]%' THEN CAST(Col1 AS VARCHAR(20)) ELSE CAST(CONVERT(INT, Col1) AS VARCHAR(20))END[/code] E 12°55'05.63"N 56°04'39.26" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-10-14 : 06:02:33
|
Not sure which is OP wanted--Pesoselect CASE WHEN Col1 LIKE '%[^0-9]%' THEN CAST(Col1 AS VARCHAR(20)) ELSE CAST(CONVERT(INT, Col1) AS VARCHAR(20))ENDfrom (select '908234' as col1 union all select '12d3' union all select '456.45') as t--Madhivananselect CASE WHEN Col1 LIKE '%[^0-9.]%' THEN CAST(Col1 AS VARCHAR(20)) ELSE CAST(CAST(CONVERT(FLOAT, Col1) AS INT) AS VARCHAR(20))ENDfrom (select '908234' as col1 union all select '12d3' union all select '456.45') as t MadhivananFailing to plan is Planning to fail |
|
|
|