Is it possible to split a column into 2 columns

Question

MySQL

Good afternoon, dear Habravchane,

Please tell me if it is possible to split one column into two in mysql:

I have a kladr base and in the code column there is an object code of 13 characters (for example: 0100000000000)

The task is to split this value into several by columns for example 01 | 000 | 000 | 000 | 00

I want to split the value in this way for a faster search for objects (cities, regions, etc.)

Can you tell me if this can be done using mysql internals or how can I optimize the query to speed up data selection without splitting the column?

Answer 1

It is possible to divide, using the SUBSTR function.
Also, without dividing, bitwise operations BIT_COUNT, etc. will probably help. see
Or also without separating, with a simple condition, for example the second column from the right, you can find the values ​​123 is allowed ... WHERE `field`>=12300 and `field`<12400

Answer 2

you have symbols… and there are 13 of them…
1. you can split them without any problems, using the built-in tools for working with strings or an external script
2. optimize?
a) add a complex condition on this field last
b) you can add an index on this field, then queries like where field = '01%' will be executed quickly. for example, you can do where field = '01%' AND substr(field, 3,3) = '000', in the first part of the condition, the index will work for you, which will reduce the selection on which the slow second part of the condition will be executed.
3. move from symbols to numbers if possible. This will give you a SMALL boost.
PS: a lot depends on the nature of queries to the database and the amount of data, it is more universal to break it down and make the fields not symbolic, but numeric
PPS: perhaps you need to do caching and very few requests will reach the database and then the speed of their execution may not become critical

Answer 3

You can create the required fields f1, f2, f3 and UPDATE tbl SET f1=SUBSTR(origin, 1, 2), f2=SUBSTR(origin, 3, 3)… You can create a trigger to update.

Answer 4

Use string functions:
www.mysql.ru/docs/man/String_functions.html
UPDATE table SET new_col_1 = LEFT(oldcol,2), new_col_1 = RIGHT(oldcol,11)

Answer 5

Is the string length fixed?
Is the width of "values" fixed?
DDL and request can show?

Answer 6

They didn't write about LIKE. You can construct a string of 13 characters - significant and "_" and compare with it.

Answer 7

INSERT INTO… + SUBSTR()?