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

[Ryan Finley]

Yep, SQL is better...

Something like:

ALTER TABLE BadTable ADD NewCol INT NULL

DECLARE Rows_Cursor CURSOR FOR
	SELECT RowID, OldCol
	FROM BadTable
	
OPEN Rows_Cursor

DECLARE @rowID int, @oldCol varchar(255)
FETCH NEXT FROM Rows_Cursor INTO @rowID, @oldCol

WHILE @@FETCH_STATUS = 0 BEGIN
	IF IsNumeric(@oldCol) = 1
		UPDATE BadTable SET NewCol = Cast(@oldCol as int) WHERE
RowID = @rowID
	FETCH NEXT FROM Rows_Cursor INTO @rowID, @oldCol
END

CLOSE Rows_Cursor
DEALLOCATE Rows_Cursor


	Ryan Finley
	President - SurveyMonkey.com (http://www.surveymonkey.com)

-----Original Message-----
From: James Aylard [mailto:webmaster at equilon-mrc.com] 
Sent: Thursday, August 16, 2001 11:38 AM
To: thelist at lists.evolt.org
Subject: Re: [thelist] SQL Server: How Can I Convert VARCHAR to INT?


Anthony,

> What Platform?

    ASP on WinNT 4.

> Create a New Column, INT Type, then you could write a Perl/PHP/VBS script
> to iterate through the rows discarding the Non-Integer data and place it
in
> the new column. Rename the olde column, rename the new column.

    Excellent suggestion. I had been looking for a SQL Server solution --
some SQL function or guru's trick that would allow me to do this within SQL
server. But your suggestion is outside the box into which I had put myself.
Creating a simple ASP page to do what you suggest seems simple enough.
    BTW, if anyone does have a SQL server trick to do this, I would still be
interested in hearing it.
    Thanks, Anthony.

James Aylard


---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !