Determining the most appropriate conversion for a string

Hi,

Is there some kind of code snippet to determine (a) the precise data type (and precision if appropriate) of a given table.columnname in the database, and (b) the most suitable conversion for a varchar that is being made to hold various types of data.

Presumably based on the above one could have some sort of CASE WHEN to cast the data to different types accordingly.

Thanks for any help on this.

You need to use the system tables. Check out sysobjects:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-t_2983.asp

syscolumns:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-c_5mur.asp

and systypes:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sys-t_2983.asp

This should get you part of the way:

declare @.tablename varchar(256)
declare @.colname varchar(256)

set @.tablename = .....
set @.colname = .....

SELECT obj.name as 'Table', col.name as 'Column', typ.name as 'DataType', col.length, col.xprec as 'Precision', col.xscale as 'Scale'
FROM sysobjects obj
join syscolumns col
on col.id = obj.id
join systypes typ
on typ.xtype = col.xtype
where obj.type = 'U'
and obj.name = @.tablename
and col.name = @.colname

Also, check out my blog for some information on system views:

http://blogs.claritycon.com/blogs/the_englishman/archive/2006/02/09/197.aspx

HTH

|||Thanks, that's really helpful...do you have any insight on determining the most appropriate conversion for a string piece of data? I.e. "23/10/04", "1.01F", "0.68", "0.00021", "1", etc.
|||

There are three system functions which would be useful here:

ISNUMERIC

ISDATE

ISNULL

See:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_79f7.asp

This should get you started:

CASE

WHEN ISNUMERIC(column) = 1 THEN ...... Process Number

WHEN ISDATE(COLUMN) = 1 THEN ... Process Date

ELSE ... Process string\

END

Also, you can use the LEN() and DATALENGTH() functions to determine the size of the value passed. If it is a number, you can also use CHARINDEX to look for a decimal point in the string, and return its value or not. That should help you determine whether the number is an integer, decimal or a money field, and help you work out the precision and size of the data.

HTH

|||ISNUMERIC will check for conversion to any of the numeric data types including integer, float, numeric and money. So if you need stricter checks you need to implement yourself. Another option is to use sql_variant but this will complicate your logic due to the conversions. On the other hand you can retain the base type of the value you are specifying.|||Many thanks for your replies. The requirement has actually now been made simpler. In short, I need to have an additional case to logic like the following (I have asterisked the key line):-

if @.stkData like '%+%'
begin
exec dbo.spNeoStackEvaluate @.stkLeft output
exec dbo.spNeoStackEvaluate @.stkRight output

***set @.stkDataOut = cast(@.stkRight as int) + cast(@.stkLeft as int)
return @.stkDataOut
end

Which should handle cases where the strings originated as money datatypes. The problem is that a simple rewrite of the above, casting @.stkRight as money, is not acceptable in T-SQL, but falls over.

So while replies so far have been very helpful in setting up some logic to know what kind of data I am dealing with, I would also like to know if a simple rewrite of the above to handle money-like strings is in some way possible?

-Thanks.
|||I should point out that all the variables here -- @.stkDataOut, @.stkRight, @.stkLeft, are originally declared as varchar(100) type.
|||

I am not quite sure what exacty your problem is here. As I suggested earlier, you can test the varchar value to seeif it is a decimal (or money) or an int by using charindex, and looking for the presence of a '.' character. By getting the position of the decimal point using charindex, you can then determine the size of the decimal or money value to cast it as. This should then be able to handle any case.

HTH

|||The problem is that even assuming a simple case, for test purposes -- e.g. '0.68' as the varchar data, casting it to money throws an error. So for example assuming a value of '20', casting it as an int is fine, but cast(@.somedata as money) where @.somedata is '0.68' fails. Now this piece of data originated in a field of money datatype. Are you saying that my cast statement must be more specific?
|||

I thought the problem was not specifying the size of the data type, but that does not appear to be the issue. Test code works fine:

declare @.char varchar(20)

set @.char = '0.68'

select cast(@.char as money)

-- returns 0.68

So I am not sure what your issue if. Are you sure that it is the value '0.68' that is throwing the error? I suggest you put a select @.somedata value before the cast to get the value of the varchar before the error is thrown, to double check you are not getting any characters in there. Also, what error are you getting? Can you post the code snippet along with the syntax error? What version of SQL Server are you running? You could try casting it as a decimal instead, but make sure you specify the size and precision, otherwise it will default to an integer value.

|||

Though this will work fine (i.e. accessing system tables to get column data types, attributes, etc.), I'd probably recommend you make use of the SQL-92 compliant INFORMATION_SCHEMA views or system provided functions when possible to retrieve the data, given that you may get different results and unpredictable updates across SQL Server versions, SKU's, and hotfixes/service packs.

In this case, the INFORMATION_SCHEMA.COLUMNS view provides you with all the things you are looking for and probably more: name, data type, precision, scale, schema, default, position, nullability, etc., etc.

Additionally, these system functions could help out as well:

COLUMNPROPERTY()
COL_NAME()
COL_LENGTH()

As for the money conversion, the posting above by Shughes should work fine as he mentions...does for me also :-)...

|||Please indicate if Chad and I solved your problem.|||Will do. I am evaluating it at the moment. It's one of a number of problems that I'm working through. I will post in greater detail later on.
|||Just briefly, this is definitely still erroring. The problem seems to consist in going from varchar to money (in order to carry out a valid calculation) and then going back to varchar.

I will post on this in more detail once I get various other problems sorted out.