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.

Sarath Subramanian · Accepted Answer · 2015-01-30 11:28:43Z

2

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

answered Jan 30, 2015 at 11:28

Sarath Subramanian

21.6k12 gold badges89 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Saravana Kumar · Accepted Answer · 2015-01-30 11:20:43Z

1

Try this. Used REPLACE function and then parsed.

DECLARE @string AS VARCHAR(100),  @result AS VARCHAR(100)
DECLARE @nStart AS int

SET @string = 'Start Date : 01/02/2013 50 x 156.00/MX + 207.64'
SET @result = REPLACE(@string, '/', '')
SET @nStart = CHARINDEX('x', @result) + 1
SET @result = SUBSTRING(@result, @nStart, CHARINDEX('M',@result) - @nStart)

SELECT @result

answered Jan 30, 2015 at 11:20

Saravana Kumar

3,7295 gold badges19 silver badges39 bronze badges

Collectives™ on Stack Overflow

Select part of a string between two values SQL 2008

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related