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

avatar image

farid nia
44 1 1 3

(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

avatar image

Tim
40.4k 39 84 166

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

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

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(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.

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:

x148
x18
x6

asked: Mar 07, 2011 at 10:46 AM

Seen: 4096 times

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

Copyright 2016 Redgate Software. Privacy Policy