CODEWITHSUNDEEP
androidsqlitesql
Raykud
Raykud

Reputation: 2484

select items with certain "/" in SQL

I did a bit of research about sql escape characters and count statements and didnt find a solution to my question. Even though I used stuff like: 

SELECT * FROM table WHERE path LIKE '%/_%' ESCAPE '/';

I got a table where in a column there is paths so I want to select the items where I have certain number of slashes:

ID    DIRECTORY
1     root/A
2     root/B
3     root/A/1/2
4     root/B/1/2
5     root/A/1
6     root/B/2

so, how do I select for example the elements that have only 2 slashes??

Edit 1: This is to be done in Android SQL-Lite Database

Upvotes: 0

Views: 104

Answers (2)

Jon
Jon

Reputation: 437434

You can use this trick to count occurrences of a character in a string:

SELECT LENGTH('path') - LENGTH(REPLACE('path', '/', '')) AS `occurrences`

So you can achieve the goal with

SELECT id, path FROM
  (SELECT id, path, LENGTH('path') - LENGTH(REPLACE('path', '/', '')) AS `occurrences`
   FROM table) temp
WHERE occurrences = 2

However, I expect performance will be terrible. If you are going to query like that, consider adding a column with the path depth so that you can query directly with

SELECT id, path FROM table WHERE depth = 2

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270637

You can use a regular expression:

SELECT * FROM table WHERE path REGEXP '^([^/]*)/([^/]+)/([^/]*)$';

The above expression looks specifically for an optional group of characters not containing /, followed by /, followed by another group without /, followed by /, and optionally another set of characters before the end of the string.

So:

     /Bxx92/2  -- match
5     root/A/1  -- match
6     root/Bxx92/2  -- match
6     root/Bxx92/2  -- match
7     root/Bxx92/  -- match
6     root/2  -- NO match

If there MUST be something before the first and after the last /, change the expression to '^([^/]+)/([^/]+)/([^/]+)$'

Upvotes: 2

Related Questions