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

[Joshua Olson]

James,

What I was thinking was this:

Instead of trying to get SQL to do the work, go ahead and use a combination.
Add a field to mark it as processed or not.  Use SQL to convert a majority
of the fields using a simple regex that checks for all number characters,
etc, or whatever, making sure to mark the fields as processed.

Then for the remainder of the fields, write a simple middleware piece (ASP
or CF or Perl or Whatever) that either uses it's built in conversion
functions (SELECT/convert/UPDATE type ordeal) or gives you, the human, a
chance to subjectively type in the numeric equivalent to the string.

This way you know the data is correct.

-joshua



----- Original Message -----
From: "James Aylard" <webmaster at equilon-mrc.com>
Subject: Re: [thelist] SQL Server: How Can I Convert VARCHAR to INT?


:     Basically, this is one-time clean-up as the application is being
: converted from a Microsoft Access VBA application (with data stored both
in
: MS Access tables and SQL Server tables) to a web-based application with a
: SQL-only database. As the data is transferred, I am making changes to
: certain columns to optimize data storage or improve data integrity (such
as
: changing 50-character varchar fields to int fields). Once the existing
data
: is scrubbed, this won't need to happen again.
:     So, efficiency is not as important as efficacy.
:
: James Aylard