parsing values in SQL w/ delimiter

[Homerboy]

say my return value on a query is "Amount: $100.00" for "SALE_AMT" column.

But I only want the "100.00"
I KNOW there has to be an easy function for this in the query, but for the life of me I can not figure it out.

 

[brandonb]

Substring(sales_amt, 9)

 

[Homerboy]

SUBSTRING(ACTIVE.NOTE, CHARINDEX('$', ACTIVE.NOTE) + 1, LEN(ACTIVE.NOTE)) AS Test


what a pain the ass. I can't believe they don't have something like RPARSE(ACTIVE.NOTE.'my choice of characeters>) built in.

 

[Homerboy]

Substring(sales_amt, 9)

I thought of that, but sadly I left out a bit of info.

"Amount?: $100.00" is a fixed width value.
So when its $100.00 there are 10 spaces between the : and the $. If it's $1000.00 there are 9 spaces...

 

[WannaFly]

Similar to your other thread if it's always "AMOUNT: " then use replace. If you must use 3 replaces, one for "AMOUNT:" one for "$" and one for all spaces.

 

[Homerboy]

Similar to your other thread if it's always "AMOUNT: " then use replace. If you must use 3 replaces, one for "AMOUNT:" one for "$" and one for all spaces.

Oh I like that solution too! sneaky.:ninja:

As I get more and more into SQL and more elaborate reports/queries, that is one thing I like about it; there's always a dozen ways to do 1 thing... each better than the next.

 

[WannaFly]

Oh, and similar to your RPARSE that you mentioned would be the RIGHT command:
http://msdn.microsoft.com/en-us/library/ms177532.aspx
But that wont help you since the number of chars could change.

 

[Homerboy]

yeap. I'm familiar with "RIGHT", and have used it on a few occasions.
Thanks.

 

[beginner99]

SUBSTRING(ACTIVE.NOTE, CHARINDEX('$', ACTIVE.NOTE) + 1, LEN(ACTIVE.NOTE)) AS Test


what a pain the ass. I can't believe they don't have something like RPARSE(ACTIVE.NOTE.'my choice of characeters>) built in.

Or one could say its retarded to store "Amount?: $100.00". Not your fault but the guy that came up with this idea sure didn't understand what a relational database is.
It's like driving your car into a river and then wonder why it doesn't float.
Excel freaks are a PITA.

 

[Homerboy]

Actually this has nothing to do with Excel at all.

 

[Emulex]

regexp all but . and - and 0-9

 

[ForumMaster]

Similar to your other thread if it's always "AMOUNT: " then use replace. If you must use 3 replaces, one for "AMOUNT:" one for "$" and one for all spaces.

A better way is this:

substring is a functions that requires as input:
that column
from where to begin
where to end

if you use 3 replaces each time on the column, that's 3 additional function calls.
it adds overhead.

the preferred method is this: using instr (a function that returns the position of a string in another string). it requires as input a column and the string to find.


an example:

substr(active.note,instr(active.note,'$')+1)

that's it. you don't have to provide the third parameter for substr if you want the rest of the string (length of column).