MSSQL deletes characters that do not contain a single character

stuff deletes characters of the specified length and inserts another set of characters at the specified starting point.

Grammar. stuff ( character_expression , start , length , character_expression )

Parameter. character_expression

An expression consisting of character data. A character expression can be a constant, a variable, or a column of character or binary data.

start is an integer value that specifies where deletion and insertion begin. If start or length is negative, an empty string is returned. If start is longer than the first character expression, an empty string is returned.

length

is an integer that specifies the number of characters to remove. If the length is longer than the first character expression, up to the last character in the last character expression is removed.

Return type. If character expression is a supported character data type, character data is returned. If character expression is a supported binary data type, binary data is returned.

Exegesis. String functions can be nested.

Example. The following example creates and returns a string by removing the three characters starting at the second position (character b) in the first string (abcdef), and then inserting the second string at the beginning of the deletion.

select stuff('abcdef', 2, 3, 'ijklmn')

Here's the result set:

aijklmnef

1 row(s) affected)

delete from tablename where address !='Fukuda'

Change the above statement tablename to your surface.

Use the following statement:

select * from table where','&txt&',' like ',9,'

I'm assuming that the & represents a string join, and some databases use +, and some use it. , some use ||You check your database manual.

Using regular expressions, will.

?!Built).*

Replace all with empty.

And then again. ]+

Replace all with empty.

Let's talk about this suspicious IP first, which is said to be a mentally retarded spider, God bless it to hell.

Friends who have read the Apache log should know that each line of the Apache access log starts with the visitor's IP. Because the logs are relatively large, I used ultraedit to look at them. When using Ultraedit, you need to pay attention to the fact that there are two kinds of regular rules, one is in line with the Perl specification, and the default is the one that comes with the ut, and the writing method is more special.

After some thought, I found that there seems to be no way to directly delete the usage of "does not contain" a specified string in the regular expression. After thinking about it, let's write a line that contains a specified string: (To test, you can find out the lines that contain the specified IP, and the next thing to do is how to keep these lines and delete the other lines.)

After n attempts, I finally gave up on the idea of directly deleting lines that didn't contain this IP. What to do? Take a step back and open the sky!

Since you can't delete it directly, let's take a detour. Although I can't directly delete a line that doesn't contain the specified string, I can still write it if I delete the regex that doesn't contain the specified character.

1. For example, to delete all the rows in the following image that contain [OneNote].

2. Enter a few words in the empty cell above ** first, and add a blank line if there is no empty cell.

3. Click [Filter] in the Excel2010 Start tab [Sort and Filter].

4. At this time, a button will appear on the right side of the cell at the top of the selection range. Click that button.

5. Enter the specified text in the text filter of the pop-up dialog box, i.e. [onenote]. Click the OK button.

6. Hold down the left mouse button and drag the mouse at the line number containing [OneNote] to select the lines.

This is done using the Find function in conjunction with the delete function.

Excel version reference: 2010

Test: Remove rows containing 6.

1. Ctrl+F to bring up the search dialog.

2. Find and enter: *6*, click to find all.

3. Press Ctrl+A, right-click any cell in the selected row 4, Delete - whole row, OK.

5. Check the effect.

Use the filter function: Select the column and menu "Data" = > "Filter" = > "Auto Filter".

A small triangular arrow appears in the bottom right corner of the first cell of the column, click on this arrow, the drop-down filter option appears, select The Thread'win32 thread', all values are the thread'win32 thread'Select these cells, right-click, select "Delete Row", then click the filter arrow, select "All", and get the result.

-- oracle

update table set column = replace (column,'Jin',''Where column like'Jin'

or update table set column ='Jin' ||Column where column not like'Jin'

mysql

update table set column = replace (column,'Jin',''Where column like'Jin'

or update table set column = concat('Jin'Column where column not like'Jin'

sqlserver

update table set column = replace (column,'Jin',''Where column like'Jin'

or update table set column ='Jin'Column where column not like'Jin'

Use automatic filtering, in "Custom", select "does not contain", and then enter "s", after confirmation, all data that does not contain s will be filtered out, and you can delete it.

Find ss, find all, then the search results will be listed under the search box, use shift combined with the mouse to select the search results, close the search dialog, delete, the whole row.

With Find Replace, replace the following: *ss* with blank.

1. Use search to find all the inclusions? and then fill it with a color.

2. Then find the format (select None), and then delete the found cells.

In the b2 input formula drop-down, first put metal stamp... Extract it to column B, copy and paste it into column A, and then delete column B.

mid(a2,find("metal stamp",a2),if(iserr(find(char(10),a2,find("metal stamp",a2))-find("metal stamp",a2)),50,find(char(10),a2,find("metal stamp",a2))-find("metal stamp",a2)))

If the metal stamp is fixed with 3 bits, such as 18k 99k 66k 27k and other 3 bits, the formula is reduced as follows:

mid(a2,find("metal stamp",a2),15)

b1 Enter the following formula:

if(substitute(a1,"metal stamp","")=a1,"",substitute(a1,a1,mid(a1,find("metal stamp",a1),find("k",a1,find("metal stamp",a1))-find("metal stamp",a1)+1)))) drop-down.

Filter with -> text - > not included.

Enter keywords and delete all the filtered rows.