Convert regex to Oracle database regex

Question

I'm trying to convert this regex:

^.*[^a-z1-9\-].*$

to a regex to be used in an oracle database query. What I want to do is to find all rows that contains at least one character in the column name different of a-z, 0-9 and -.

The query

select * from device where regexp_like(ctnmname, '^.*[^a-z1-9\\-].*$')

returns all rows in the table.

EDIT

The problem was the regex with the 0 and a escaped -. It works with the regex ^.*[^a-z0-9-].*$

Tim Pietzcker · Accepted Answer · 2013-04-17 19:40:47Z

2

The regex itself looks OK. You might want to make it case-sensitive and include the 0:

SELECT * FROM device WHERE REGEXP_LIKE(mycolumn, '^.*[^a-z0-9-].*$', 'c');

answered Apr 17, 2013 at 19:40

Tim Pietzcker

337k59 gold badges520 silver badges572 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

beny23 · Accepted Answer · 2013-04-17 19:43:30Z

1

As the dash is the last in the character class, you won't need to escape it:

where regexp_like(ctnmname, '^.*[^a-z1-9-].*$')

However

where regexp_instr(ctnmname, '[^a-z1-9-]') > 0

might be easier to read...

2 Comments

elias Over a year ago

Thanks for the answer beny23, but still not working. Gives the same result.

beny23 Over a year ago

Can you edit your question and add some of the values present in your ctnmname column?

Egor Skriptunoff · Accepted Answer · 2013-04-17 20:01:29Z

0

where not regexp_like(ctnmname, '^(\w|-)*$')

fiddle

edited Apr 17, 2013 at 20:01

answered Apr 17, 2013 at 19:54

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Collectives™ on Stack Overflow

Convert regex to Oracle database regex

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related