Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Return subset of results based on alpha characters

Author  Topic 

richardwaugh
Starting Member

36 Posts

Posted - 2013-03-11 : 15:34:07
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-11 : 16:24:39
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 - 2013-03-12 : 08:05:28
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-12 : 10:26:41
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
Go to Top of Page

richardwaugh
Starting Member

36 Posts

Posted - 2013-03-18 : 08:23:47
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
   

- Advertisement -