Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Posts
    6

    Unanswered: test datatype before converting

    Can anyone tell me how to test a field in a sql statment before converting it?

    I want to do something like convert a text field to an int if the text field has a number in it, otherwise I would like the result to be blank.

    Right now, once it hits the record that contains text and therefore can't be converted, it gives the error: "Syntax error converting the nvarchar value 'mytext' to a column of data type int."

    Thanks!

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    You could use the function ISNUMERIC with a CASE statement

    SELECT CASE WHEN ISNUMERIC(myFld) THEN CAST(myFld AS int) ELSE '' END
    MCDBA

  3. #3
    Join Date
    Apr 2002
    Posts
    6
    Thank you very much! I'll give it a try.

Posting Permissions

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