patindex and substring

Question

I have a need to pull an amount from a column (varchar 450) that contains text like the following
'NSF in Originator Account. Host account balance -108092.43. | Error<CPA_ERR>'
I have the following that pulls the amount that I need except I don't know how to eliminate the text after the 2 decimal points '. | Error<CPA_ERR>' so on my substring for length how do replace the 8000 with where the numeric ends. I hope this makes sense

select text,substring(TEXT,PATINDEX ('%[0-9-]%',TEXT),8000)
From table1

Answer 1

0

Is the second period the last period in your text string? If so you can reverse the column to get the position and use that in your substring.

answered Mar 07 '11 at 10:49 AM

Tim
31.5k ● 20 ● 30 ● 116

I sure hope so, let's say it is

Mar 07 '11 at 10:51 AM farid nia

This works if you need the - in the value, if not it is easy to remove.

DECLARE @COLUMN varchar(50)
SET @COLUMN = 'NSF -108092.43. | Error'
SELECT SUBSTRING(@COLUMN,1+LEN(@COLUMN)-CHARINDEX('-',REVERSE(@COLUMN)) ,PATINDEX('%.%',@COLUMN)+2-(LEN(@COLUMN)-CHARINDEX('-',REVERSE(@COLUMN))))

Mar 07 '11 at 11:00 AM Tim

If I had some samples of the results you get from your source table I could test this for your scenario.

Mar 07 '11 at 11:00 AM Tim

Thanks Tim

Mar 07 '11 at 11:59 AM farid nia

@Farid Nia after playing with the data a bit more, I think this will be the best solution for you. It takes into consideration having or not having a minus in the dollar amount.

DECLARE @COLUMN varchar(50) SET @COLUMN = 'GOASDOGLE 108092.43. | Error<cpaASDF_errrror' SELECT SUBSTRING(@column,PATINDEX('%[0-9-]%',@column),(LEN(@column)-PATINDEX(('%.%'),REVERSE(@COLUMN)))-PATINDEX('%[0-9-]%',@column)+1)

Mar 07 '11 at 01:40 PM Tim

(comments are locked)

Answer 2

0

If the pipe character is reliable then you could use

DECLARE @str VARCHAR(450)
SET @str = 'NSF in Originator Account. Host account balance -108092.43. | Error<CPA_ERR>'

DECLARE @FirstPoint INT
DECLARE @SecondPoint INT
DECLARE @FinalResult VARCHAR(100)

SELECT  @FirstPoint = PATINDEX('%.%', @str)
SELECT  @SecondPoint = PATINDEX('%' + CHAR(124) + '%', @str)

SELECT  @FinalResult = SUBSTRING(@str, @firstpoint + 1,
                                 ( @secondpoint - @firstpoint ) - 1)
SELECT  @FinalResult

answered Mar 07 '11 at 12:57 PM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

patindex and substring

Follow this question

Related Questions