I think i *will* loose my mind!
Thanks both of you for your help.

You see, the problem is that i can have a few flags at the end of the string
which mean somehing. e.g. M means missing, T means Trace, E means estimated,
etc.Information that i should have. If i split each column into two ,
well....possible but a lot of work and too much overhead since most
measurements are "clean".

That's the reason i kept the data in their original format.

Now if i dont convert to strings how the heck am i going to create the sql
string to send to the database with things like:
temperature between so and so, humidity bigger than 50, blah blah blah....
The string is created by an interface in VB where the user scrolls down
various controls and selects things. But how would you enter the bounds of
temperature in a textbox since its not stored as an integer AND it might
have a stupid letter at the end of its value!!!

I could do the coding after the results are returned.
I think My Biggest problem is string comparisons!!! If the user wants a
temperature between -7 and +15 how well and reliably can I create code to
compare "-00007" and "000015".

Just some thoughts from my brainstorming....


"Andy Williams" <f_u_b_a_r_1_1_1_9@y_a_h_o_o_._c_o_m> a écrit dans le
message de news: CIwgd.1071$wN4.303@newssvr16.news.prodigy.com...
[quoted text, click to view]

steve,

SELECT * FROM Table1
WHERE CAST(CASE RIGHT(Measurement, 1) WHEN 'M' THEN NULL ELSE Measurement
END AS decimal) * .1
BETWEEN -2.5 AND 12.4

-Andy

[quoted text, click to view]

Why is the data stored in this format? If these are numeric measurements you
will be much better off storing them with a numeric datatype. Storing
numbers as strings will just make your queries difficult and slow and also
make it hard to maintain any data integrity. Fix the design and convert the
data to numeric form is my advice.

If you've no other choice you could try something like this:

SELECT col
FROM Measurements
WHERE CAST(LEFT(col,6) AS INTEGER) > -2.5
AND CAST(LEFT(col,6) AS INTEGER) < 12.4

--
David Portas
SQL Server MVP
--

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
[quoted text, click to view]

And store the missing values as NULL.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Create a new table, then use the query I gave you to INSERT into it:

INSERT INTO NewTable (...)
SELECT ...
FROM OldTable

But if the data is changing, maintaining two copies of it is hard work and
unnecessary. The usual practice is to validate and transform data once and
then maintain it in a consistent, strongly-typed relational format in the
database. If you validate the data properly once then you won't need the
original format again. If you don't then you pay the price every time you
query the table.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

Then you have a non-atomic column, which is a violation of the most
fundamental relational design principles. This information belongs in a
separate column.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]

Yep, it's lousy... So why waste time on it when you could just redesign the
table properly :-)

--
David Portas
SQL Server MVP
--