Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    29

    Question Unanswered: How to verify & Convert char value to int?

    Hi

    I have varchar(10) column in a table which holds most of the time numeric data or 'A' value. Now I want to select that data as int. If the value is 'A', I should get 0 (Zero) value. If I use convert(int,column), it will work most of the time except when the value is 'A'. How can I write this query?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    If you have 'A' as character all the time in that column, then this will work...

    select OP=case when col1 = 'A' then 0 else convert(int,col1) end from <tablename>

    else this will work for any character in that column...

    select OP=case when ascii(col1) between 65 and 122 then 0 else convert(int,col1) end from <tablename>

    HTH.

  3. #3
    Join Date
    Oct 2002
    Posts
    29
    Thanks for the idea.
    Addition to that, if I have more than one char like 'AA','ZB'.. how can I verify that.

  4. #4
    Join Date
    Mar 2004
    Posts
    25
    Originally posted by nmr
    Thanks for the idea.
    Addition to that, if I have more than one char like 'AA','ZB'.. how can I verify that.
    The ascii function returns the ascii value for the first character in a varchar field. So, you can use this for longer strings, so long as the first character is always a letter. Where you will run into problems is if it is possible for your fields to be populated with '1AQ'... and at that point I'm stumped - unless you cycle through every character in the varchar field and test each individually (*shudder*)


    If anyone knows of a better way to do this, I'm interested too!

  5. #5
    Join Date
    Jan 2004
    Posts
    32
    Try this it should for in your case:

    select op = case when col1 like '%[A-z]%' then 0 else convert(int, col1) end
    from TableName

  6. #6
    Join Date
    Mar 2004
    Posts
    25
    Originally posted by ssglb
    Try this it should for in your case:

    select op = case when col1 like '%[A-z]%' then 0 else convert(int, col1) end
    from TableName

    Cool - that I never knew...

Posting Permissions

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