SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Return subset of results based on alpha characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

richardwaugh
Starting Member

36 Posts

Posted - 03/11/2013 :  15:34:07  Show Profile  Reply with Quote
Hello,

I have a form that saves each indivual field into our database. I am being asked to pull results from the database and tally the totals. The issue is that on the form the amount columns are text fields. As such, there are workers who are enter things like "$100 weekly" or "$25 x 4 cards", etc...

Is there a way in SQL 2008 that I can easily pull the data up until there is a character? I found a way of doing this by stopping when there is a space (but the issue is that some workers are entering $ 100) but nothing where I can stop when I see an "a", "b", "c" ...

Thanks,

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/11/2013 :  16:24:39  Show Profile  Reply with Quote
Just as a general design thing the only thing you can only use user-entered text for reporting the exact value back. Things like comments and notes, or entering their names. Anything that needs to be grouped or summed needs to be controlled in some way on the front end and stored in typed database columns. for instance $25 x 4 cards should be 3 fields (amount, quantity, item). Idealy item would be a pick list of possible items or at least item type like office expenses. Then a free text field could be used for entering "cards".

Just out of curiosity how will you tally "$100 weekly", "$300 monthly", and "$25 x 4 cards"?

But to answer your question you'll need to post some examples of what results you want based on specific example values. It would help if you posted your code that works for spaces too.

Be One with the Optimizer
TG
Go to Top of Page

richardwaugh
Starting Member

36 Posts

Posted - 03/12/2013 :  08:05:28  Show Profile  Reply with Quote
Hello TG,

The problem is that we have no control over the front end coding at all (actually, we technically do as I have no qualms about opening up the developer brain in me and fixing the issue that way...but the software is copyrighted). On the form we actually have an amount field and then a separate field for a description. However...

The idea of having three fields would normally work except the third would have to be a text box as we would never be able to capture everything in that drop down list.

We have a field that has a drop down that has "Weekly" and "Monthly" in. So you can see that having $100 weekly in the AMOUNT field is well...I'll refrain.

Basically I'd like to weed everything out that is alpha and just keep the numeric portion. Once I have this I can then convert it to a decimal and then tally up the totals. Each worker can have multiple forms that contain the information needed. So I basically just need to tally up the totals for each worker. If a worker has 3 forms then I need to tally up the total of 3 forms. Form 1 has $200, form 2 has $50 gas card and form 3 has %125. I need to total 200+50+125...which of course doesn't work. I can't convert $50 gas card to a decimal.

Hope this helps.

Thanks,
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/12/2013 :  10:26:41  Show Profile  Reply with Quote
see if this is what you're looking for - be sure to read through to the end because there are references to other links as well


Be One with the Optimizer
TG

Edited by - TG on 03/12/2013 10:29:41
Go to Top of Page

richardwaugh
Starting Member

36 Posts

Posted - 03/18/2013 :  08:23:47  Show Profile  Reply with Quote
Thanks TG! Although the posts didn't help 100% they did provide me with right things needed to figure this out. I used a combination of what you linked me to as well as a function that was already existing in the database to pull out only the numeric values. Works like a charm now.

Thanks again!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000