MySQL Text processing function 2_20160921

Requirements: From the current Test_a03order table to extract the product specifications, deposit data

First Add a table field
We add these two fields in the table named Product_size,deposit later to update the contents of the two fields can be

1 ALTER TABLE ADD VARCHAR (255) after ProductName; #在productname之后插入规格字段 2 ALTER TABLE ADD VARCHAR (255) after Price; #在price之后插入押金带元字 only this varchar data type

Insert a new field to build

Insert Field succeeded

II. Analysis of requirements attributes

1, found the product specifications, the deposit data is present in the ProductName so we need to look at how many products in the data sheet to observe the naming laws of all product

1 1 SELECT  from GROUP  by ProductName

View several products

as a result, there are 4 product in this table, respectively , "Soul Chicken Soup (1000 yuan) 10000ml*24 bottle/box", "Horizontal knife won love pill (888 yuan) 15 Capsules/box", "Brain fragments (99 yuan) 15 pieces/box", "Obsessed soup (888 yuan bottle) 10000ml*12 bottle/box", "Obsessed soup (888 yuan) 10000ml*12 bottle/box", by observing ProductName contains a "bet" word, product_size is the last word in the string, deposit in the middle of the parentheses and in the "bottle" word and "yuan" in the middle You can therefore use the Left () right () Char_length () function for processing.

2. Get Product_size

1 SELECT ProductName,Right (ProductName,1 as product_size2  from test_a03order3WHERE like "% of %"

Get Product_size

3. Get Deposit

# Let's take a step-by-step, first remove the left side of the "Bottle" field (containing the string itself), and then remove the left side of the "meta" word (containing the string itself)

1 SELECT ProductName, 2  Left (Productname,instr (ProductName, "Bottle"))  as Deposit_left, 3  Left  as Deposit_leftall 4  from Test_a03order 5 WHERE  like "% % "

first steps to get deposit deposit

This time found that Deposit_leftall contains all of the Deposit_left string, and the difference is in the deposit value, and is located in the right part of the Deposit_leftall field, with Deposit_ Leftall minus Deposit_left is not a deposit. But the string type cannot be subtracted, so we need to first calculate the length of the above deposit_left,deposit_leftall with the difference in their length as the parameter intercept of the right () function. Deposit_leftall

Why do we use Char_length () instead of the length () function to intercept character lengths, which is length () The default length of a Chinese character is 3,char_length () both Chinese and English default lengths of 1

so now re-write the SQL

1 SELECTProductName,2  Left(Productname,instr (ProductName, "Bottle")) asDeposit_left,3  Left(Productname,instr (ProductName, "Yuan")) asDeposit_leftall,4Char_length ( Left(Productname,instr (ProductName, "Bottle"))) asDeposit_left_length,5Char_length ( Left(Productname,instr (ProductName, "Yuan"))) asDeposit_leftall_length,6  Right( Left(Productname,instr (ProductName, "Yuan")), Char_length ( Left(Productname,instr (ProductName, "Yuan")))-Char_length ( Left(Productname,instr (ProductName, "Bottle")))) asDeposit7#上面right () This long code is essentially right (deposit_leftall,deposit_leftall_length-deposit_left_length)8  fromTest_a03order9 WHEREProductName like"%Bet%"

Get Deposit Second step

Rearrange the following code to eliminate intermediate processes for both product_size and deposit

1 SELECTProductName,2  Right(ProductName,1) asProduct_size,3  Right( Left(Productname,instr (ProductName, "Yuan")), Char_length ( Left(Productname,instr (ProductName, "Yuan")))-Char_length ( Left(Productname,instr (ProductName, "Bottle")))) asDeposit4  fromTest_a03order5 WHEREProductName like"%Bet%"

Successful acquisition of deposit

Iii. update the two fields inserted in the data table

Update Field Contents

1 UPDATETest_a03orderSETProduct_size=right(ProductName,1)WHEREProductName like"%Bet%";2 UPDATETest_a03orderSET' Deposit '=right( Left(Productname,instr (ProductName, "Yuan")), Char_length ( Left(Productname,instr (ProductName, "Yuan")))-Char_length ( Left(Productname,instr (ProductName, "Bottle"))))WHEREProductName like"%Bet%"

update two newly inserted fields in a data table

Update successfully executes query view effect

1 SELECT *  from Test_a03order

View Update Effects

Iv. success of the update

MySQL Text processing function 2_20160921