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.
  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.
  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.
  4. ndinakar Member

    Do you have any data that is of string type or is all the data numeric within quotes?
  5. najeed_dba New Member

    Its all numeric. Actually, its the result of perfmon log file.
    Thanks.
  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.
  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.
  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
  9. satya Moderator

    What counter data is that, may be you could get with DMVs if you are using SQL 2005.
  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]
  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 >...
  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]..
  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>
  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]
  15. satya Moderator

    You can embed such smileys with CS in this case [;)]
  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]
  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. [:)]
  18. satya Moderator

    Ahem, why not take this as a seperate discussion [:p]

Share This Page