SQL- How to SUM varchar with text in some fields

I have a lookup table that we have modified the data in recently. I want to SUM up the values that are whole number and decimal number (.50, .25, 1, 2,) so on. The problem is the old data has text and there is too much to get rid of and I need to keep it.  Can I use the SUM(CAST(LOOKUP2 AS INT)) with another statement that will allow the report to process without the error coming up about the old data?
JCCAROLINAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
Try this

select sum(cast(lookup2 as float)) from YourTable

Raj
0
JCCAROLINAAuthor Commented:
When I do this I get the following error:
Error in compliling SQL expression: DB connector error 42000 ODBC SQL server. Error converting data type varchar to float. DB vendor code 8114
0
Rajkumar GsSoftware EngineerCommented:
select sum(cast(ltrim(rtrim(lookup2)) as float)) from YourTable

I think, some of your may not be integer. anyway if there is any space causing this error, this may fix.

Raj
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Aneesh RetnakaranDatabase AdministratorCommented:
select sum(cast(lookup2 as float)) from YourTable
where isnumeric(lookup2) = 1
0
JCCAROLINAAuthor Commented:
same error as before. one example of the old text is:  03)1 hour.  Now it is just 1 for the one hour.  I am using this command in Crystal version 11.  I am by far not a report builder in Crystal, but I can ususally get out what I need if it is simple.
0
JCCAROLINAAuthor Commented:
maybe I am typing this wrong. If I am in the expression builder, and my table location is "tasks.lookup2" what is the "exact" line(s) that I would type?
Thanks!
0
Rajkumar GsSoftware EngineerCommented:
First you need to remove the texts like 'hour' or anything alse from the table

Check the non-numeric data in your table using this query
select distinct(lookup2) from Yourtable
      where isnumeric(lookup2) = 0

Then remove it from the table, using the query like this
eg:- to remove the string 'hour', execute this. Same for other strings (replace 'hour' with required ones)

update Yourtable set lookup2 = replace(lookup2, 'hour', '')

Once this is done, this query will work

select sum(cast(ltrim(rtrim(lookup2)) as float)) from YourTable

Hope this helps
Raj
0
JCCAROLINAAuthor Commented:
I guess I am just missing something, I tried the script to find the text from the lookup2 column and I ran it from the SQL 2005 studio manager. If I run it like you have it, it says "invaild column" If I put in the entire DB path "dbo.tasks.lookup2" is says the multi-part identifier could not be bound. Should it be this complicated?
0
Rajkumar GsSoftware EngineerCommented:
post the full query, you tried.

Raj
0
mlmccCommented:
You can do it in the report by using a formula to convert the numeric fields to numbers and the others to 0

If IsNumeric({YourField}) then
   Val({YourField})
else
    0

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
The problem with using Val (), as mlmcc suggested, is that it takes the characters up to the first non-numeric character and converts those to a number.  Using your example of "03)1 hour", it would take the "03" and convert that to 3, instead of pulling the 1 in "1 hour".

 If you can define which characters in these strings are the numbers that you want to convert, you can probably do this (either in SQL or in CR).  If you can't come up with some consistent rules, then you won't be able to get reliable results.

 Using your example of "03)1 hour", you could say that you should discard everything through the ")" and the numeric characters right after that (in this case, "1") are your number.  But do the other text values follow the same format?

 James
0
James0628Commented:
Thought of this just after I posted.

 For the record, even if Val would work, IsNumeric (as in mlmcc's formula) won't work, because it looks for fully numeric strings.  It will be false on strings like "03)1 hour".  So, you'd just use Val ({field}), without the IsNumeric test.  But, like I said, I don't think Val will give you what you want anyway.

 James
0
mlmccCommented:
My attempt assumed you wanted only numeric fields added.  However if there is data like 03) 1 hour and you need 1 added then you will need to determine what data needs to be editted and try to account for each case.

mlmcc
0
JCCAROLINAAuthor Commented:
ok so it sounds like it would be easier if the table in SQL all contained only numeric values. How about this. is there a way from SQL to do a find and replace like; find all 02) 30 min (and) change to .50 and so on?  If so, I will just update the tables. I need the data in the tables but it can change to the new format.
Thanks.
0
James0628Commented:
It's possible, if you can come up with specific patterns to look for.  That may be the tricky part.

 James
0
JCCAROLINAAuthor Commented:
I have converted all fields to a value of .25 .50 1 2 3 4 5 6 7 8 and so on. Now I have tried both the statements given by Raj and aneeshattingal: but in both case in Crystal Report formula editor I get the following message; "the ) is missing" and it always highlights the word "cast"  Any ideas? It does seem to be getting closer.
Thanks
JC
0
James0628Commented:
CAST is a SQL function, not CR.

 In CR you can use Val ({field}), as mlmcc suggested earlier, or ToNumber ({field}).  You can check the CR Help for the details, but the basic difference is that Val will convert any leading numeric characters to a number and not give you an error if the string is not actually numeric (you just get 0), while ToNumber (aka CDbl) will only work on strings that are numeric, and will give you an error on anything else.

 If you're going to use ToNumber, it's a good idea to use IsNumeric first, to make sure the string is numeric.

 If the field could ever be null, you should check for that first using IsNull.  For example:

if not IsNull ({field}) then
  Val ({field})


 James
0
JCCAROLINAAuthor Commented:
OK I can successfully put in the Val or ToNumber statement and not get an error, but I dont get the correct count. Here is a full example, sorry I am making this so hard but I am a hardware person and not a SQL/Crystal guy!
My table is "Tasks" my column is "Lookup2" Lookup2 is a varchar field.
My currunt values are 0,0,.25,.25,.25,0,0,3,0,2,2,1,2,0,1,1,0,.25,0 for my lookup2 column for user1 in the report.  My statement is
if not IsNull ({Tasks.lookup2}) then
Val ({Tasks.lookup2})
My answer is always the LAST value in the column and not the sum of the numbers. I have tried with the statement mlmmc suggested and with ToNumber statement.
My answer should be 13.  What could I be doing wrong?
I created this statement in Crystal Reports x+1 field.

Thanks so much!
JC
0
mlmccCommented:
Where did you create it?

Where is it in the report?

mlmcc
0
James0628Commented:
Since you accepted a solution, I guess you've figured this out, but it sounded like you just needed to do a summary on that formula.

 James
0
JCCAROLINAAuthor Commented:
yes after I put in the statement I still did not have it in the correct "group" to sum up the fields. I don't work with Crystal or SQL in dept at all so i was stumbling my way through. With all the help everyone gave me I was able to get the results I wanted. Thanks so much.
JC
0
James0628Commented:
OK.  Glad you were able to figure out that last problem.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.