question

farid nia avatar image
farid nia 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
selectsubstringpatindex
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
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.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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
1 Like 1 ·
I sure hope so, let's say it is
0 Likes 0 ·
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))))
0 Likes 0 ·
If I had some samples of the results you get from your source table I could test this for your scenario.
0 Likes 0 ·
Thanks Tim
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.