Obtain the value of a specific position in the field in MySQL

Obtain the value of a specific position in the field in MySQL

Obtain the value of a specific position in the query field in MySQL

For example, there are two types of data:

mysql>  select  *  from  t; + --------------------------+ |  name | + --------------------------+ | Dafeng district, Yancheng City, Jiangsu Province | | Yancheng Airport code (YNZ) | + --------------------------+

To obtain the third column of Data separated by spaces, use the substring_index () function.

mysql>  select  substring_index( name , ' ' ,-1)  as  addr  from  t limit 1; + -----------+ | addr      | + -----------+ | Dafeng district | + -----------+

Substring_index (column name, separator, index), index indicates the number of characters starting from,-Indicates reverse order, the first position starts from 1, not 0

To obtain data in parentheses, use the SQL statement as follows:

mysql>  select  substring_index(substring_index( name , '(' ,-1), ')' ,1)  as  a  from  t limit 1,1; + ------+ | a    | + ------+ | YNZ  | + ------+ 

When the field values are complex, you can write scripts for processing. There are many data processing modules in the python library. Below are some of the scripts you have previously written.

import  re import  os def  main():     "The database result is stored as a file here (because it is a heterogeneous database, no driver is installed,     For mysql databases, you can directly connect to the database to read data. The fields are separated by commas (,). You can also use other separators """     with  open ( 'C:/Users/user/Desktop/1.txt' ,  'r' ) as f:         while  1 :  # Endless loop             line  =  f.readline()  # Read a row at a time             if  not  line:  # Exit when no data exists                 break             server_room, host, wip, lip, server_role_id, raid, cpu, disk, cip, provider, model, mem, os, os_version, tip, port, vip, p_version  =  line.split( ',' )             # Separate rows into fields              # port store             try :                 vip  =  re.split( '（|\(|）|\)|\||,' , vip)  # Clean data, remove the '()', '|' and other symbols in the vip field, and generate a list                 while  ''  in  vip:                     vip.remove(' ') # Remove' 'Character                 while  ' '  in  vip:                     vip.remove( ' ' )                     vip_w  =  vip[ 0 ]  # Retrieve the first data and perform other processing, such as storing files or directly storing results into the database              except  Exception as e:                 print ( 'error:' , e) if  __name__  = =  '__main__' :     main()

�� Permanent link update: