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

rudy r937 at interlog.com
Fri Aug 17 21:15:10 CDT 2001


>    Comments? Suggestions? Revisions?

seems like a lotta UPDATE TABLE statements -- in a loop, no less

i was always taught to do as few database calls as possible

now, if it's a real tight loop, inside a stored procedure, one of you
sql/server dba folks might be able to confirm whether or not multiple
database calls actually take place at all -- i sure don't know, but i
wouldn't be totally surprised to hear that it's all done in sql/server
memory and only one database update is done, after the loop is finished

but i doubt that's the way it actually runs

wouldn't it be better to do something like this (sincere apologies if this
isn't valid transact-sql) --

    @mystring=''
    loop  j=1 to len(varcharfield)
       if substring(varcharfield, j,1) is numeric
          @mystring = @mystring+substring(varcharfield, j,1)
     end loop
   varcharfield = @mystring

that way you can do the update in place, and *then* ALTER the table, no new
column required, the conversion should be good to go because all the values
will be numeric

i'm pushing my envelope here, stored procs are definitely not my strong
suit


rudy







More information about the thelist mailing list