convert ###,###,##0 to INT

Hi,

I've imported an Excel file into a work table, via an Access Project. One of my fields is an integer, represented in Excel with thousands separator e.g. 3,137,458

The above now sits in a varchar column, and I need to convert these values to an INT. Strangely, is numeric() returns One, but then convert( int, ...) does not like the commas.

To add insult to injury, my MSDE does not seem to allow me to CREATE FUNCTION. It protests even if I do Grant Create Function to Login, while running as 'sa'. Side question: is this a known limitation of MSDE ?

Is there an efficient way to convert such strings to Int ?

I note that the commas may actually be missing, since their presence depends on the "Digit Grouping" value in the Regional Settings of Control Panel.

In the past, I was using Sybase, and I had to use set-based queries, running against a few work fields in my table. The first query would use charindex() to find the position of the first comma, if any. The second query would pick up the portion of the string up to the comma, then another query chasing the next comma, etc. Rather painful.Hmm...maybe you could try playing around with the replace command to filter out the commas.

I tested this 1 line code in QA and it works fine.

select cast(replace('3,137,458',',','') as int).|||oops, temporary blindness ... apologies ... please ignore this question

convert( int, REPLACE( column_name, ',', '' ) )|||thanks, mate, I've just found it at the same time. Works like a charm.
Me self-learner too...