Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Unanswered: 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.

  2. #2
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    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).
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  3. #3
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    oops, temporary blindness .... apologies ... please ignore this question

    convert( int, REPLACE( column_name, ',', '' ) )

  4. #4
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    thanks, mate, I've just found it at the same time. Works like a charm.
    Me self-learner too....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •