Oracle SQL Query to find the existence of characters in string

Question

I am using oracle database and trying to find out the query which should return the result when there is a special character(',`,(,)) exist on the string.

I am trying something like this,

select username from users where username like (',`,~,(,));

I tried to achieve the same using the below query,

select username from users where (username like '%`%' OR username like '%~%');

It doesn't consider the second condition and returns the value to the first condition only.

Is there any function/methods using which this result can be fetched?

The second query should get you all users whose names contain `\`` or `~` or both. Are you sure it doesn't? Or am I misunderstanding "It doesn't consider the second condition"? — Thorsten Kettner, Oct 16 '17 at 11:25
Please confirm the list of special characters you need to check.Is it single-quote, comma, backtick, round brackets and tilde? If so the regexp is `'['',\`()~]'`. — William Robertson, Oct 16 '17 at 11:40

score 1 · Answer 1 · answered Oct 16 '17 at 06:10

1

You can use regular expressions and check all special characters with one condition:

SELECT username 
FROM users 
WHERE regexp_instr(username,'[''`\(\)]') > 0

answered Oct 16 '17 at 06:10

pablomatico

2,222
20
25

score 0 · Answer 2 · answered Oct 16 '17 at 11:09

0

Old school style without regexp

where length(translate(username, '_''`~()', '_')) <> length(username)

answered Oct 16 '17 at 11:09

Dr Y Wit

2,000
9
16

Oracle SQL Query to find the existence of characters in string

2 Answers2