SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Determining Datatype
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

madscientist
Starting Member

30 Posts

Posted - 10/09/2008 :  18:02:16  Show Profile  Reply with Quote
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 - 10/09/2008 :  18:13:02  Show Profile  Reply with Quote
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 - 10/09/2008 :  18:53:56  Show Profile  Reply with Quote
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 - 10/09/2008 :  19:16:09  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/10/2008 :  00:42:47  Show Profile  Reply with Quote
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 - 10/10/2008 :  12:09:21  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/10/2008 :  12:44:56  Show Profile  Reply with Quote
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 - 10/10/2008 :  13:02:19  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/10/2008 :  13:05:11  Show Profile  Reply with Quote
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 - 10/10/2008 :  13:10:48  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/10/2008 :  13:13:52  Show Profile  Reply with Quote
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 - 10/10/2008 :  13:30:13  Show Profile  Reply with Quote
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 - 10/10/2008 :  19:36:34  Show Profile  Reply with Quote
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


Edited by - hanbingl on 10/10/2008 19:43:13
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/11/2008 :  01:10:58  Show Profile  Reply with Quote
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 - 10/11/2008 :  04:51:12  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 10/11/2008 :  07:01:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22761 Posts

Posted - 10/14/2008 :  05:28:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Sweden
30265 Posts

Posted - 10/14/2008 :  05:40:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CASE
	WHEN Col1 LIKE '%[^0-9]%' THEN CAST(Col1 AS VARCHAR(20))
	ELSE CAST(CONVERT(INT, Col1) AS VARCHAR(20))
END



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/14/2008 :  06:02:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000