Select substring by expression rather than char or index

1

I have a log field (QuoteLog) that contains a log of the row, in a table called (aQuote). It contains data like this:

steve marked down payment received 7/15/2016
steve created quote on 6/28/2016

What I need to do is select the dates out of these strings that are preceded by 'down payment received '

I have this:

SELECT RIGHT(QuoteLog, (CHARINDEX('%down payment received',REVERSE(QuoteLog),0))-1) FROM aQuote where QuoteDownPaymentReceived = 1

but this does not work because I can't use charindex and it also does not limit the length. I know how to do this in something like C++ but would rather simply do it in the server if I can help it.

2

Perhaps this?

DECLARE @searchString VARCHAR(32) = 'down payment received';
SELECT 
	STUFF(QuoteLog, 1, NULLIF(CHARINDEX(@searchString,QuoteLog),0)+LEN(@searchString),'')
FROM 
	aQuote 
WHERE 
	QuoteDownPaymentReceived = 1
3

That is close, it cuts off the front end. I have this to try to cut off the back end to just be left with the date (looking for first empty space to know the length of the date) but have never used these functions before and I just get null responses.

DECLARE @searchString VARCHAR(32) = 'down payment received';
SELECT 
	stuff( STUFF(QuoteLog, 1, NULLIF(CHARINDEX(@searchString,QuoteLog),0)+LEN(@searchString),''), 0, charindex(' ', STUFF(QuoteLog, 1, NULLIF(CHARINDEX(@searchString,QuoteLog),0)+LEN(@searchString),''), 0), '')
FROM 
	aQuote 
WHERE 
	QuoteDownPaymentReceived = 1
4

Didn't quite get what you meant by "cuts off the front end". Are you trying to extract the date from the string, or just find all the rows where you have a date and the search string?

When I run the query I posted (with the column name replaced with a variable as shown below):

DECLARE @QuoteLog VARCHAR(256) = 'steve marked down payment received 7/15/2016';

DECLARE @searchString VARCHAR(32) = 'down payment received';
SELECT 
	STUFF(@QuoteLog, 1, NULLIF(CHARINDEX(@searchString,@QuoteLog),0)+LEN(@searchString),'')

I get the following. Isn't that what you are looking for?
7/15/2016

5

It looked initally like what I was looking for the thing is, the field does not end with a date, it ends with a new line control character. I need to presume that the date is in the middle of a bunch of text

6

Alternative to @JamesK solution:

declare @s varchar(100)=' down payment received ';

select quotelog
      ,case
          when charindex(@s,quotelog)>0
          then substring(quotelog
                        ,charindex(@s,quotelog)+len(@s)+1
                        ,charindex(' '
                                  ,replace(replace(quotelog,'\r',' '),'\n',' ')+' '
                                  ,charindex(@s,quotelog)+len(@s)+1
                                  )
                        -(charindex(@s,quotelog)+len(@s)+1)
                        )
          else ''
       end
  from aquote
 where quotedownpaymentreceived=1
   and quotelog like '%'+@s+'%'
;
7

That looks extremely close, though on most lines it still leaved on the first word of the next line. I presume there needs to be another replace but am not sure what character we need to replace

8

Use "ascii(substring(qoutelog,charposition,1))" to investigate.

9

where would I use that statement?

select ascii(substring(QuoteLog, charposition,1)) from aQuote ?

charpositionis not recognized, do you mean charindex?

10

No, I meant that you should replace the charposition with the position right after the date. That of cause might be difficult if you have many rows, and don't know which row fails.

Hang on, will see if I can find something to locate the rows with "funny" chars.

11

can you post some actual sample data that causes wrong output? Try the following example:

create table #tmp(quotelog varchar(256));
insert into #tmp values 
('steve marked down payment received 7/15/2016And then some traling text');

DECLARE @searchString VARCHAR(32) = 'down payment received';
SELECT 

	left(x,NULLIF(patindex('%[1-2][0-9][0-9][0-9]%',x),0)+3)
from
	#tmp t
	cross apply 
	( values ( STUFF(quotelog, 1, NULLIF(CHARINDEX(@searchString,quotelog	),0)+LEN(@searchString),'') ) ) s(x)
2 Likes
12

Here is a CSV of the data per my google drive. This represents the output of Bitsmed's alternative

https://drive.google.com/file/d/0B5-u80qKnJVxTUthT1RsUnNtaUU/view?usp=sharing

Edit: JamesK's worked. Thank you two so much for your help.

13

Nice :slight_smile: Remember to like @JamesK answer which solved your "challenge"