Re: how to sum a text field with commas

Tech-Archive recommends: Fix windows errors by optimizing your registry

BY DEFINITION a column has scalar values, this is not a valid value in
SQL. This violates a thing called First Normal Form (1NF) which you

I think you are confusing the query language (SQL, or in this product TSQL) with database design.

In database design yes, you would eradicate this type of structure.

SQL is querying - nothing more.

You also show a sheltered existence, really, have you never had to chop and refine data - would you honestly write a separate one off program in C when in a fraction of the time you could cut and massage the data into a structure you want and conforms to proper database design principles?

--ROGGIE--

"--CELKO--" <jcelko212@xxxxxxxxxxxxx> wrote in message news:6ab35ce4-1acc-48cb-8c6e-08bb960ccc6a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a field [sic: columns are not fields] that contains values like "1,3,0,0" and "2,0,0" and "1". I need to sum those values within the field [sic]. The field [sic] can have up to 100 numbers separated by commas. How can I do sum them? <<

BY DEFINITION a column has scalar values, this is not a valid value in
SQL. This violates a thing called First Normal Form (1NF) which you
will find in the first few chapters of a book on RDBMS.

You can find lots of stinking kludges to parse strings, but the right
answer is to design a proper schema and fire the guy who did this.

If you use a kludge, be sure to include code to raise all the errors
that a parameter in a procedure call can raise. The cowboy coders
never bother with that part ..

.

Relevant Pages

  • Re: TypeError
    ... > I note that in the code shown there are examples of building an SQL ... and that's presumably why the OP is constructing whole ... cursor.executefrom %sCustomerData;' % store) ... The bad database design has been ...
    (comp.lang.python)
  • Re: transition from programmer to developer
    ... copies of Chris Date's "Principles of Database Design" and "The SQL ... Standard" on my shelf. ... SQL standard. ... Newcomers to database design may not have heard of Chris Date. ...
    (comp.lang.java.programmer)
  • Re: How to build a procedure that returns different numbers of columns as a result based on a parame
    ... maintain and violates some of basic ideas of RDBMS. ... SQL is not an application language; it is a data retrieval language. ... to cram everything into one SQL module. ... DeMarco, Myers, etc. and the basics of structured programming. ...
    (comp.databases.ms-sqlserver)
  • Re: how to sum a text field with commas
    ... "BY DEFINITION a column has scalar values, this is not a valid value in SQL" ... Holding "1,2,3" in a column has nothing to do with DDL, DML, DCL, and transaction control. ... IT has everything to do with database design 1nf, ... I find that most one-shot jobs have the data I want on a ...
    (microsoft.public.sqlserver.programming)
  • Re: Pass Field Name Using Variable to SQL string
    ... First of all, you have a HORRIBLE database design, which violates ... important database design principles such as the "repeating group" ... I want to use a recordset with SQL to identify the current field and edit ... This syntax works perfectly in the SQL to identify the current field in the ...
    (microsoft.public.access.tablesdbdesign)