Apply different replacement on a single regular expression..

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

[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

[aimy]

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.

[aimy]

Finally I managed to do this with some painful steps..

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

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

Thank you.

[aimy]

Well, as you can see.. this step

Code: Select all

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

could be eliminated right on the 1st replacement..

[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

[aimy]

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.

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.

[ben_josephs]

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.

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: )

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.