[thelist] SQL Server: How Can I Convert VARCHAR to INT?
Ryan Finley
RyanF at SonicFoundry.com
Thu Aug 16 11:55:18 CDT 2001
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 !
More information about the thelist
mailing list