Select part of a string between two values SQL 2008

Question

I'm attempting to select a part of a strint between 2 values, i've managed to get it working to about 90% but then get an error -

SUBSTRING(TranText, CHARINDEX('x', TranText) + 1, LEN(TranText) - CHARINDEX('x', TranText) - CHARINDEX('/', REVERSE(TranText)))

The field it is querying is like so

Start Date : 01/02/2013 50 x 156.00/MX + 207.64

with the desired result being

156.00

Now I think the issue is because sometimes the X can have a space before or after it, or no space at all. It gets through about 114,000 rows before throwing

Invalid length parameter passed to the LEFT or SUBSTRING function.

But am struggling to resolve.

Answer 1

The main root cause could be because LEN(@QRY) - CHARINDEX('x', @QRY)-CHARINDEX('/', REVERSE(@QRY)) is less than zero ie, negative value. This in turn will throw error in SUBSTRING since the minimum index for SUBSTRING is zero. Therefore we check that condition in a case statement.

SELECT CASE WHEN LEN(TranText) - CHARINDEX('x',TranText)-CHARINDEX('/', REVERSE(TranText)) >= 0 THEN
SUBSTRING(TranText, CHARINDEX('x', TranText) + 1, LEN(TranText) - CHARINDEX('x', TranText) - CHARINDEX('/', REVERSE(TranText)))
ELSE NULL END
FROM YOURTABLE