asked
patindex and substring
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 '` 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 '` 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
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.
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))))
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' 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
