Position of a character in a string

[bpolunin]

Hey guys,
I am writing a sp and I need to find the position of a character in a string I get back. Any ideas?
expl: I get {FUE} .42 / {295} -.27
{FUE} .49 / {295} -.34
{FUE} .72 / {295} -.5
I need to calculate all the money after the FUE (.42+.49+.72) and then the 295's (-.27+-.34+-.5). So I will have 2 different calculations. I also need to get the FUE out and the 295 to get the description of these in another table.

Please help.:plot:

[bpolunin]

Does no one know how to do this????
Please help if anyone knows.

[inkedmn]

maybe i'm just dense, but i don't have the first idea what you're trying to do...

[_underdog]

What is an sp?

[bpolunin]

sp = stored procedure

[rlopez]

You did not specify what database you are using. This will work with SQL Server 2000.

This will break the string into its components. You can use a cursor or a temporary table to do the summation.

DECLARE @vStr AS VARCHAR(100);
DECLARE @vFirstNumber AS VARCHAR(20);
DECLARE @vSecondNumber AS VARCHAR(20);
DECLARE @vDescription1 AS VARCHAR(5);
DECLARE @vDescription2 AS VARCHAR(5);
DECLARE @vCurrentChar AS CHAR(1);


SET @vStr = '{FUE} .42 / {295} -.27';

--Get rid of blank spaces
SET @vStr = REPLACE(@vStr, ' ', '');

SET @vDescription1 = SUBSTRING(@vStr, CHARINDEX('{', @vStr) + 1, CHARINDEX('}', @vStr) - 2);
SET @vDescription2 = SUBSTRING(@vStr,
CHARINDEX('{', @vStr, CHARINDEX('}', @vStr)) + 1,
CHARINDEX('}', @vStr, CHARINDEX('}', @vStr)) - 2);
-- Go to first number
SET @vStr = SUBSTRING(@vStr, CHARINDEX('}', @vStr) + 1, LEN(@vStr) - CHARINDEX('}', @vStr));

SET @vCurrentChar = SUBSTRING(@vStr, 1, 1);
SET @vFirstNumber = '';

WHILE @vCurrentChar <> '/'
BEGIN
SET @vFirstNumber = @vFirstNumber + @vCurrentChar
SET @vStr = SUBSTRING(@vStr, 2, LEN(@vStr) - 1);
SET @vCurrentChar = SUBSTRING(@vStr, 1, 1);
END

-- Go to second number
SET @vSecondNumber = SUBSTRING(@vStr, CHARINDEX('}', @vStr) + 1, LEN(@vStr) - CHARINDEX('}', @vStr));

PRINT 'First Description= ' + @vDescription1 + ' Number=' + @vFirstNumber;
PRINT 'Second Description= ' + @vDescription2 + ' Number=' + @vSecondNumber;

[bpolunin]

Thank you so much for helping me on this. This works great. But now I am running into problem where if I have only one thing to calculate or more then 2. How would I handle that?
Ex: {SUR} 2 / {FUE} .49 / {295} -.25 or
{FUE} 1.56

[rlopez]

If you don't know all the possible values for the description then I would use a temporary table to store the descriptions and the values. Something like

Table #Temp (Description VARCHAR(20), Total NUMERIC(8,2))

First get the descriptions from the first string and numbers. Insert them into the temporary table. For the rest of the rows do the same but check and see if the description is already in the table. If it is then add to the total otherwise insert it.

So form your example
{SUR} 2 / {FUE} .49 / {295} -.25 or
{FUE} 1.56

The table would look like this after you process the first row.

Description Total
SUR 2
FUE 0.49
295 -0.25

One the next row you you would get the description FUE check if it exists. It does so get the number and do and update query.

Description Total
SUR 2
FUE 2.05
295 -0.25


Let me know if this helps.

[bpolunin]

OK, That makes sense. Thank you very much for your help.