SQL Server Performance

Integer in Varchar Columns

Discussion in 'SQL Server 2005 General Developer Questions' started by najeed_dba, Aug 20, 2007.

  1. najeed_dba New Member

    Hi all.
    I have some integer data in a table which is enclosed in double quotes.
    But, I want to perform some sum(), avg() function on those columns, but cannot do so, because it is Varchar() data.enclosed in Double Quotes.
    Sample Data:
    Column_Name -------- Datatype is varchar
    "123"
    "234"
    "234"
    If Some one can tell the workaround , it will be greatly appreciated.
    Thanks.
    najeed_dba, Aug 20, 2007
    #2

  2. FrankKalis Moderator

    SELECT SUM(CAST(REPLACE('your column', '"', '') AS INT)
    FROM table
    should do.
    But it's better to use the appropriate data types.
    FrankKalis, Aug 20, 2007
    #3

  3. najeed_dba New Member

    After i execute , got the error:Msg 245, Level 16, State 1, Line 2
    Conversion failed when converting the varchar value 'column_name' to data type int.
    Actually , the data came from CSV file(from pefrmon), so thats the reason it is varchar.
    Thanks.
    najeed_dba, Aug 20, 2007
    #4

  4. ndinakar Member

    Do you have any data that is of string type or is all the data numeric within quotes?
    ndinakar, Aug 20, 2007
    #5

  5. najeed_dba New Member

    Its all numeric. Actually, its the result of perfmon log file.
    Thanks.
    najeed_dba, Aug 20, 2007
    #6

  6. ndinakar Member

    You need to look through the data and randomly pick few numbers and run the conversion yourself. It would be almost impossible for us to guess which data in your table is causing the error. All we can tell is there is some varchar data in the table that SQL Server is unable to convert to INT. You have the data. You need to look through it and find out the offending data.
    ndinakar, Aug 20, 2007
    #7

  7. FrankKalis Moderator

    A rough indication of bad data, though certainly not 100% accurate is the use of ISNUMERIC(). If that return a 0 for an expression you do have data in your column that's not convertible to a numeric data type without modification.
    FrankKalis, Aug 20, 2007
    #8

  8. smy New Member

    this queries work:
    SELECT SUM(CAST((REPLACE(Column_Name, '"', '')) AS INT)) AS Column_Name FROM tbl
    SELECT AVG(CAST((REPLACE(Column_Name, '"', '')) AS INT)) AS Column_Name FROM tbl
    smy, Aug 20, 2007
    #9

  9. satya Moderator

    What counter data is that, may be you could get with DMVs if you are using SQL 2005.
    satya, Aug 21, 2007
    #10

  10. Madhivanan Moderator

    It means that you specified column_name withing a single quote like this

    SELECT SUM(CAST((REPLACE('Column_Name', '"', '')) AS INT)) AS Column_Name FROM (select 1 as column_name) tbl
    [quote user="najeed_dba"]
    After i execute , got the error:Msg 245, Level 16, State 1, Line 2
    Conversion failed when converting the varchar value 'column_name' to data type int.
    Actually , the data came from CSV file(from pefrmon), so thats the reason it is varchar.
    Thanks.[/quote]
    Madhivanan, Aug 21, 2007
    #11

  11. FrankKalis Moderator

    Oops, I guess that's my fault. I wrote
    ('your column'...) in my first reply. I should have written
    (< your column goes here >...
    FrankKalis, Aug 21, 2007
    #12

  12. ndinakar Member

    [quote user="FrankKalis"]
    Oops, I guess that's my fault. I wrote
    ('your column'...) in my first reply. I should have written
    (< your column goes here >...
    [/quote]
    Yeah.. slacker...[:p]..
    ndinakar, Aug 21, 2007
    #13

  13. FrankKalis Moderator

    <P mce_keep="true">Yes, shame on me!</P><P mce_keep="true"><IMG style="WIDTH: 71px; HEIGHT: 43px" height=43 src="http://www.insidesql.de/images/mixed-smiley-030.gif" width=71 mce_src="http://www.insidesql.de/images/mixed-smiley-030.gif"></P>
    FrankKalis, Aug 22, 2007
    #14

  14. Madhivanan Moderator

    <P mce_keep="true">I like that animation [:D]&nbsp; <P mce_keep="true">[quote user="FrankKalis"] <P>Yes, shame on me!</P><P><IMG style="WIDTH: 71px; HEIGHT: 43px" height=43 alt="" src="http://www.insidesql.de/images/mixed-smiley-030.gif" width=71></P>[/quote]
    Madhivanan, Aug 22, 2007
    #15

  15. satya Moderator

    You can embed such smileys with CS in this case [;)]
    satya, Aug 22, 2007
    #16

  16. Madhivanan Moderator

    <P mce_keep="true">&nbsp;as an attachment? <P mce_keep="true">[quote user="satya"] <P>You can embed such smileys with CS in this case <IMG alt=Wink src="http://sql-server-performance.com/Community/emoticons/emotion-5.gif"></P>[/quote]
    Madhivanan, Aug 22, 2007
    #17

  17. FrankKalis Moderator

    There is an Insert/edit image button when you use the editor to write a post. There you can specify a source URL for an image. [:)]
    FrankKalis, Aug 22, 2007
    #18

  18. satya Moderator

    Ahem, why not take this as a seperate discussion [:p]
    satya, Aug 22, 2007
    #19

Share This Page