CODEWITHSUNDEEP
mysqlsql
winter sun
winter sun

Reputation: 582

SQL - find exact match inside a string

I am using mySql Data base I have a table with a column for account hierarchy, the values in this column look like this

/home/first level hierarchy / second level hierarchy / third level hierarchy/

(some have only one level and some have 3 levels)

I want to get only the first level hierarchy items

my SQL query looks like this

SELECT Account.name FROM Account WHERE account_hierarchy like "/home/%/"

the problem is that i am getting in the result all the levels after home since they all ends with /

is there any way in SQL to get only the first level

Upvotes: 0

Views: 786

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I think you want exactly three slashes. You can get that using:

where hierarchy like '/home/%/' and
      hierarchy not like '/home/%/%/'

In databases that support regular expressions, there are alternative solutions.

Actually, if you want just a beginning and ending slash, then there is no slash in the middle with other characters around it. So you can use:

where hierarchy not like '/home/%_/_%'

This assumes that all begin with '/home/'.

Upvotes: 2

Luuk
Luuk

Reputation: 14968

In MySQL, you can use REGEXP_SUBSTR

Example:

SELECT regexp_substr('/a/b/c','[a-z]+',3), returns b

Because 'b' is the piece after the second '/'.

You might need to change the regular expression '[a-z]+'. This on only matches lower-case paths. If you also need upper-case matches, change it to [a-zA-Z]+.

All the other posibilitues of regular expressions (in MySQL) are explained here.

Upvotes: 1

Related Questions