Capture data left of '%'

[rose4567]

I am using Reporting Services 2005.

I need to calculate a "Payment Discount" by extracting information from a text field. The values in the field look like:

Net 30
1% Net 30
20% Net 60

If a % exists in the field I need to capture everything to the left of the % sign since the percentage can very in length. I tried the following in the SQL Data tab as part of a simple select statement. The syntax below is from MSDN but instead of returning the number(s) left of %, it returns "0" (because I specified Coalesce). Without Coalesce it returns nothing.

SELECT COALESCE(SUBSTRING(PAYMENT_TERMS_DESC, 0, CHARINDEX('%', PAYMENT_TERMS_DESC) -1),0) AS Expr1
FROM PO_HEADER

Any tips or suggestions would be greatly appreciated to get this running in SQL data view. If there's a better way to do this, like via an expression within the Layout view, I am open to suggestions.


Thank you!

 

[RiverGuy]

Here's an example. Run this in a query editor window:

DECLARE @T TABLE(Col1 VARCHAR(100))

INSERT INTO @T SELECT 'Net 30'
INSERT INTO @T SELECT  '1% Net 30'
INSERT INTO @T SELECT '20% Net 60'

SELECT 
CASE
	WHEN CHARINDEX('%', Col1, 1)  > 0 THEN CONVERT(DECIMAL(18,2), LEFT(Col1, CHARINDEX('%', Col1, 1) - 1))
	ELSE 0.0
END
FROM @T

 

[rose4567]

Works Great! Thank you so much!

Here's the snippet without declare and output as whole number instead of decimal.
_____________________________________________________
SELECT
Case when charindex('%',PAYMENT_TERMS_DESC,1) > 0 THEN CONVERT (CHAR(18), LEFT (PAYMENT_TERMS_DESC, CHARINDEX('%',PAYMENT_TERMS_DESC, 1) -1))
Else 0 END
FROM PO_HEADER