Loading ...
Sorry, an error occurred while loading the content.

Fix a varchar column's data to Int

Expand Messages
  • John Warner
    Ok the problem. I have inherited an SQL Server 2005 database that is tied to a website (Intranet) that is the front end for data entry. On the screens a lot of
    Message 1 of 10 , Jan 6, 2011
    • 0 Attachment
      Ok the problem. I have inherited an SQL Server 2005 database that is tied
      to a website (Intranet) that is the front end for data entry. On the
      screens a lot of data is scanned in via hand held barcode readers. The
      reader software drops the data into <input type='text' /> textboxes based
      on focus. But the textboxes can also have data keyed into them. Note
      before we get much further I cannot alter the database and the data entry
      screens at present I have limited access to the code to repair the data
      validation for the problem I am about to tell you about.



      There is a field in the database that is a varchar(50) I think (not in
      front of it right now but sure of the varchar) that in fact is supposed to
      hold only Integers. If scanning is done right with the proper focus ect,
      only integers go into this field. The original developer appears to have
      not considered this might not happen this way every time. Result other non
      numeric data is in the column. Again I can't actually fix the root of the
      problem so any solution that involves me writing code to prevent this
      happening will not at present work -and I am not happy about this -.



      Ok I'm looking for a query that will let me clean this field of non
      integer values as it is killing a reporting program down the line. I
      worked my way though the data yesterday in 40,000+ only found about 50
      entries that were bad. I am looking to set a proc into the database that
      fix this column that the users can invoke without me.



      I am OK with replacing non integers with zero or 999. Null won't work for
      other reasons. I want the consumer of reports to be able to spot rows
      where there was an issue. Columns in the table



      OrderNo , cnt, batch, .



      Cnt is the column the other two will be different for each cnt that is cnt
      = 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
      will have different OrderNo batch combinations so you can get to row one's
      cnt =1 without hitting row 10's.



      Cnt is the column to clean. I thought about SELECT * FROM table WHERE
      ISNUMERIC(cnt) = false; and then some sort of Update.



      My question is how would you attack this. also note I encountered
      yesterday .1 and .5 (yes decimals, but they are invalid values so need to
      be reset, I suspect they are keying errors and should be 1 and 5
      respectively)?



      Thanks and I hope I'm more clear than mud.



      John Warner





      [Non-text portions of this message have been removed]
    • Paul Livengood
      I am not 100% sure i understand what you want...so let me phrase it how i understand it. You want an update statement to set the cnt field in your table to
      Message 2 of 10 , Jan 6, 2011
      • 0 Attachment
        I am not 100% sure i understand what you want...so let me phrase it how i
        understand it.

        You want an update statement to set the 'cnt' field in your table to '-0' (-0
        was used in COBAL as a numeric null), or some other value, when ever the current
        text of 'cnt' is not numeric and does not have a '\period in it. 


        Is that correct?
        if so you can use this

        UPDATE    a
        SET            cnt = '-0'
        FROM        Table1
        WHERE    ISNUMERIC(cnt) = false
        OR            cnt like '%.%'

        HTH
        Paul




        ________________________________
        From: John Warner <john@...>
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Thu, January 6, 2011 5:56:26 AM
        Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int

         
        Ok the problem. I have inherited an SQL Server 2005 database that is tied
        to a website (Intranet) that is the front end for data entry. On the
        screens a lot of data is scanned in via hand held barcode readers. The
        reader software drops the data into <input type='text' /> textboxes based
        on focus. But the textboxes can also have data keyed into them. Note
        before we get much further I cannot alter the database and the data entry
        screens at present I have limited access to the code to repair the data
        validation for the problem I am about to tell you about.

        There is a field in the database that is a varchar(50) I think (not in
        front of it right now but sure of the varchar) that in fact is supposed to
        hold only Integers. If scanning is done right with the proper focus ect,
        only integers go into this field. The original developer appears to have
        not considered this might not happen this way every time. Result other non
        numeric data is in the column. Again I can't actually fix the root of the
        problem so any solution that involves me writing code to prevent this
        happening will not at present work -and I am not happy about this -.

        Ok I'm looking for a query that will let me clean this field of non
        integer values as it is killing a reporting program down the line. I
        worked my way though the data yesterday in 40,000+ only found about 50
        entries that were bad. I am looking to set a proc into the database that
        fix this column that the users can invoke without me.

        I am OK with replacing non integers with zero or 999. Null won't work for
        other reasons. I want the consumer of reports to be able to spot rows
        where there was an issue. Columns in the table

        OrderNo , cnt, batch, .

        Cnt is the column the other two will be different for each cnt that is cnt
        = 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
        will have different OrderNo batch combinations so you can get to row one's
        cnt =1 without hitting row 10's.

        Cnt is the column to clean. I thought about SELECT * FROM table WHERE
        ISNUMERIC(cnt) = false; and then some sort of Update.

        My question is how would you attack this. also note I encountered
        yesterday .1 and .5 (yes decimals, but they are invalid values so need to
        be reset, I suspect they are keying errors and should be 1 and 5
        respectively)?

        Thanks and I hope I'm more clear than mud.

        John Warner

        [Non-text portions of this message have been removed]




        [Non-text portions of this message have been removed]
      • Paul Livengood
        sorry...correction UPDATE    a SET            cnt = -0 FROM        Table1 a WHERE    ISNUMERIC(cnt) = false OR            cnt
        Message 3 of 10 , Jan 6, 2011
        • 0 Attachment
          sorry...correction

          UPDATE    a
          SET            cnt = '-0'
          FROM        Table1 a
          WHERE    ISNUMERIC(cnt) = false
          OR            cnt like '%.%'





          ________________________________
          From: Paul Livengood <p_livengood@...>
          To: SQLQueriesNoCode@yahoogroups.com
          Sent: Thu, January 6, 2011 8:44:19 AM
          Subject: Re: [SQLQueriesNoCode] Fix a varchar column's data to Int

           
          I am not 100% sure i understand what you want...so let me phrase it how i
          understand it.

          You want an update statement to set the 'cnt' field in your table to '-0' (-0
          was used in COBAL as a numeric null), or some other value, when ever the current

          text of 'cnt' is not numeric and does not have a '\period in it. 

          Is that correct?
          if so you can use this

          UPDATE    a
          SET            cnt = '-0'
          FROM        Table1
          WHERE    ISNUMERIC(cnt) = false
          OR            cnt like '%.%'

          HTH
          Paul

          ________________________________
          From: John Warner <john@...>
          To: SQLQueriesNoCode@yahoogroups.com
          Sent: Thu, January 6, 2011 5:56:26 AM
          Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int

           
          Ok the problem. I have inherited an SQL Server 2005 database that is tied
          to a website (Intranet) that is the front end for data entry. On the
          screens a lot of data is scanned in via hand held barcode readers. The
          reader software drops the data into <input type='text' /> textboxes based
          on focus. But the textboxes can also have data keyed into them. Note
          before we get much further I cannot alter the database and the data entry
          screens at present I have limited access to the code to repair the data
          validation for the problem I am about to tell you about.

          There is a field in the database that is a varchar(50) I think (not in
          front of it right now but sure of the varchar) that in fact is supposed to
          hold only Integers. If scanning is done right with the proper focus ect,
          only integers go into this field. The original developer appears to have
          not considered this might not happen this way every time. Result other non
          numeric data is in the column. Again I can't actually fix the root of the
          problem so any solution that involves me writing code to prevent this
          happening will not at present work -and I am not happy about this -.

          Ok I'm looking for a query that will let me clean this field of non
          integer values as it is killing a reporting program down the line. I
          worked my way though the data yesterday in 40,000+ only found about 50
          entries that were bad. I am looking to set a proc into the database that
          fix this column that the users can invoke without me.

          I am OK with replacing non integers with zero or 999. Null won't work for
          other reasons. I want the consumer of reports to be able to spot rows
          where there was an issue. Columns in the table

          OrderNo , cnt, batch, .

          Cnt is the column the other two will be different for each cnt that is cnt
          = 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
          will have different OrderNo batch combinations so you can get to row one's
          cnt =1 without hitting row 10's.

          Cnt is the column to clean. I thought about SELECT * FROM table WHERE
          ISNUMERIC(cnt) = false; and then some sort of Update.

          My question is how would you attack this. also note I encountered
          yesterday .1 and .5 (yes decimals, but they are invalid values so need to
          be reset, I suspect they are keying errors and should be 1 and 5
          respectively)?

          Thanks and I hope I'm more clear than mud.

          John Warner

          [Non-text portions of this message have been removed]

          [Non-text portions of this message have been removed]




          [Non-text portions of this message have been removed]
        • John Warner
          You understood correctly, I ll give that a try Paul. Thanks. I ll have to check on the negative but yours looks better then the hoops I was considering. John
          Message 4 of 10 , Jan 6, 2011
          • 0 Attachment
            You understood correctly, I'll give that a try Paul. Thanks.

            I'll have to check on the negative but yours looks better then the hoops I was considering.

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
            > Sent: Thursday, January 06, 2011 10:44 AM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: Re: [SQLQueriesNoCode] Fix a varchar column's data to Int
            >
            > I am not 100% sure i understand what you want...so let me phrase it how i
            > understand it.
            >
            > You want an update statement to set the 'cnt' field in your table to '-0' (-0
            > was used in COBAL as a numeric null), or some other value, when ever the
            > current
            > text of 'cnt' is not numeric and does not have a '\period in it.
            >
            >
            > Is that correct?
            > if so you can use this
            >
            > UPDATE a
            > SET cnt = '-0'
            > FROM Table1
            > WHERE ISNUMERIC(cnt) = false
            > OR cnt like '%.%'
            >
            > HTH
            > Paul
            >
            >
            >
            >
            > ________________________________
            > From: John Warner <john@...>
            > To: SQLQueriesNoCode@yahoogroups.com
            > Sent: Thu, January 6, 2011 5:56:26 AM
            > Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
            >
            >
            > Ok the problem. I have inherited an SQL Server 2005 database that is tied
            > to a website (Intranet) that is the front end for data entry. On the
            > screens a lot of data is scanned in via hand held barcode readers. The
            > reader software drops the data into <input type='text' /> textboxes based
            > on focus. But the textboxes can also have data keyed into them. Note
            > before we get much further I cannot alter the database and the data entry
            > screens at present I have limited access to the code to repair the data
            > validation for the problem I am about to tell you about.
            >
            > There is a field in the database that is a varchar(50) I think (not in
            > front of it right now but sure of the varchar) that in fact is supposed to
            > hold only Integers. If scanning is done right with the proper focus ect,
            > only integers go into this field. The original developer appears to have
            > not considered this might not happen this way every time. Result other non
            > numeric data is in the column. Again I can't actually fix the root of the
            > problem so any solution that involves me writing code to prevent this
            > happening will not at present work -and I am not happy about this -.
            >
            > Ok I'm looking for a query that will let me clean this field of non
            > integer values as it is killing a reporting program down the line. I
            > worked my way though the data yesterday in 40,000+ only found about 50
            > entries that were bad. I am looking to set a proc into the database that
            > fix this column that the users can invoke without me.
            >
            > I am OK with replacing non integers with zero or 999. Null won't work for
            > other reasons. I want the consumer of reports to be able to spot rows
            > where there was an issue. Columns in the table
            >
            > OrderNo , cnt, batch, .
            >
            > Cnt is the column the other two will be different for each cnt that is cnt
            > = 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
            > will have different OrderNo batch combinations so you can get to row one's
            > cnt =1 without hitting row 10's.
            >
            > Cnt is the column to clean. I thought about SELECT * FROM table WHERE
            > ISNUMERIC(cnt) = false; and then some sort of Update.
            >
            > My question is how would you attack this. also note I encountered
            > yesterday .1 and .5 (yes decimals, but they are invalid values so need to
            > be reset, I suspect they are keying errors and should be 1 and 5
            > respectively)?
            >
            > Thanks and I hope I'm more clear than mud.
            >
            > John Warner
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          • John Warner
            No problem I understood the (-0 was an error Thanks again! John Warner
            Message 5 of 10 , Jan 6, 2011
            • 0 Attachment
              No problem I understood the (-0 was an 'error'

              Thanks again!

              John Warner


              > -----Original Message-----
              > From: SQLQueriesNoCode@yahoogroups.com
              > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Paul Livengood
              > Sent: Thursday, January 06, 2011 10:45 AM
              > To: SQLQueriesNoCode@yahoogroups.com
              > Subject: Re: [SQLQueriesNoCode] Fix a varchar column's data to Int
              >
              > sorry...correction
              >
              > UPDATE a
              > SET cnt = '-0'
              > FROM Table1 a
              > WHERE ISNUMERIC(cnt) = false
              > OR cnt like '%.%'
              >
              >
              >
              >
              >
              > ________________________________
              > From: Paul Livengood <p_livengood@...>
              > To: SQLQueriesNoCode@yahoogroups.com
              > Sent: Thu, January 6, 2011 8:44:19 AM
              > Subject: Re: [SQLQueriesNoCode] Fix a varchar column's data to Int
              >
              >
              > I am not 100% sure i understand what you want...so let me phrase it how i
              > understand it.
              >
              > You want an update statement to set the 'cnt' field in your table to '-0' (-0
              > was used in COBAL as a numeric null), or some other value, when ever the
              > current
              >
              > text of 'cnt' is not numeric and does not have a '\period in it.
              >
              > Is that correct?
              > if so you can use this
              >
              > UPDATE a
              > SET cnt = '-0'
              > FROM Table1
              > WHERE ISNUMERIC(cnt) = false
              > OR cnt like '%.%'
              >
              > HTH
              > Paul
              >
              > ________________________________
              > From: John Warner <john@...>
              > To: SQLQueriesNoCode@yahoogroups.com
              > Sent: Thu, January 6, 2011 5:56:26 AM
              > Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
              >
              >
              > Ok the problem. I have inherited an SQL Server 2005 database that is tied
              > to a website (Intranet) that is the front end for data entry. On the
              > screens a lot of data is scanned in via hand held barcode readers. The
              > reader software drops the data into <input type='text' /> textboxes based
              > on focus. But the textboxes can also have data keyed into them. Note
              > before we get much further I cannot alter the database and the data entry
              > screens at present I have limited access to the code to repair the data
              > validation for the problem I am about to tell you about.
              >
              > There is a field in the database that is a varchar(50) I think (not in
              > front of it right now but sure of the varchar) that in fact is supposed to
              > hold only Integers. If scanning is done right with the proper focus ect,
              > only integers go into this field. The original developer appears to have
              > not considered this might not happen this way every time. Result other non
              > numeric data is in the column. Again I can't actually fix the root of the
              > problem so any solution that involves me writing code to prevent this
              > happening will not at present work -and I am not happy about this -.
              >
              > Ok I'm looking for a query that will let me clean this field of non
              > integer values as it is killing a reporting program down the line. I
              > worked my way though the data yesterday in 40,000+ only found about 50
              > entries that were bad. I am looking to set a proc into the database that
              > fix this column that the users can invoke without me.
              >
              > I am OK with replacing non integers with zero or 999. Null won't work for
              > other reasons. I want the consumer of reports to be able to spot rows
              > where there was an issue. Columns in the table
              >
              > OrderNo , cnt, batch, .
              >
              > Cnt is the column the other two will be different for each cnt that is cnt
              > = 1 in row one and cnt = 1 in row 10 (assume a sort on any other column)
              > will have different OrderNo batch combinations so you can get to row one's
              > cnt =1 without hitting row 10's.
              >
              > Cnt is the column to clean. I thought about SELECT * FROM table WHERE
              > ISNUMERIC(cnt) = false; and then some sort of Update.
              >
              > My question is how would you attack this. also note I encountered
              > yesterday .1 and .5 (yes decimals, but they are invalid values so need to
              > be reset, I suspect they are keying errors and should be 1 and 5
              > respectively)?
              >
              > Thanks and I hope I'm more clear than mud.
              >
              > John Warner
              >
              > [Non-text portions of this message have been removed]
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >
              > ------------------------------------
              >
              > Yahoo! Groups Links
              >
              >
              >
            • Arnie Rowland
              John, Would it help to remove the non-integer characters and keep any integer characters -or do you wish to just clear out the entire value replacing with a
              Message 6 of 10 , Jan 6, 2011
              • 0 Attachment
                John,

                Would it help to remove the non-integer characters and keep any integer
                characters -or do you wish to just clear out the entire value replacing
                with a marker?

                The sorting is not an issue, it is easily accomplished by using a cast()
                in the ORDER BY clause.


                Regards,

                Arnie Rowland, MVP (SQL Server)

                "You cannot do a kindness too soon because you never know how soon it
                will be too late."
                -Ralph Waldo Emerson




                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com
                [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                Sent: Thursday, January 06, 2011 4:59 AM
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int

                Ok the problem. I have inherited an SQL Server 2005 database that is
                tied to a website (Intranet) that is the front end for data entry. On
                the screens a lot of data is scanned in via hand held barcode readers.
                The reader software drops the data into <input type='text' /> textboxes
                based on focus. But the textboxes can also have data keyed into them.
                Note before we get much further I cannot alter the database and the data
                entry screens at present I have limited access to the code to repair the
                data validation for the problem I am about to tell you about.



                There is a field in the database that is a varchar(50) I think (not in
                front of it right now but sure of the varchar) that in fact is supposed
                to hold only Integers. If scanning is done right with the proper focus
                ect, only integers go into this field. The original developer appears to
                have not considered this might not happen this way every time. Result
                other non numeric data is in the column. Again I can't actually fix the
                root of the problem so any solution that involves me writing code to
                prevent this happening will not at present work -and I am not happy
                about this -.



                Ok I'm looking for a query that will let me clean this field of non
                integer values as it is killing a reporting program down the line. I
                worked my way though the data yesterday in 40,000+ only found about 50
                entries that were bad. I am looking to set a proc into the database that
                fix this column that the users can invoke without me.



                I am OK with replacing non integers with zero or 999. Null won't work
                for other reasons. I want the consumer of reports to be able to spot
                rows where there was an issue. Columns in the table



                OrderNo , cnt, batch, .



                Cnt is the column the other two will be different for each cnt that is
                cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
                column) will have different OrderNo batch combinations so you can get to
                row one's cnt =1 without hitting row 10's.




                Cnt is the column to clean. I thought about SELECT * FROM table WHERE
                ISNUMERIC(cnt) = false; and then some sort of Update.



                My question is how would you attack this. also note I encountered
                yesterday .1 and .5 (yes decimals, but they are invalid values so need
                to be reset, I suspect they are keying errors and should be 1 and 5
                respectively)?



                Thanks and I hope I'm more clear than mud.



                John Warner





                [Non-text portions of this message have been removed]



                ------------------------------------

                Yahoo! Groups Links







                Disclaimer - January 6, 2011
                This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
              • John Warner
                Oh yes, that would be even better. CAST() further down the road in the process is what is causing me to need to scrub the field. What am I missing? Thanks.
                Message 7 of 10 , Jan 6, 2011
                • 0 Attachment
                  Oh yes, that would be even better.

                  CAST() further down the road in the process is what is causing me to need
                  to 'scrub' the field. What am I missing?


                  Thanks.

                  John Warner


                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                  > Sent: Thursday, January 06, 2011 12:00 PM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
                  >
                  > John,
                  >
                  > Would it help to remove the non-integer characters and keep any integer
                  > characters -or do you wish to just clear out the entire value replacing
                  > with a marker?
                  >
                  > The sorting is not an issue, it is easily accomplished by using a cast()
                  > in the ORDER BY clause.
                  >
                  >
                  > Regards,
                  >
                  > Arnie Rowland, MVP (SQL Server)
                  >
                  > "You cannot do a kindness too soon because you never know how soon it
                  > will be too late."
                  > -Ralph Waldo Emerson
                  >
                  >
                  >
                  >
                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  > Sent: Thursday, January 06, 2011 4:59 AM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
                  >
                  > Ok the problem. I have inherited an SQL Server 2005 database that is
                  > tied to a website (Intranet) that is the front end for data entry. On
                  > the screens a lot of data is scanned in via hand held barcode readers.
                  > The reader software drops the data into <input type='text' /> textboxes
                  > based on focus. But the textboxes can also have data keyed into them.
                  > Note before we get much further I cannot alter the database and the data
                  > entry screens at present I have limited access to the code to repair the
                  > data validation for the problem I am about to tell you about.
                  >
                  >
                  >
                  > There is a field in the database that is a varchar(50) I think (not in
                  > front of it right now but sure of the varchar) that in fact is supposed
                  > to hold only Integers. If scanning is done right with the proper focus
                  > ect, only integers go into this field. The original developer appears to
                  > have not considered this might not happen this way every time. Result
                  > other non numeric data is in the column. Again I can't actually fix the
                  > root of the problem so any solution that involves me writing code to
                  > prevent this happening will not at present work -and I am not happy
                  > about this -.
                  >
                  >
                  >
                  > Ok I'm looking for a query that will let me clean this field of non
                  > integer values as it is killing a reporting program down the line. I
                  > worked my way though the data yesterday in 40,000+ only found about 50
                  > entries that were bad. I am looking to set a proc into the database that
                  > fix this column that the users can invoke without me.
                  >
                  >
                  >
                  > I am OK with replacing non integers with zero or 999. Null won't work
                  > for other reasons. I want the consumer of reports to be able to spot
                  > rows where there was an issue. Columns in the table
                  >
                  >
                  >
                  > OrderNo , cnt, batch, .
                  >
                  >
                  >
                  > Cnt is the column the other two will be different for each cnt that is
                  > cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
                  > column) will have different OrderNo batch combinations so you can get to
                  > row one's cnt =1 without hitting row 10's.
                  >
                  >
                  >
                  >
                  > Cnt is the column to clean. I thought about SELECT * FROM table WHERE
                  > ISNUMERIC(cnt) = false; and then some sort of Update.
                  >
                  >
                  >
                  > My question is how would you attack this. also note I encountered
                  > yesterday .1 and .5 (yes decimals, but they are invalid values so need
                  > to be reset, I suspect they are keying errors and should be 1 and 5
                  > respectively)?
                  >
                  >
                  >
                  > Thanks and I hope I'm more clear than mud.
                  >
                  >
                  >
                  > John Warner
                  >
                  >
                  >
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                  >
                  >
                  >
                  >
                  > Disclaimer - January 6, 2011
                  > This email and any files transmitted with it are confidential and
                  intended solely
                  > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                  > addressee you should not disseminate, distribute, copy or alter this
                  email. Any
                  > views or opinions presented in this email are solely those of the author
                  and
                  > might not represent those of Westwood Consulting, Inc. Warning: Although
                  > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                  > viruses are present in this email, the company cannot accept
                  responsibility for
                  > any loss or damage arising from the use of this email or attachments.
                  > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                  >
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                • Arnie Rowland
                  John, Here is a UDF that I created a long time ago. You may be able to use it to clean the data, then your queries can be sorted properly. USE tempdb; GO
                  Message 8 of 10 , Jan 6, 2011
                  • 0 Attachment
                    John,

                    Here is a UDF that I created a long time ago. You may be able to use it
                    to 'clean' the data, then your queries can be sorted properly.

                    USE tempdb;
                    GO

                    CREATE FUNCTION dbo.fnNumericOnly
                    ( @InParam varchar(50) )
                    RETURNS varchar(50)
                    AS
                    BEGIN
                    IF patindex( '%[^0-9]%', @InParam ) > 0
                    BEGIN
                    WHILE patindex( '%[^0-9]%', @InParam ) > 0
                    BEGIN
                    SET @InParam = Stuff( @InParam,
                    patindex( '%[^0-9]%', @InParam), 1, '' )
                    END
                    END
                    RETURN @InParam
                    END;
                    GO

                    CREATE TABLE #TestTable
                    ( RowId int IDENTITY
                    , SomeValue varchar(20)
                    );

                    INSERT INTO #TestTable
                    ( SomeValue )
                    VALUES
                    ( '1' ),
                    ( '25x' ),
                    ( '.5' ),
                    ( 'a2x5m' ),
                    ( 'a05' ),
                    ( 'b1x5m' );

                    UPDATE #TestTable
                    SET SomeValue = dbo.fnNumericOnly( SomeValue );

                    SELECT SomeValue
                    FROM #TestTable
                    ORDER BY cast(SomeValue AS int);

                    DROP TABLE #TestTable;
                    DROP FUNCTION dbo.fnNumericOnly;
                    GO

                    The results will order like this. NOTE: If you wish to eliminate leading
                    zeros, that is another small issue but can be easily done.
                    SomeValue
                    1
                    5
                    05
                    15
                    25
                    25

                    Regards,

                    Arnie Rowland, MVP (SQL Server)

                    "You cannot do a kindness too soon because you never know how soon it
                    will be too late."
                    -Ralph Waldo Emerson




                    -----Original Message-----
                    From: SQLQueriesNoCode@yahoogroups.com
                    [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                    Sent: Thursday, January 06, 2011 9:08 AM
                    To: SQLQueriesNoCode@yahoogroups.com
                    Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int

                    Oh yes, that would be even better.

                    CAST() further down the road in the process is what is causing me to
                    need to 'scrub' the field. What am I missing?


                    Thanks.

                    John Warner


                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                    > Sent: Thursday, January 06, 2011 12:00 PM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
                    >
                    > John,
                    >
                    > Would it help to remove the non-integer characters and keep any
                    > integer characters -or do you wish to just clear out the entire value
                    > replacing with a marker?
                    >
                    > The sorting is not an issue, it is easily accomplished by using a
                    > cast() in the ORDER BY clause.
                    >
                    >
                    > Regards,
                    >
                    > Arnie Rowland, MVP (SQL Server)
                    >
                    > "You cannot do a kindness too soon because you never know how soon it
                    > will be too late."
                    > -Ralph Waldo Emerson
                    >
                    >
                    >
                    >
                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                    > Sent: Thursday, January 06, 2011 4:59 AM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
                    >
                    > Ok the problem. I have inherited an SQL Server 2005 database that is
                    > tied to a website (Intranet) that is the front end for data entry. On
                    > the screens a lot of data is scanned in via hand held barcode readers.
                    > The reader software drops the data into <input type='text' />
                    > textboxes based on focus. But the textboxes can also have data keyed
                    into them.
                    > Note before we get much further I cannot alter the database and the
                    > data entry screens at present I have limited access to the code to
                    > repair the data validation for the problem I am about to tell you
                    about.
                    >
                    >
                    >
                    > There is a field in the database that is a varchar(50) I think (not in

                    > front of it right now but sure of the varchar) that in fact is
                    > supposed to hold only Integers. If scanning is done right with the
                    > proper focus ect, only integers go into this field. The original
                    > developer appears to have not considered this might not happen this
                    > way every time. Result other non numeric data is in the column. Again
                    > I can't actually fix the root of the problem so any solution that
                    > involves me writing code to prevent this happening will not at present

                    > work -and I am not happy about this -.
                    >
                    >
                    >
                    > Ok I'm looking for a query that will let me clean this field of non
                    > integer values as it is killing a reporting program down the line. I
                    > worked my way though the data yesterday in 40,000+ only found about 50

                    > entries that were bad. I am looking to set a proc into the database
                    > that fix this column that the users can invoke without me.
                    >
                    >
                    >
                    > I am OK with replacing non integers with zero or 999. Null won't work
                    > for other reasons. I want the consumer of reports to be able to spot
                    > rows where there was an issue. Columns in the table
                    >
                    >
                    >
                    > OrderNo , cnt, batch, .
                    >
                    >
                    >
                    > Cnt is the column the other two will be different for each cnt that is

                    > cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
                    > column) will have different OrderNo batch combinations so you can get
                    > to row one's cnt =1 without hitting row 10's.
                    >
                    >
                    >
                    >
                    > Cnt is the column to clean. I thought about SELECT * FROM table WHERE
                    > ISNUMERIC(cnt) = false; and then some sort of Update.
                    >
                    >
                    >
                    > My question is how would you attack this. also note I encountered
                    > yesterday .1 and .5 (yes decimals, but they are invalid values so need

                    > to be reset, I suspect they are keying errors and should be 1 and 5
                    > respectively)?
                    >
                    >
                    >
                    > Thanks and I hope I'm more clear than mud.
                    >
                    >
                    >
                    > John Warner
                    >
                    >
                    >
                    >
                    >
                    > [Non-text portions of this message have been removed]
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                    >
                    >
                    >
                    >
                    > Disclaimer - January 6, 2011
                    > This email and any files transmitted with it are confidential and
                    intended solely
                    > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                    > addressee you should not disseminate, distribute, copy or alter this
                    email. Any
                    > views or opinions presented in this email are solely those of the
                    > author
                    and
                    > might not represent those of Westwood Consulting, Inc. Warning:
                    > Although Westwood Consulting, Inc has taken reasonable precautions to
                    > ensure no viruses are present in this email, the company cannot accept
                    responsibility for
                    > any loss or damage arising from the use of this email or attachments.
                    > This disclaimer was added by Policy Patrol:
                    > http://www.policypatrol.com/
                    >
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >



                    ------------------------------------

                    Yahoo! Groups Links







                    [Non-text portions of this message have been removed]
                  • John Warner
                    Oh, you have misunderstood, it isn t a sorting issue. But let me play with this as it still may well work just fine. John Warner
                    Message 9 of 10 , Jan 6, 2011
                    • 0 Attachment
                      Oh, you have misunderstood, it isn't a sorting issue. But let me play with
                      this as it still may well work just fine.

                      John Warner


                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                      > Sent: Thursday, January 06, 2011 12:53 PM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
                      >
                      > John,
                      >
                      > Here is a UDF that I created a long time ago. You may be able to use it
                      > to 'clean' the data, then your queries can be sorted properly.
                      >
                      > USE tempdb;
                      > GO
                      >
                      > CREATE FUNCTION dbo.fnNumericOnly
                      > ( @InParam varchar(50) )
                      > RETURNS varchar(50)
                      > AS
                      > BEGIN
                      > IF patindex( '%[^0-9]%', @InParam ) > 0
                      > BEGIN
                      > WHILE patindex( '%[^0-9]%', @InParam ) > 0
                      > BEGIN
                      > SET @InParam = Stuff( @InParam,
                      > patindex( '%[^0-9]%', @InParam), 1, '' )
                      > END
                      > END
                      > RETURN @InParam
                      > END;
                      > GO
                      >
                      > CREATE TABLE #TestTable
                      > ( RowId int IDENTITY
                      > , SomeValue varchar(20)
                      > );
                      >
                      > INSERT INTO #TestTable
                      > ( SomeValue )
                      > VALUES
                      > ( '1' ),
                      > ( '25x' ),
                      > ( '.5' ),
                      > ( 'a2x5m' ),
                      > ( 'a05' ),
                      > ( 'b1x5m' );
                      >
                      > UPDATE #TestTable
                      > SET SomeValue = dbo.fnNumericOnly( SomeValue );
                      >
                      > SELECT SomeValue
                      > FROM #TestTable
                      > ORDER BY cast(SomeValue AS int);
                      >
                      > DROP TABLE #TestTable;
                      > DROP FUNCTION dbo.fnNumericOnly;
                      > GO
                      >
                      > The results will order like this. NOTE: If you wish to eliminate leading
                      > zeros, that is another small issue but can be easily done.
                      > SomeValue
                      > 1
                      > 5
                      > 05
                      > 15
                      > 25
                      > 25
                      >
                      > Regards,
                      >
                      > Arnie Rowland, MVP (SQL Server)
                      >
                      > "You cannot do a kindness too soon because you never know how soon it
                      > will be too late."
                      > -Ralph Waldo Emerson
                      >
                      >
                      >
                      >
                      > -----Original Message-----
                      > From: SQLQueriesNoCode@yahoogroups.com
                      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                      > Sent: Thursday, January 06, 2011 9:08 AM
                      > To: SQLQueriesNoCode@yahoogroups.com
                      > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
                      >
                      > Oh yes, that would be even better.
                      >
                      > CAST() further down the road in the process is what is causing me to
                      > need to 'scrub' the field. What am I missing?
                      >
                      >
                      > Thanks.
                      >
                      > John Warner
                      >
                      >
                      > > -----Original Message-----
                      > > From: SQLQueriesNoCode@yahoogroups.com
                      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie
                      > Rowland
                      > > Sent: Thursday, January 06, 2011 12:00 PM
                      > > To: SQLQueriesNoCode@yahoogroups.com
                      > > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
                      > >
                      > > John,
                      > >
                      > > Would it help to remove the non-integer characters and keep any
                      > > integer characters -or do you wish to just clear out the entire value
                      > > replacing with a marker?
                      > >
                      > > The sorting is not an issue, it is easily accomplished by using a
                      > > cast() in the ORDER BY clause.
                      > >
                      > >
                      > > Regards,
                      > >
                      > > Arnie Rowland, MVP (SQL Server)
                      > >
                      > > "You cannot do a kindness too soon because you never know how soon it
                      > > will be too late."
                      > > -Ralph Waldo Emerson
                      > >
                      > >
                      > >
                      > >
                      > > -----Original Message-----
                      > > From: SQLQueriesNoCode@yahoogroups.com
                      > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                      > > Sent: Thursday, January 06, 2011 4:59 AM
                      > > To: SQLQueriesNoCode@yahoogroups.com
                      > > Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
                      > >
                      > > Ok the problem. I have inherited an SQL Server 2005 database that is
                      > > tied to a website (Intranet) that is the front end for data entry. On
                      > > the screens a lot of data is scanned in via hand held barcode readers.
                      > > The reader software drops the data into <input type='text' />
                      > > textboxes based on focus. But the textboxes can also have data keyed
                      > into them.
                      > > Note before we get much further I cannot alter the database and the
                      > > data entry screens at present I have limited access to the code to
                      > > repair the data validation for the problem I am about to tell you
                      > about.
                      > >
                      > >
                      > >
                      > > There is a field in the database that is a varchar(50) I think (not in
                      >
                      > > front of it right now but sure of the varchar) that in fact is
                      > > supposed to hold only Integers. If scanning is done right with the
                      > > proper focus ect, only integers go into this field. The original
                      > > developer appears to have not considered this might not happen this
                      > > way every time. Result other non numeric data is in the column. Again
                      > > I can't actually fix the root of the problem so any solution that
                      > > involves me writing code to prevent this happening will not at present
                      >
                      > > work -and I am not happy about this -.
                      > >
                      > >
                      > >
                      > > Ok I'm looking for a query that will let me clean this field of non
                      > > integer values as it is killing a reporting program down the line. I
                      > > worked my way though the data yesterday in 40,000+ only found about 50
                      >
                      > > entries that were bad. I am looking to set a proc into the database
                      > > that fix this column that the users can invoke without me.
                      > >
                      > >
                      > >
                      > > I am OK with replacing non integers with zero or 999. Null won't work
                      > > for other reasons. I want the consumer of reports to be able to spot
                      > > rows where there was an issue. Columns in the table
                      > >
                      > >
                      > >
                      > > OrderNo , cnt, batch, .
                      > >
                      > >
                      > >
                      > > Cnt is the column the other two will be different for each cnt that is
                      >
                      > > cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
                      > > column) will have different OrderNo batch combinations so you can get
                      > > to row one's cnt =1 without hitting row 10's.
                      > >
                      > >
                      > >
                      > >
                      > > Cnt is the column to clean. I thought about SELECT * FROM table WHERE
                      > > ISNUMERIC(cnt) = false; and then some sort of Update.
                      > >
                      > >
                      > >
                      > > My question is how would you attack this. also note I encountered
                      > > yesterday .1 and .5 (yes decimals, but they are invalid values so need
                      >
                      > > to be reset, I suspect they are keying errors and should be 1 and 5
                      > > respectively)?
                      > >
                      > >
                      > >
                      > > Thanks and I hope I'm more clear than mud.
                      > >
                      > >
                      > >
                      > > John Warner
                      > >
                      > >
                      > >
                      > >
                      > >
                      > > [Non-text portions of this message have been removed]
                      > >
                      > >
                      > >
                      > > ------------------------------------
                      > >
                      > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      > >
                      > >
                      > >
                      > >
                      > > Disclaimer - January 6, 2011
                      > > This email and any files transmitted with it are confidential and
                      > intended solely
                      > > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                      > > addressee you should not disseminate, distribute, copy or alter this
                      > email. Any
                      > > views or opinions presented in this email are solely those of the
                      > > author
                      > and
                      > > might not represent those of Westwood Consulting, Inc. Warning:
                      > > Although Westwood Consulting, Inc has taken reasonable precautions to
                      > > ensure no viruses are present in this email, the company cannot accept
                      > responsibility for
                      > > any loss or damage arising from the use of this email or attachments.
                      > > This disclaimer was added by Policy Patrol:
                      > > http://www.policypatrol.com/
                      > >
                      > >
                      > >
                      > >
                      > > ------------------------------------
                      > >
                      > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                      > [Non-text portions of this message have been removed]
                      >
                      >
                      >
                      > ------------------------------------
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                    • Arnie Rowland
                      I understood it to be primarily a bad character issue. Sorting was secondary. Regards, Arnie Rowland, MVP (SQL Server) You cannot do a kindness too soon
                      Message 10 of 10 , Jan 6, 2011
                      • 0 Attachment
                        I understood it to be primarily a 'bad character' issue. Sorting was
                        secondary.



                        Regards,

                        Arnie Rowland, MVP (SQL Server)

                        "You cannot do a kindness too soon because you never know how soon it
                        will be too late."
                        -Ralph Waldo Emerson




                        -----Original Message-----
                        From: SQLQueriesNoCode@yahoogroups.com
                        [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                        Sent: Thursday, January 06, 2011 10:36 AM
                        To: SQLQueriesNoCode@yahoogroups.com
                        Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int

                        Oh, you have misunderstood, it isn't a sorting issue. But let me play
                        with this as it still may well work just fine.

                        John Warner


                        > -----Original Message-----
                        > From: SQLQueriesNoCode@yahoogroups.com
                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                        > Sent: Thursday, January 06, 2011 12:53 PM
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
                        >
                        > John,
                        >
                        > Here is a UDF that I created a long time ago. You may be able to use
                        > it to 'clean' the data, then your queries can be sorted properly.
                        >
                        > USE tempdb;
                        > GO
                        >
                        > CREATE FUNCTION dbo.fnNumericOnly
                        > ( @InParam varchar(50) )
                        > RETURNS varchar(50)
                        > AS
                        > BEGIN
                        > IF patindex( '%[^0-9]%', @InParam ) > 0
                        > BEGIN
                        > WHILE patindex( '%[^0-9]%', @InParam ) > 0
                        > BEGIN
                        > SET @InParam = Stuff( @InParam,
                        patindex(
                        > '%[^0-9]%', @InParam), 1, '' )
                        > END
                        > END
                        > RETURN @InParam
                        > END;
                        > GO
                        >
                        > CREATE TABLE #TestTable
                        > ( RowId int IDENTITY
                        > , SomeValue varchar(20)
                        > );
                        >
                        > INSERT INTO #TestTable
                        > ( SomeValue )
                        > VALUES
                        > ( '1' ),
                        > ( '25x' ),
                        > ( '.5' ),
                        > ( 'a2x5m' ),
                        > ( 'a05' ),
                        > ( 'b1x5m' );
                        >
                        > UPDATE #TestTable
                        > SET SomeValue = dbo.fnNumericOnly( SomeValue );
                        >
                        > SELECT SomeValue
                        > FROM #TestTable
                        > ORDER BY cast(SomeValue AS int);
                        >
                        > DROP TABLE #TestTable;
                        > DROP FUNCTION dbo.fnNumericOnly;
                        > GO
                        >
                        > The results will order like this. NOTE: If you wish to eliminate
                        > leading zeros, that is another small issue but can be easily done.
                        > SomeValue
                        > 1
                        > 5
                        > 05
                        > 15
                        > 25
                        > 25
                        >
                        > Regards,
                        >
                        > Arnie Rowland, MVP (SQL Server)
                        >
                        > "You cannot do a kindness too soon because you never know how soon it
                        > will be too late."
                        > -Ralph Waldo Emerson
                        >
                        >
                        >
                        >
                        > -----Original Message-----
                        > From: SQLQueriesNoCode@yahoogroups.com
                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                        > Sent: Thursday, January 06, 2011 9:08 AM
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
                        >
                        > Oh yes, that would be even better.
                        >
                        > CAST() further down the road in the process is what is causing me to
                        > need to 'scrub' the field. What am I missing?
                        >
                        >
                        > Thanks.
                        >
                        > John Warner
                        >
                        >
                        > > -----Original Message-----
                        > > From: SQLQueriesNoCode@yahoogroups.com
                        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie
                        > Rowland
                        > > Sent: Thursday, January 06, 2011 12:00 PM
                        > > To: SQLQueriesNoCode@yahoogroups.com
                        > > Subject: RE: [SQLQueriesNoCode] Fix a varchar column's data to Int
                        > >
                        > > John,
                        > >
                        > > Would it help to remove the non-integer characters and keep any
                        > > integer characters -or do you wish to just clear out the entire
                        > > value replacing with a marker?
                        > >
                        > > The sorting is not an issue, it is easily accomplished by using a
                        > > cast() in the ORDER BY clause.
                        > >
                        > >
                        > > Regards,
                        > >
                        > > Arnie Rowland, MVP (SQL Server)
                        > >
                        > > "You cannot do a kindness too soon because you never know how soon
                        > > it will be too late."
                        > > -Ralph Waldo Emerson
                        > >
                        > >
                        > >
                        > >
                        > > -----Original Message-----
                        > > From: SQLQueriesNoCode@yahoogroups.com
                        > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                        > > Sent: Thursday, January 06, 2011 4:59 AM
                        > > To: SQLQueriesNoCode@yahoogroups.com
                        > > Subject: [SQLQueriesNoCode] Fix a varchar column's data to Int
                        > >
                        > > Ok the problem. I have inherited an SQL Server 2005 database that is

                        > > tied to a website (Intranet) that is the front end for data entry.
                        > > On the screens a lot of data is scanned in via hand held barcode
                        readers.
                        > > The reader software drops the data into <input type='text' />
                        > > textboxes based on focus. But the textboxes can also have data keyed
                        > into them.
                        > > Note before we get much further I cannot alter the database and the
                        > > data entry screens at present I have limited access to the code to
                        > > repair the data validation for the problem I am about to tell you
                        > about.
                        > >
                        > >
                        > >
                        > > There is a field in the database that is a varchar(50) I think (not
                        > > in
                        >
                        > > front of it right now but sure of the varchar) that in fact is
                        > > supposed to hold only Integers. If scanning is done right with the
                        > > proper focus ect, only integers go into this field. The original
                        > > developer appears to have not considered this might not happen this
                        > > way every time. Result other non numeric data is in the column.
                        > > Again I can't actually fix the root of the problem so any solution
                        > > that involves me writing code to prevent this happening will not at
                        > > present
                        >
                        > > work -and I am not happy about this -.
                        > >
                        > >
                        > >
                        > > Ok I'm looking for a query that will let me clean this field of non
                        > > integer values as it is killing a reporting program down the line.
                        > > I worked my way though the data yesterday in 40,000+ only found
                        > > about 50
                        >
                        > > entries that were bad. I am looking to set a proc into the database
                        > > that fix this column that the users can invoke without me.
                        > >
                        > >
                        > >
                        > > I am OK with replacing non integers with zero or 999. Null won't
                        > > work for other reasons. I want the consumer of reports to be able to

                        > > spot rows where there was an issue. Columns in the table
                        > >
                        > >
                        > >
                        > > OrderNo , cnt, batch, .
                        > >
                        > >
                        > >
                        > > Cnt is the column the other two will be different for each cnt that
                        > > is
                        >
                        > > cnt = 1 in row one and cnt = 1 in row 10 (assume a sort on any other
                        > > column) will have different OrderNo batch combinations so you can
                        > > get to row one's cnt =1 without hitting row 10's.
                        > >
                        > >
                        > >
                        > >
                        > > Cnt is the column to clean. I thought about SELECT * FROM table
                        > > WHERE
                        > > ISNUMERIC(cnt) = false; and then some sort of Update.
                        > >
                        > >
                        > >
                        > > My question is how would you attack this. also note I encountered
                        > > yesterday .1 and .5 (yes decimals, but they are invalid values so
                        > > need
                        >
                        > > to be reset, I suspect they are keying errors and should be 1 and 5
                        > > respectively)?
                        > >
                        > >
                        > >
                        > > Thanks and I hope I'm more clear than mud.
                        > >
                        > >
                        > >
                        > > John Warner
                        > >
                        > >
                        > >
                        > >
                        > >
                        > > [Non-text portions of this message have been removed]
                        > >
                        > >
                        > >
                        > > ------------------------------------
                        > >
                        > > Yahoo! Groups Links
                        > >
                        > >
                        > >
                        > >
                        > >
                        > >
                        > >
                        > > Disclaimer - January 6, 2011
                        > > This email and any files transmitted with it are confidential and
                        > intended solely
                        > > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                        > > addressee you should not disseminate, distribute, copy or alter this
                        > email. Any
                        > > views or opinions presented in this email are solely those of the
                        > > author
                        > and
                        > > might not represent those of Westwood Consulting, Inc. Warning:
                        > > Although Westwood Consulting, Inc has taken reasonable precautions
                        > > to ensure no viruses are present in this email, the company cannot
                        > > accept
                        > responsibility for
                        > > any loss or damage arising from the use of this email or
                        attachments.
                        > > This disclaimer was added by Policy Patrol:
                        > > http://www.policypatrol.com/
                        > >
                        > >
                        > >
                        > >
                        > > ------------------------------------
                        > >
                        > > Yahoo! Groups Links
                        > >
                        > >
                        > >
                        >
                        >
                        >
                        > ------------------------------------
                        >
                        > Yahoo! Groups Links
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        > [Non-text portions of this message have been removed]
                        >
                        >
                        >
                        > ------------------------------------
                        >
                        > Yahoo! Groups Links
                        >
                        >
                        >



                        ------------------------------------

                        Yahoo! Groups Links
                      Your message has been successfully submitted and would be delivered to recipients shortly.