find replace help with tabs

[cignaj]

Greetings,

I have a line that appears numerous times in multiple stored procedures and I work like to find a way to duplicate it with some minor changes. I have not been able to get the regular expression to actually find the line consistently because I am relatively new with using it and unsure how exactly to use the syntax for it.

The line I need it to find, copy and modify is ...

'v' + convert(varchar, A.RowNumber) as [PersVeh!10!id],

There should be 4 tabs between the ')' and the word as (for formatting purposes).

I need the line to remain the same but also add another line on the replace ...

'v' + convert(varchar, A.RowNumber) as [PersVeh!10!id],
A.RowNumber as [PersVeh!10!id],

On the replace I need to keep the 4 tabs on the first line. Also, the second line I am adding would need to have 7 tabs between the end of A.RowNumber and the word as.

If anyone could help me figure this out, it would save me a great deal of time.

Also, if anyone knows of any good sites on where to research solutions to this sort of thing or good info on regular expression in general a link would certainly be appreciated.

Thanks for your consideration!

Nate J

[ben_josephs]

Find what: 'v' \+ convert\(varchar, (A.RowNumber)\)\t\t\t\t(as \[PersVeh!10!id\],)
Replace with: \0\n\1\t\t\t\t\t\t\t\2

[X] Regular expression

Replace All

The use of capturing parentheses here might not be necessary for your purposes, but it shows how this might be generalised.

This assumes you are using Posix regular expression syntax:

Configure | Preferences | Editor

[X] Use POSIX regular expression syntax

There are many regular expression tutorials on the web, and you'll find recommendations for some of them if you search this forum.

A standard reference is

Friedl, Jeffrey E F
Mastering Regular Expressions, 3rd ed
O'Reilly, 2006
ISBN: 0-596-52812-4
http://regex.info/

But be aware that the regular expression recogniser used by TextPad is very weak compared with modern tools. So you may get frustrated if you discover a handy trick that works elsewhere but doesn't work in TextPad.

[cignaj]

Will certainly do some looking around and keep in mind that not everything I read may be applicable. Thanks for the help and insight

[cignaj]

I tried the find replace above and it does not find any of the occurrences ...

Code: Select all

Select distinct
10								as Tag,
Null								as Parent,
Null								as [PersVeh!10],
'v' + convert(varchar, A.RowNumber)				as [PersVeh!10!id],
'd' +  UNVEHdrv_nbr						as [PersVeh!10!RatedDriverRef],
Null								as [ItemIdInfo!20],
Null								as [ItemIdInfo!20!InsurerId!element],

This is a snippet of the SQL I am trying to go through ... this particular file is about 39000 lines and I am trying to avoid manually adding another line since I have 3 other files similar to do the exact same thing with.

I need the line

Code: Select all

'v' + convert(varchar, A.RowNumber)				as [PersVeh!10!id],

to be duplicated and modified

Code: Select all

'v' + convert(varchar, A.RowNumber)				as [PersVeh!10!id],
A.RowNumber							as [PersVeh!10!id!hide],

I double checked to make sure POSIX and regular expression were both checked and they are. Am I missing something?

[ben_josephs]

Is there a spurious space at the end of your regex?
Are there exactly four tabs and no spaces before the word as?

[cignaj]

I must have added a space somehow at the end or beginning of the line because it is finding it after trying again and double checking that.

Is there a rather simple explanation to the 0 .. 1 .. 2 in the replace? I believe I left off part of the replace when I initially asked and need to figure out where to insert the additional '!hide' toward the end.

Thanks!

[Bob Hansen]

Check out the TextPad Help for "Replacement Expressions"

Replace with: \0\n\1\t\t\t\t\t\t\t\2

\0 = the full match that was found with the RegEx
\1 = the match that is inside the first set of (...)
\2 = the match that is inside the second set of (...)
\3 - \9 = the matches that are inside the third through ninth set of (...)
\n = a line feed at the end of the line
\t = a tab character

So the full string \0\n\1\t\t\t\t\t\t\t\2 means take the matching string, add a line feed, insert the values inside the first(...), follow that with 7 tabs, and iinsert the values inside the second (...)

In Freidl's reference book you will see that $0-$9 are more commonly used in other RegEx utilties vs. \0-\9 used by TextPad.

[cignaj]

You have both been extremely helpful. I modified the replace with what was needed and it works wonderfully. Thanks for your time and knowledge!