Split a String into Rows Using Pure SQLite

Split a string into rows using pure SQLite

This is possible with a recursive common table expression:

WITH RECURSIVE split(s, last, rest) AS (
  VALUES('', '', 'C:\Users\fidel\Desktop\Temp')
  UNION ALL
  SELECT s || substr(rest, 1, 1),
         substr(rest, 1, 1),
         substr(rest, 2)
  FROM split
  WHERE rest <> ''
)
SELECT s
FROM split
WHERE rest = ''
   OR last = '\';

^{(You did not ask for a reasonable way.)}

Split column into different rows on SQLite recursively using delimiter ,

With a recursive CTE:

with recursive cte as (
  select _id, id_surat, id_ayat, 
    id_ayat + 0 col 
  from tablename 
  union all 
  select _id, id_surat, trim(substr(id_ayat, length(col) + 2)), 
    trim(substr(id_ayat, length(col) + 2)) + 0 
  from cte  
  where instr(id_ayat, ',')
)
select _id, id_surat, col id_ayat 
from cte
order by _id, id_surat

See the demo.

Results:

| _id | id_surat | id_ayat |
| --- | -------- | ------- |
| 3   | 2        | 112     |
| 3   | 2        | 213     |
| 3   | 3        | 19      |
| 3   | 3        | 83      |
| 3   | 3        | 85      |
| 3   | 3        | 102     |

Is it possible to split value from a column to rows when query in SQLite?

Let's say you are getting each record as a String as I have mentioned below and from there you can split all data as below:

Create One Class Which Contains All Properties like Animal, Color and Gender as Below

  class AnimalData         // You can give any name 
  {
      String strAnimal, strGender, strColor;

      public AnimalData(String strAnimal, String strGender, String strColor)
      {
          this.strAnimal = strAnimal;
          this.strGender = strGender;
          this.strColor = strColor;
      }

      public String getStrAnimal()
      {
          return strAnimal;
      }

      public String getStrGender()
      {
          return strGender;
      }

      public String getStrColor()
      {
          return strColor;
      }
  }

Now currently I am using 3 static variable which contains data as above you get from database, you need to use them instead of these below variable

    String strAnimalData[] = {"Cat","Cat","Dog","Dog"};
    String strGenderData[] = {"Male","Female","Male","Female"};
    String strColorData[] = {"White,Black,Brown","White","Black,Yellow","Black"};

Now I am using one method which will give me result in list from above values

public List<AnimalData> getAllAnimalData()
{
      List<AnimalData> strAllAnimalData = new ArrayList<>();

      for(int i = 0; i < strAnimalData.length; i++)
      {
          if(strAnimalData[i].indexOf(",") > 0)
          {
              String strSubAnimalData[] = strAnimalData[i].split(",");

              for(int j = 0; j < strSubAnimalData.length; j++)
              {
                  AnimalData ad = new AnimalData(strSubAnimalData[j], strGenderData[i], strColorData[i]);
                  strAllAnimalData.add(ad);
              }
          }
          else if(strGenderData[i].indexOf(",") > 0)
          {
              String strSubGenderData[] = strGenderData[i].split(",");

              for(int j = 0; j < strSubGenderData.length; j++)
              {
                  AnimalData ad = new AnimalData(strAnimalData[i], strSubGenderData[j], strColorData[i]);
                  strAllAnimalData.add(ad);
              }
          }
          else if(strColorData[i].indexOf(",") > 0)
          {
              String strSubColorData[] = strColorData[i].split(",");

              for(int j = 0; j < strSubColorData.length; j++)
              {
                  AnimalData ad = new AnimalData(strAnimalData[i], strGenderData[i], strSubColorData[j]);
                  strAllAnimalData.add(ad);
              }
          }
          else
          {
              AnimalData ad = new AnimalData(strAnimalData[i], strGenderData[i], strColorData[i]);
            strAllAnimalData.add(ad);
          }
      }

      // Below code is just for Display Data

      for(AnimalData tempData: strAllAnimalData)
      {
          System.out.println("\t" + tempData.getStrAnimal() + "\t" + tempData.getStrGender() + "\t" + tempData.getStrColor());
        System.out.println("\n");
    }
}

Note:- Currently this will work when each position only one variable has more data, like if animal has "Cat,Dog" at position 1 and Color has also "Red,Brown" at position 1 then it will only separate animal not by color, for that you need to implement same logic inside for loops.

How to split comma delimited values into multiple rows using Python

Using Python,

cursor.execute("""Select * from Movies""")
all_data = cursor.fetchall()
cursor.execute("""CREATE TABLE IF NOT EXISTS Countries
                    (ID TEXT,
                    Country TEXT)""")
for single_data in all_data:
    countries = single_data[1].split()
    for single_country in countries:
        cursor.execute("""INSERT INTO Countries VALUES(%s,"%s")"""%(single_data[0],single_country))
    conn.commit()

Android SQLite query string to reach to D in A~B~C~D~E~F where A,B,C,D,E and F could have any interested length and data

Eureka! It's possible with LIKE clause (NOT LIKE) in a basic SQLite:

          SELECT_QUERY =
                        String.format("SELECT * FROM %s WHERE %s NOT LIKE %s;",
                                Constants.TABLE_M4,
                                Constants.KEY_M4_Data,
                                "%~%~%~~%'"
                        );

If D is not empty, it is identified with the above query

SQLite: Efficient substring search in large table

Solution 1:
If you can make every character in your database as an individual word, you can use phrase queries to search the substring.

For example, assume "my_table" contains a single column "person":

person
------
John Doe
Jane Doe

you can change it to

person
------
J o h n D o e
J a n e D o e

To search the substring "ohn", use phrase query:

SELECT * FROM my_table WHERE person MATCH '"o h n"'

Beware that "JohnD" will match "John Doe", which may not be desired.
To fix it, change the space character in the original string into something else.

For example, you can replace the space character with "$":

person
------
J o h n $ D o e
J a n e $ D o e

Solution 2:
Following the idea of solution 1, you can make every character as an individual word with a custom tokenizer and use phrase queries to query substrings.

The advantage over solution 1 is that you don't have to add spaces in your data, which can unnecessarily increase the size of database.

The disadvantage is that you have to implement the custom tokenizer. Fortunately, I have one ready for you. The code is in C, so you have to figure out how to integrate it with your Java code.

---

Related Topics