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

[James Aylard]

Rory,

> Did you catch the post on the LEFT and PATINDEX functions?

    My apologies for not replying earlier. For some reason, my attempt
yesterday to implement your simple suggestion didn't work (can't recall
exactly what I tried to know what I must've done wrong), so I had set it
aside for the moment with the intent to come back to it. When I did get back
to it, I got it to work. Thank you!
    You wrote:

UPDATE table SET field = LEFT(field, PATINDEX('% %', field)) WHERE criteria

    My WHERE criteria were:

where patindex('%[A-Z]%',field) <> 0

    Additionally, since I wanted empty fields to be set to NULL once I
converted the column to type int, I added a second update statement:

update table
set field = null
where field = '' or field = ' '

    I suspect that there is a better way to do this, but it does work. (And
if I don't do this, empty fields are filled with a 0 when I convert the
column data type to int.)
    So, if you, or any other SQL gurus, can see a more efficient way to
accomplish this task, I am always eager to learn.
    Thanks again.

James Aylard