[thelist] SQL Server: How Can I Convert VARCHAR to INT?

[James Aylard]

Rory, et al,

    An update on the varchar-to-int conversion query: I (think) that, after
much tinkering, I have improved upon the original query, making it more
robust. A few drawbacks to the original query: I discovered later that
fields which contained multiple words were not handled properly (the query
would remove one word, but not the rest), and in many fields, an individual
space would still precede the numeric value (although this did not interfere
with the conversion of the field from varchar to int). Here is my "improved"
version; perhaps you, or one of our (other) SQL gurus can further refine it:

WHILE (SELECT COUNT(DISTINCT field) FROM table
  WHERE PATINDEX('%[ ,/A-Z]%',field) <> 0) > 0
  BEGIN
    UPDATE table
    SET field =
      REPLACE(field,SUBSTRING(field,PATINDEX('%[ ,/A-Z]%',field),1),'')
      WHERE PATINDEX('%[ ,/A-Z]%', field) <> 0
  END

UPDATE table
  SET field =
    REPLACE(field,SUBSTRING(field,PATINDEX('%.', field),1),'')
    WHERE PATINDEX('%.', field) <> 0

UPDATE table
SET field = NULL
WHERE field = ''

    In the first update, I also strip out empty spaces, commas, and slashes.
Obviously, other characters could be added.
    That second update may seem odd, but it is an attempt to avoid removing
a period unless it is at the end of the field -- my assumption being (though
imperfect, I know) that a period anywhere else in an otherwise numeric field
would likely be a decimal point. I suspect this can be cleaned up a bit so
that anything but a true decimal point would be removed. (I'll save that for
Monday... :)
    Comments? Suggestions? Revisions? And, as always, thanks for your help.

James Aylard