Get the value at a specific location in the field in mysql

Get the value at a specific location in the field in mysql

For example, there are two pieces of data:

To get the third column of data separated by spaces, you can use the substring_index () function

Mysql > select substring_index (name,'',-1) as addr from t limit 1 + | addr | +-+ | Dafeng District | +-+

Substring_index (column name, delimiter, index). The index starts with the number of characters,-indicates reverse order, and the first position starts at 1, not 0.

To get the data in parentheses, use the SQL statement to do this:

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

When the value in the field is more complex, you can write a script to handle it. There are many modules for processing data in the python library. Here is some of the script I wrote earlier.

Import reimport osdef main (): "" here the database result is stored as a file (because it is a heterogeneous database, there is no driver installed, if it is a mysql database, you can directly connect to the database to read data), and the fields are separated by','. You can also use the other delimiter "" with open ('C _ while _ wip _ user _ name _ r _ Cip, provider, model, mem, os, os_version, tip, port, vip, p_version = line.split (',') # separate lines into fields # port store try: vip = re.split ('(\ (|) |\) |\ |,', vip) # Clean data Remove the symbols such as'()','|'in the vip field and generate a list while''in vip: vip.remove ('') # remove the''character while''in vip: vip.remove ('') vip_w = vip [0] # fetch the first piece of data Other processing can be performed, such as saving the file, or directly storing the results in the database except Exception as e: print ('error:', e) if _ _ name__ =' _ main__': main ()