Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Determining Datatype

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. 12345AA

I 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'

Go to Top of Page

madscientist
Starting Member

30 Posts

Posted - 2008-10-09 : 18:53:56
Hello hanbingl

Thank 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 varchar

so when it is a float I want to convert it to an INT

when it is a varchar I want to leave it as a varchar

Thank you once again
Go to Top of Page

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 keyword

UPDATE RISK
SET 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 INT
ELSE BUREAU_NUMBER
END

I want it to work like that but don't know how to code it in the correct syntax
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

madscientist
Starting Member

30 Posts

Posted - 2008-10-10 : 13:10:48
Thank you visakh16

I will try that. Is there a substring function that works on datatype float?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 13:13:52
quote:
Originally posted by madscientist

Thank you visakh16

I 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
Go to Top of Page

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+008

Is there a way to handle this?
Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-10 : 19:36:34
I know ISNUMERIC has some problems but try this:
UPDATE @RISK
SET BUREAU_NUMBER =
cast(cast(BUREAU_NUMBER as float) as int)
where isnumeric(BUREAU_NUMBER)>0

Go to Top of Page

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 @RISK
SET 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
Go to Top of Page

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'
Go to Top of Page

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"
Go to Top of Page

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 reliable

SELECT Isnumeric('12d3')

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-14 : 06:02:33
Not sure which is OP wanted

--Peso
select
CASE
WHEN Col1 LIKE '%[^0-9]%' THEN CAST(Col1 AS VARCHAR(20))
ELSE CAST(CONVERT(INT, Col1) AS VARCHAR(20))
END
from (select '908234' as col1 union all select '12d3' union all select '456.45') as t

--Madhivanan
select
CASE
WHEN Col1 LIKE '%[^0-9.]%' THEN CAST(Col1 AS VARCHAR(20))
ELSE CAST(CAST(CONVERT(FLOAT, Col1) AS INT) AS VARCHAR(20))
END
from (select '908234' as col1 union all select '12d3' union all select '456.45') as t




Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -