SQL selecting for a unique character

Question

in sql, I'd like to get all rows, where the slash '/' as character exists only once.

For exapmle:

[table1]
id | path_name  |
 1 | "/abc/def/"|
 2 | "/abc"     |
 3 | "/a/b/cdfe"|
 4 | "/hello"   |

select * from table1 where path_name=.... ;

So in this example, I want to have only the second and fourth row...

How do I have to form this statement?

use like '%/%' and not like '%/%/%' – Sachin Feb 28 '13 at 12:22 — Sachin, Feb 28 '13 at 12:22

score 3 · Answer 1 · answered Feb 28 '13 at 12:21

3

where path_name like '%/%' and path_name not like '%/%/%'

or

where len(path_name) = len(replace(path_name,'/','')) + 1

answered Feb 28 '13 at 12:21

Andomar

232,371
49
380
404

score 1 · Accepted Answer · answered Feb 28 '13 at 12:22

To find an expression with exactly one slash:

where path_name like '%/%' and not path_name like '%/%/%'

Explanation: The first one checks that the slash appears at least once. The second one checks that it doesn't appear twice.

At least once, but less than twice, is exactly once.

If you want only those which begin with a slash, you should change the first pattern to '/%'.

score 0 · Answer 3 · answered Feb 28 '13 at 12:23

0

select * from table1 where path_name not like '/%/%' and path_name not like '/%/';

answered Feb 28 '13 at 12:23

Parvathy

2,275
3
24
39

score 0 · Answer 4 · edited May 23 '17 at 11:43

0

Or count the length after replacing / with nothing.
WHERE ( LENGTH(col) - LENGTH(REPLACE(col, '/', '')) )=1

( thanks to How to count instances of character in SQL Column )

edited May 23 '17 at 11:43

Community

1
1

answered Feb 28 '13 at 12:27

AjV Jsy

5,799
4
34
30

gady RajinikanthB · Answer 5 · 2017-10-17T11:15:42.147

0

1.select * from Table1 where len(path_name)-len(replace(path_name,'/',''))= 1

2.select * from table1 where len(path_name)= len(replace(path_name,'/',''))+1

3.select * from table1 where path_name like '%/%' and path_name not like'%/%/%'

edited Oct 17 '17 at 11:15

answered Oct 17 '17 at 07:11

gady RajinikanthB

19
7

1

Please at least explain your answers a bit. – xpy Oct 17 '17 at 08:53
len(path_name)-len(replace(path_name,'/','')) ----- this statement lenth is 1 example view @why i need only one '/' ...1.First i am finding the length then i - (minus) the replace lenth of the / charecter – gady RajinikanthB Oct 17 '17 at 11:12
1

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. You should mention something about efficiency, and any indexes that might improve that efficiency. – Toby Speight Oct 17 '17 at 12:26

SQL selecting for a unique character

5 Answers5