Determining the most appropriate conversion for a string

Determining the most appropriate conversion for a string

• Filip2412

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

• Seiya

  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.
  
  

• kslim

  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.
  
  

• holodok

  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.
  
  

• PavanM

  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
  
  

• kerrycas

  Please indicate if Chad and I solved your problem.
  

• newCinC

  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

  
  

• Stevo Guy

  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 :-)...

  
  
  

• Timpany

  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.
  
  

• KevinBurton

  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

  
  

• BaskarBS

  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.
  
  

• etncc

  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.

  
  

• Jsamuel

  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

  
  
• Determining the most appropriate conversion for a string