Another RegEx question!

General
1

I have a need to find the first occurrence of a string and am having some real problems in finding the right RegEx pattern to give me that.

I have (as an example) an SQL statement like

create view accounttransactions (acctid, dtposted, acyear, acmonth, trntype, payee, debit, credit, postto) as select trans.acctid, trans.dtposted, cast(strftime('%y', trans.dtposted) as integer), cast(strftime('%m', trans.dtposted) as integer), trans.trntype, trans.payee, case when trans.trnamt < 0 then cast(-trans.trnamt as double) else cast(0.0 as double) end, case when trans.trnamt > 0 then cast(trans.trnamt as double) else cast(0.0 as double) end, postacs.acname from trans inner join postacs on ( postacs.mkey = trans.secac );

I want to find the first occurrence of " as " but everything I have tried gets me all the " as ", like cast xx as etc.

For further information I actually want to replace this first ‘as’ with ‘as’ + endofline to get this:

create view accounttransactions (acctid, dtposted, acyear, acmonth, trntype, payee, debit, credit, postto) as select trans.acctid, trans.dtposted, cast(strftime('%y', trans.dtposted) as integer), cast(strftime('%m', trans.dtposted) as integer), trans.trntype, trans.payee, case when trans.trnamt < 0 then cast(-trans.trnamt as double) else cast(0.0 as double) end, case when trans.trnamt > 0 then cast(trans.trnamt as double) else cast(0.0 as double) end, postacs.acname from trans inner join postacs on ( postacs.mkey = trans.secac );

Can someone help me, please?

Simon.

2

devils advocate…

CREATE TABLE myTABLE  SELECT XYA as newfield,  'from as test' as sometext

what I suspect you want is

CREATE TABLE myTABLE  SELECT XYA as 
newfield,  'from as test' as 
sometext

and NOT this

CREATE TABLE myTABLE  SELECT XYA as 
newfield,  'from as
test' as 
sometext
3

turn greedy off
that way it stops when it hits the first pattern that matches - instead of the longest one
then a simple pattern like “.\sas\s*” finds “create view accounttransactions (acctid, dtposted, acyear, acmonth, trntype, payee, debit, credit, postto) as”

4

I have an idea where Simon is going, and I’ll bet he doesn’t want just the “first”, but all of them… EXCEPT the ones inside of quotes

5

Thats not what he asked for

6

What patterns have you tried?

7

Just seen the responses and I have to go out now for a few hours so cannot answer directly.

Will do so tomorrow as it is now 6pm here in the UK.

Thank you for your interest, will answer and give more details tomorrow.

Simon.

8

Ok, so to be a little more precise and explain what I am looking for,

I have an SQL statement like:

create view accounttransactions (acctid, dtposted, acyear, acmonth, trntype, payee, debit, credit, postto) as select trans.acctid, trans.dtposted, cast(strftime('%y', trans.dtposted) as integer), cast(strftime('%m', trans.dtposted) as integer), trans.trntype, trans.payee, case when trans.trnamt < 0 then cast(-trans.trnamt as double) else cast(0.0 as double) end, case when trans.trnamt > 0 then cast(trans.trnamt as double) else cast(0.0 as double) end, postacs.acname from trans inner join postacs on ( postacs.mkey = trans.secac );

I would like to replace the FIRST ‘as’ so it looks like this:

create view accounttransactions (acctid, dtposted, acyear, acmonth, trntype, payee, debit, credit, postto) as select trans.acctid, trans.dtposted, cast(strftime('%y', trans.dtposted) as integer), cast(strftime('%m', trans.dtposted) as integer), trans.trntype, trans.payee, case when trans.trnamt < 0 then cast(-trans.trnamt as double) else cast(0.0 as double) end, case when trans.trnamt > 0 then cast(trans.trnamt as double) else cast(0.0 as double) end, postacs.acname from trans inner join postacs on ( postacs.mkey = trans.secac );
But my RegEx pattern is returning:

create view accounttransactions (acctid, dtposted, acyear, acmonth, trntype, payee, debit, credit, postto) as select trans.acctid, trans.dtposted, cast(strftime('%y', trans.dtposted) as integer), cast(strftime('%m', trans.dtposted) as integer), trans.trntype, trans.payee, case when trans.trnamt < 0 then cast(-trans.trnamt as double) else cast(0.0 as double) end, case when trans.trnamt > 0 then cast(trans.trnamt as double) else cast(0.0 as double) end, postacs.acname from trans inner join postacs on ( postacs.mkey = trans.secac );
I have tried a number of different RegEx patterns, none of which give me what I need.

To be precise, my question is this:

What RegEx pattern do I need to find the FIRST (and ONLY the first) occurrence of a string (in this case ‘as’)?

All the RegEx documentation that I have read talks about zero or more occurrences, 1 or more occurrences or a number. I used ‘{1,1}’ to attempt to get 1 occurrence but I get 7 occurrences returned. My RegEx is “(as\b){1,1}”. I can’t seem to get the pattern to stop after the first occurrence.

Whilst this is a specific question about my current issue I would like to know how to get just the first occurrence of any string I send to the engine so any answer should be based on that assumption.

I hope this is more helpful.

Thanks all.

Simon.

9

a) what is the difference between the 2 regex statements?
b) the number of occurrences doesn’t matter, you only need to do one replacement and not all in your code
c) how do you make the SQL? Is it generated somehow? Can you generate it in a way that makes the regex less hacky? For instance, in my app I cobble the SQL together with a placeholder like “xxx”.

10

[quote=337908:@Beatrix Willius]a) what is the difference between the 2 regex statements?
b) the number of occurrences doesn’t matter, you only need to do one replacement and not all in your code
c) how do you make the SQL? Is it generated somehow? Can you generate it in a way that makes the regex less hacky? For instance, in my app I cobble the SQL together with a placeholder like “xxx”.[/quote]
a) I am not sure what you mean here. Any RegEx pattern that I use returns zero or all occurrences of ‘as’.
b) I am using the search and replace in the RegEx engine and I only want the first occurrence to be replaced. At the moment it is none or all occurrences of ‘as’.
c) The SQL is extracted from the SQLite database so I do not have an opportunity to ‘massage’ it.

I think I am actually looking for the RegEx pattern that replicates the Replace statement in Xojo, but all the patterns I am using act like the ReplaceAll statement!

11

Are you using RegExOptions to override any default values? In particular, could you be setting ReplaceAllMatches to true anywhere? You could also try explicitly setting it to False. I believe this is the crux of the difference between Replace and ReplaceAll behavior.

See ReplaceAllMatches in RegExOptions

13

[quote=337944:@Douglas Handy]Are you using RegExOptions to override any default values? In particular, could you be setting ReplaceAllMatches to true anywhere? You could also try explicitly setting it to False. I believe this is the crux of the difference between Replace and ReplaceAll behavior.

See ReplaceAllMatches in RegExOptions [/quote]
Yes, I was. So my immediate problem is answered, thank you.