Introduction and examples of UltraEdit regular expressions __ Other

A few days ago, there is a need to import data in Excel into the database, originally thought to use the program to read the data in Excel and stored in the database, but a friend of the reminder that the use of editplus or ultraedit such tools to directly piece the data into SQL INSERT statements more easily, Also do not have to write any code, because I use ultraedit, so consider using ultraedit regular expression to do this thing, the following is such a process.

Assuming that the imported table fields: Name,email, introduce, and the data in Excel correspond to the same field, it is worth noting that data in Excel, such as introduce, cannot be wrapped, otherwise the statements will be executed with an error.

The sample data to be pieced together are as follows (copied from Excel and pasted into UltraEdit):

John Zhangsan@zihou.com "sat in May 2004 by the Ministry of Education approved to upgrade to the general undergraduate institutions.  "  

Each field value is separated by one or more tab keys, and the following steps:

Replace Press Ctrl+r

1, the first single quotes, double quotes and other characters removed, this step without regular, simple replacement can be.

2, remove the blank line: with regular%[^t]++^p replaced with an empty string, in addition, there are ^p$ can also, but in the replacement, ^p$ can only replace one blank line at a time.

3. Increase at the front of each row: INSERT INTO Test (name,email,introduce) VALUES (', in UltraEdit,% is the beginning of the line, the following figure:

4. Replace Tab key

5, the last at the end of the join ');

OK, that's it. This is a piece of normal SQL INSERT statement, it is not very easy to feel quickly.

How to learn ultraedit regular usage. The following two areas can be used:

1, from the Internet search some of this information

2, through the UltraEdit help document to learn

UltraEdit's help document has two aspects, one is its CHM format document and the other is its official website document.

Official Website Document entry mode is: Menu Help-> Quick Start Guide, in open window, click any link will go to its official website Help document page, of course, if you are here, you can also directly click on the following link:

Http://www.ultraedit.com/support/tutorials_power_tips/ultraedit.html

The document entry mode in CHM format is:

Menu Help-> use Help, or help-> index can, as shown below:

On the left side of the "regular expression" to find, it will pop up to the right side of the window, see a lot of use introduction bar. You can choose to be interested in entering.

The following is a description of the syntax extracted from the UltraEdit document :

Regular expression (UltraEdit syntax):

Symbol	Function
%	Match Start-Indicates that the search string must start at the beginning, but does not include the line terminator character in any of the selected result characters.
$	Match end of line-indicates that the search string must be at the end of a line, but does not include the row terminator characters in any of the selected result characters.
?	Matches any character except for line breaks.
*	Matches any number of characters that appear except for line breaks.
+	Matches one or more of the preceding characters/expressions. At least one occurrence of the character must be found. does not match a duplicate newline character.
++	Matches the preceding character/expression 0 or more times. does not match a duplicate newline character.
^b	Matches a page break.
^p	Match a line break (CR/LF) (paragraph) (DOS file)
^r	Match a line break (CR only) (paragraph) (MAC file)
^n	Match a line break (only LF) (paragraph) (UNIX file)
^t	Match a tab
[ ]	Matches a single character or range in any bracket
^{a^}^{b^}	Match expression A or B
^	Ignore the regular expression characters that followed
^(*^)	In the expression with parentheses or labels used in the substitution command. A regular expression can have 9 expression labels, and numbers determine the number according to the order in which they are in the regular expression. The corresponding substitution expression is the range of ^x,x is 1-9. For example, if ^ (h*o^) ^ (f*s^) matches "Hello folks", then ^2 ^1 means that it will be replaced with "folks hello".

Note –^ Here the character "^" is not a control key + value.

For example:

M?n matches "man", "Men", "min", but does not match "moon".

T*t matches the "Tea T" section of "Test", "Tonight" and "Tea Time", but does not match "tea

Time (there is a newline between "tea" and "time").

Te+st matches "test", "Teest", "teeeest", and so on, but does not match "TST".

[Aeiou] matches each vowel lowercase letter

[,.?] Match text ",", "." or "?".

[0-9a-z] matches any number or lowercase letter

[~0-9] matches any character except the number (~ indicates the content that does not match)

You can search for expressions A or B like the following:

"^{john^}^{tom^}

This will search for John or Tom. There should be no other content between the two expressions.

You can combine a or B and C or D in the same search as the following:

' ^{john^}^{tom^} ^{smith^}^{jones^} '

This will search for John or Tom followed by Smith or Jones.

The following table shows the regular expression syntax for the "Unix" style.

Regular Expressions (Unix syntax):

Symbol	Function
\	Indicates that the next character has a special meaning. "N" means match the character "n" and "\ n" to match a line break. Look at the example below (\d, \f, \ n, and so on).
^	Match/stop at the beginning of the line.
$	Match/stop at the end of the line.
*	Matches the preceding character 0 or more times.
+	Matches the preceding character one or more times. does not match a duplicate newline character.
.	Matches any single character except the newline character. does not match a duplicate newline character.
(expression)	In the expression with parentheses or labels used in the substitution command. A regular expression can have 9 expression labels, and numbers determine the number according to the order in which they are in the regular expression. The corresponding substitution expression is the range of ^x,x is 1-9. For example, if ^ (h*o^) ^ (f*s^) matches "Hello folks", then ^2 ^1 means that it will be replaced with "folks hello".
[XYZ]	Character set, matching any character between parentheses ...
[^XYZ]	Excludes character sets. Matches any characters that are not in parentheses.
\d	Matches a numeric character. equivalent to [0-9].
\d	Matches a non-numeric character, equivalent to [^0-9].
\f	Matches a page feed character.
\ n	Matches a line feed character.
\ r	Matches a carriage return character.
\s	Matches any character that does not appear, including spaces, tabs, and so on, but does not match newline characters.
\s	Matches any character that is not a blank area (the display character), but does not match the line feed.
\ t	Matches a tab character.
\v	Matches a vertical tab.
\w	Matches any word that contains an underscore.
\w	Matches any character that is not a word.
\p	Match cr/lf (equivalent to \ r \ n) to match the DOS line terminator.

Note –^ Here the character "^" is not a control key + value.

For example:

M.N matches "man", "Men", "min", but does not match "moon".

Te+st matches "test", "Teest", "teeeest", and so on, but does not match "TST".

Te*st matches "test", "Teest", "Teeeest", and "TST".

[Aeiou] matches each vowel lowercase letter

[,.?] Match text ",", "." or "?".

[0-9a-z] matches any number or lowercase letter

[^0-9] matches any character except the number (~ indicates the content that does not match)

You can search for expressions A or B like the following:

"(john| Tom) "

This will search for John or Tom. There should be no other content between the two expressions.

You can combine a or B and C or D in the same search as the following:

"(john| Tom) (smith| Jones) "

This will search for Smith or Jones as well as John or Tom following behind.

If you do not choose to use regular expressions in Find/replace, the following special characters are also valid in the replacement object:

Symbol	Function
^^	Match character "^"
^s	Represents the selected (highlighted) text in the active file window.
^c	Represents the contents of a clipboard.
^b	Matches a page break.
^p	Match a line break (CR/LF) (paragraph) (DOS file)
^r	Match a line break (CR only) (paragraph) (MAC file)
^n	Match a line break (only LF) (paragraph) (UNIX file)
^t	Match a tab

Note –^ here the character "^" is not a control key + value.