You first need to convert your character strings into values that can be recognized as a number before you convert the type from char() to decimal(). The string "12,34,56" is not a number, and so becomes 0.0 when you try to convert it to one.
What you need to do is replace the commas with the appropriate numerical punctuation, and the string functions you need are Left$(), Mid$(), and Right$() but if the commas can appear in varying positions (e.g. "1,23,45" or "123,45,67") you'll also need InStr() and Len(). THese are well documented in the online Help, but how and when you use them may not be obvious. (This is a problem with all documentation. It's like describing a sword in great detail, but failing to mention that you direct the pointy end toward the enemy.) Anyway, if your commas are always in the same place, run this SQL command (MyTable and MyColumn are your table and column names): update MyTable Set MyColumn = Left$(MyColumn,2) + "." + Mid$(MyColumn,4,2) + Mid$(MyColumn,7,2) If your commas are in varying positions, then convert the first one to a decimal point in one pass, and then remove the other commas in subsequent passes. Like so: update MyTable Set MyColumn = Left$(MyColumn,InStr(1,MyColumn,",")-1) + "." + Right$(MyColumn, Len(MyColumn) - InStr(1,MyColumn,",") Then get rid of remaining commas like so: update MyTable Set MyColumn = Left$(MyColumn,InStr(1,MyColumn,",")-1) + Right$(MyColumn, Len(MyColumn) - InStr(1,MyColumn,",") Personally, I think the online help is pretty good, but you do have to develop an awareness of how the tools are used. Generally, that information is found in the Users Guide, not the Reference Guide or online Help. -- - Bill Thoen ------------------------------------------------------------ GISnet, 1401 Walnut St., Suite C, Boulder, CO 80302 tel: 303-786-9961, fax: 303-443-4856 mailto:[EMAIL PROTECTED], http://www.gisnet.com/ ------------------------------------------------------------ Philip Chinnici wrote: > > Hello, > > I am looking for a documented overview of MapInfo string functions. The > help menu is bogus as well as the Ref Guide. > > I want to do several things: > > 1. Take a character column with commas and numbers and covert into a > decimal (8,3) column without losing the values > Example: Character column 12,34,56 > > Output expected: 12.3456 > > What currently happens is I get a column with 0.0000 > > 2. I have a character column with spaces after the 2nd and 4th characters, > I want to convert this to a integer column dropping the spaces > > Example 12 34 56 > Output expected: 123456 > > If someone know of a good detailed document which both explains the string > functions and samples of these functions, I would be most grateful.. > > Regards, > > Philip Chinnici > GIS Officer > [EMAIL PROTECTED] --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
