x

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<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
more ▼

asked Mar 07, 2011 at 10:46 AM in Default

farid nia gravatar image

farid nia
44 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
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.
more ▼

answered Mar 07, 2011 at 10:49 AM

Tim gravatar image

Tim
36.4k 36 41 139

I sure hope so, let's say it is
Mar 07, 2011 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, 2011 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, 2011 at 11:00 AM Tim
Thanks Tim
Mar 07, 2011 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, 2011 at 01:40 PM Tim
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Mar 07, 2011 at 12:57 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x108
x12
x4

asked: Mar 07, 2011 at 10:46 AM

Seen: 3082 times

Last Updated: Mar 07, 2011 at 12:39 PM