Good day. I have a sign, in it a column with the format of the line (ip [/ folder])

12.12.12.12[/folder] 12.34.56.78[/otherFolder] 11.11.11.11[/folder3]

It is necessary to make a request that would give 2 columns (ip, / folder). That is, cut the string and discard the characters of square brackets. All this needs to be done on SQLite. Thank you for your participation and answers.

  • @QuitLN, According to the rules of the forum, questions should not be reduced to offers to do the work. - karmadro4
  • one
    The question is, and what for it is necessary? Why if you need to remove the tonsils, so necessarily through the anus? - alexlz
  • The answer is performance. My project uses a self-written programming language. And the base volumes are large. Therefore, cutting it upon receipt will be too costly. The idea is to load SQLite, which is optimized for large amounts of data. And take the info from the database immediately ready. - QuitLN
  • @QuitLN If this data in a column is used as two different fields, then why not change the database schema by breaking it up into two columns? - stanislav
  • There is already a large amount of such information with which you have to work. Such a format is a consequence of a wrong decision at the stage of creating a structure. It's too late to make changes. We have to work with what is. The question is not about doing the job. And about auxiliary functions. Is there at least a way in SQLite to get the position number of the character '[' in a string? - QuitLN

2 answers 2

SQLite does not have built-in functions for splitting rows by separator. What can be done?

  1. Replace the "/" sign with a certain number of spaces with the replace function.
  2. Split the string into two along the maximum length of the left part, taking into account the spaces by the function substr.
  3. Remove unnecessary spaces with the trim function.
  4. Remove square brackets with the function substr or replace.

That is, do so:

1.2.3.4/[xyz] - исходная 1.2.3.4 [xyz] - с пробелами 1.2.3.4 , [xyz] - разбита на части 1.2.3.4, [xyz] - без пробелов 1.2.3.4, xyz - без скобок

Another option is to write the SQLite extension itself to C.

    It is necessary to cut the string with one of the methods specified here , then replace the characters of square brackets with empty symbols and wrap the whole thing with TRIM()

    PS Nobody will write for you - that's for sure

    Update Since SQLite is an embedded database, it’s likely that all this disgrace happens either under Android or under iOS, so it’s better to get the full string, then use Java or Objective-C to cut it properly than to actually remove the tonsils through the anus (as noted by the comrades in kamentah)