英文:
Oracle SQL how to check if a field has a certain value/values
问题
以下是翻译好的部分：
我有一个查询要写，我需要找到包含特殊字符的数据。
我知道如何使用像 '%example%' 这样的方法，但我想要搜索多个特殊字符而不是只有一些（我可以使用OR），所以我创建了一个新表格，其中包含了我要查找的所有特殊字符。
例如：
从表格1中选择
其中field1像 '%*%'
或者field1像 '%$%'
。
。
。
所以这是我的问题：我可以在上面的查询中使用我创建的表格来避免编写很多行代码吗？
谢谢。
英文:
I have a query that I am trying to write, I need to find the data that has special characters in it.
I know how to use like '%example%', but I want to search for multiple special characters rather than a few (which I can use OR), so I made a new table that contains all the special characters that I am looking for.
ex:
select * from table1
where field1 like '%*%'
OR field1 like '%$%'
.
.
.
So here is my question: Can I use the table I have created in the above query to avoid writing many lines of code?
Thanks.
答案1
得分: 1
以下是翻译好的内容：
这是一个选项。
示例表格：
SQL> select ename, job from emp order by job, ename;
ENAME JOB
---------- ---------
FORD ANALYST
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SMITH CLERK
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
KING PRESIDENT
ALLEN SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
WARD SALESMAN
14 rows selected.
这是您的“新”表格，其中包含您想要在示例表格中的某一列中搜索的字符串：
SQL> select * from test;
WORD
----
GER
LER
这是查询 - 搜索在test表中存储的“单词”在emp表的job列中出现的员工：
SQL> select e.ename, e.job
2 from emp e join test t on instr(e.job, t.word) > 0;
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
7 rows selected.
SQL>
英文:
Here's one option.
Sample table:
SQL> select ename, job from emp order by job, ename;
ENAME JOB
---------- ---------
FORD ANALYST
SCOTT ANALYST
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SMITH CLERK
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
KING PRESIDENT
ALLEN SALESMAN
MARTIN SALESMAN
TURNER SALESMAN
WARD SALESMAN
14 rows selected.
This is your "new" table, containing strings you'd want to search for within a column in sample table above:
SQL> select * from test;
WORD
----
GER
LER
This is the query - searching for employees whose job contains "words" stored in the test table:
SQL> select e.ename, e.job
2 from emp e join test t on instr(e.job, t.word) > 0;
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
7 rows selected.
SQL>
答案2
得分: 0
你可以使用以下方法来实现，使用 INNER JOIN ：
SELECT t.name
FROM mytable t
INNER JOIN regTable r on t.name like '%' || r.reg || '%';
或者你可以简单地使用 regexp_like 如下：
select *
from mytable
where regexp_like(name, '[*$]');
英文:
You can do it using INNER JOIN as follows :
SELECT t.name
FROM mytable t
INNER JOIN regTable r on t.name like '%' || r.reg || '%';
Or you can simply use regexp_like as follows :
select *
from mytable
where regexp_like(name, '[*$]');
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论