Community

forums.textpad.com
https://forums.textpad.com/

Apply different replacement on a single regular expression..

https://forums.textpad.com/viewtopic.php?t=9590

Page 1 of 1

Apply different replacement on a single regular expression..

Posted: Mon Aug 25, 2008 7:47 am
by aimy
I have this set of text..

Code: Select all

ADSCARD              ADSLAM_IDENT_CODE CARD_TYPE END_PORT_NUM END_VCI_NUM START_PORT_NUM START_VCI_NUM

ADSLAM               ADSLAM_IDENT_CODE BUILD_IDENT_CODE CARD_TYPE CONTRACTOR_NUM DSLAM_ADDR

ADSLBAR              BUILD_IDENT_CODE CODESYS_ID

ADSLMPR              ADSLAM_IDENT_CODE ADSL_PAIR_NUM CABINET_IDENT_CODE CARD_TYPE DPSDF_IDENT_CODE

BASE_TRAN            BTS_ADDR_LINE1 BTS_IDENT_CODE BTS_NAME COST_CENTER_CODE EXCHANGE_IDENT_CODE
p/s: Each table and its respective columns appear on one single line


The 1st part is the table name and the rest are their respective columns.

My objective is to made a replacement in such a way..

select source, max(column_1), max(column_2),..... max(column_n)
from table_name group by source;


I manage to select each line using this RE:

Code: Select all

(^\<[[:graph:]]{1,20}\>)([[:space:]]{1,20}\<[[:graph:]]{1,50}\>){1,10}
But my problem is.. How do I tag the expression so that I can apply max(column_name) to each column that follows the table name?

Thank you very much.

Posted: Mon Aug 25, 2008 3:44 pm
by ben_josephs
You can't do this in a single step in TextPad.

Here's one way to do it in (stretching a point) two steps:
Find what: (^[^ ]+) +([^ ]+( +[^ ]+)*)
Replace with: select source \2\nfrom \1 group by source;

[X] Regular expression

Replace All
Then
Find what: ^(select [^,]+) ([^,]+)
Replace with: \1, max(\2)

[X] Regular expression

Replace All -- do this repeatedly until it beeps
These assume you are using Posix regular expression syntax:
Configure | Preferences | Editor

[X] Use POSIX regular expression syntax

Posted: Tue Aug 26, 2008 1:13 am
by aimy
ben_josephs wrote:You can't do this in a single step in TextPad.

Here's one way to do it in (stretching a point) two steps:
Find what: (^[^ ]+) +([^ ]+( +[^ ]+)*)
Replace with: select source \2\nfrom \1 group by source;

[X] Regular expression

Replace All
Then
Find what: ^(select [^,]+) ([^,]+)
Replace with: \1, max(\2)

[X] Regular expression

Replace All -- do this repeatedly until it beeps
These assume you are using Posix regular expression syntax:
Configure | Preferences | Editor

[X] Use POSIX regular expression syntax
Thanks ben..

But that partially worked.

Let's take the 1st table ADSCARD for example...

The first replacement will yield to:

Code: Select all

select source ADSLAM_IDENT_CODE CARD_TYPE END_PORT_NUM END_VCI_NUM START_PORT_NUM START_VCI_NUM
from ADSCARD group by source;
That looks fine..

But then, the 2nd replacement only max the last column:

Code: Select all

select source ADSLAM_IDENT_CODE CARD_TYPE END_PORT_NUM END_VCI_NUM START_PORT_NUM, max(START_VCI_NUM)
from ADSCARD group by source;
For this particular example, what I want to achieve is actually this:

Code: Select all

select source, max(ADSLAM_IDENT_CODE), max(CARD_TYPE), max(END_PORT_NUM), max(END_VCI_NUM), max(START_PORT_NUM), max(START_VCI_NUM)
from ADSCARD group by source;
Can I do that??

Or is there any way that I can replace all occurences of \<[[:graph:]]{1,50}\> but I want to exclude these keywords: select, source, from, group, by. So that I can still use your first RE replacement, then I extend it.

Thank you very much.

Posted: Tue Aug 26, 2008 2:33 am
by aimy
Finally I managed to do this with some painful steps.. :roll:

Code: Select all

(^\<[[:graph:]]{1,20}\>)([[:space:]]{1,20}[[:print:]]{1,150}) --> select source, \2 from @\1 group by source;

Code: Select all

\<select\>|\<source\>|\<group\>|\<by\>|\<from\> --> @&

Code: Select all

([^@^])(\<[[:graph:]]{1,50}\>) --> max(length(\2)) "\2",

Code: Select all

(^\<[[:graph:]]{1,20}\>)([[:space:]]{1,20}[[:print:]]{1,150})

Code: Select all

remove @

Code: Select all

, from --> ' 'from
Finally, I run the Macro that I made it myself to remove the double spaces between the table name and the column name. 8)

Very interesting working with Text Pad's Regular Expression.. :lol:

Should anyone have a more simpler way of doing this, you are most welcomed..

Thank you.

Posted: Tue Aug 26, 2008 4:14 am
by aimy
Well, as you can see.. this step

Code: Select all

\<select\>|\<source\>|\<group\>|\<by\>|\<from\> --> @&
could be eliminated right on the 1st replacement.. :P

Posted: Tue Aug 26, 2008 9:42 am
by ben_josephs
My suggestion does work. Please read it again more carefully, in particular, the bit in italics (and now emboldened) in the second replacement:
Find what: ^(select [^,]+) ([^,]+)
Replace with: \1, max(\2)

[X] Regular expression

Replace All -- do this repeatedly until it beeps

Posted: Wed Aug 27, 2008 6:20 am
by aimy
ben_josephs wrote:My suggestion does work. Please read it again more carefully, in particular, the bit in italics (and now emboldened) in the second replacement:
Find what: ^(select [^,]+) ([^,]+)
Replace with: \1, max(\2)

[X] Regular expression

Replace All -- do this repeatedly until it beeps
Wow!!! It's amazing!!!

Thank you so much Ben.. :)

I am so sorry for not reading your post carefully. But yeah.. you should have emphasized in earlier anyway.. Coz all this while I never knew that I can do Replace All indefinitely like that. I thought Replace All also means Replace All At Once only!! 8)

So, thanks again for your help. But it will be more appreciated if you could explain the syntax. :D

Last but not least, could you please answer me about to find regular expression with specific text exclusion? And I need more explanation what does actually NEGATED EXPRESSION in TextPad is actually mean.

Thank you.

Posted: Mon Sep 01, 2008 6:39 pm
by ben_josephs
aimy wrote:I never knew that I can do Replace All indefinitely like that. I thought Replace All also means Replace All At Once only!
When you Replace All, TextPad repeatedly searches for the text that matches the search expression and replaces it as specified by the replacement expression: search, replace, search, replace, and so on. Each search starts from the end of the previous replacement. In this case, that implies that once the initial part of the search expression, select, has been used for a match, it's not available for another match on the same line. So only one match on any one line can be recognised.
aimy wrote:But it will be more appreciated if you could explain the syntax.
^(select [^,]+) ([^,]+) matches

Code: Select all

^             the beginning of a line

(             start of captured text number 1
  select        the literal text: select 
  [^,]+         any non-empty text not containing commas (see below)
)             end of captured text number 1

(             start of captured text number 2
  [^,]+         any non-empty text not containing commas (see below)
)             end of captured text number 2
where [^,]+ matches:

Code: Select all

[^,]          any character except newline or comma
+             ... any non-zero number of times
We stick the matched text back together, adjusted as required:

\1, max(\2) is composed of:

Code: Select all

\1        captured text number 1
, max(    the literal text: , max(
\2        captured text number 2
)         the literal text: )
aimy wrote:could you please answer me about to find regular expression with specific text exclusion? And I need more explanation what does actually NEGATED EXPRESSION in TextPad is actually mean.
There is no general negation operator. There are only negated class expressions. From the help:
If the first character of a class expression is the circumflex (^), the expression matches any character not in the class. For example [^AB^] matches any character except A, B and the circumflex itself.
The best you can do in TextPad is negate a single character; you can specify that a match consists of exactly one character and that it isn't any of a specified set of characters. More modern tools have more powerful regular expression recognisers and can do better than this; for example, WildEdit (http://www.textpad.com/products/wildedit/).

Look in TextPad's help under
Reference Information | Regular Expressions,
Reference Information | Replacement Expressions and
How to... | Find and Replace Text | Use Regular Expressions.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited