Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    80

    Unanswered: string to int conversion

    hi all,
    I have writen a function in which I'm selecting max of varchar datatype column after converting it into integer datatype ,though the where condition of the select statement retrives only numbers which are in string format but I'm getting conversion error for a string value which doesn't satisfy the condition. can any one explain this behaviour?
    Thanks in advance.

  2. #2
    Join Date
    Aug 2004
    Posts
    3
    Instead of lines of statements, why not post your T-SQL?

  3. #3
    Join Date
    Oct 2003
    Posts
    84
    You probably have a row which contains an invalid string representation of a number. Try this


    MAX(CAST(CASE ISNUMERIC(MyVarCharField) WHEN 1 THEN MyVarCharField ELSE NULL END AS INT))

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    yes ofcourse I did this.
    but I still Can't Understand why I'm getting Unmatched String value Exception

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Hopefully this thread will help a little:

    http://www.dbforums.com/t993650.html

  6. #6
    Join Date
    Jul 2004
    Posts
    52
    In addition to E and D, strings containing <comma>, $, and leading <tab> will evaluate to IsNumeric() = 1 but will fail to convert to int.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Find out what LIKE '%^[0-9]%' and NOT LIKE '%^[0-9]%] does by researching it in Books Online and testing it. You might find all kinds of neat little things to do with that and some simple variations of it.

    It is expensive though.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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