在oracle SQL查询中使用string contains函数

时间:2022-01-19 15:47:05

I'm using an Oracle database and I want to know how can I find rows in a varchar type column where the values of that column has a string which contains some character.

我正在使用Oracle数据库,我想知道如何在varchar类型列中找到行,其中该列的值包含一个包含某些字符的字符串。

I'm trying something like this (that's a simple example of what I want), but it doesn't work:

我正在尝试这样的事情(这是我想要的一个简单例子),但它不起作用:

select p.name
from   person p
where  p.name contains the character 'A';

I also want to know if I can use a function like chr(1234) where 1234 is an ASCII code instead of the 'A' character in my example query, because in my case I want to search in my database values where the name of a person contains the character with 8211 as ASCII code.

我还想知道我是否可以使用像chr(1234)这样的函数,其中1234是ASCII代码而不是我的示例查询中的'A'字符,因为在我的情况下我想在我的数据库中搜索其中的名称一个人包含8211作为ASCII码的字符。

With the query select CHR(8211) from dual; I get the special character that I want.

使用查询从双重选择CHR(8211);我得到了我想要的特殊角色。

Example:

例:

select p.name
from   person p
where  p.name contains the character chr(8211);

2 个解决方案

#1


52  

By lines I assume you mean rows in the table person. What you're looking for is:

通过线我假设你的意思是表人的行。你在寻找的是:

select p.name
from   person p
where  p.name LIKE '%A%'; --contains the character 'A'

The above is case sensitive. For a case insensitive search, you can do:

以上是区分大小写的。对于不区分大小写的搜索,您可以执行以下操作:

select p.name
from   person p
where  UPPER(p.name) LIKE '%A%'; --contains the character 'A' or 'a'

For the special character, you can do:

对于特殊角色,您可以:

select p.name
from   person p
where  p.name LIKE '%'||chr(8211)||'%'; --contains the character chr(8211)

The LIKE operator matches a pattern. The syntax of this command is described in detail in the Oracle documentation. You will mostly use the % sign as it means match zero or more characters.

LIKE运算符匹配模式。 Oracle文档中详细介绍了此命令的语法。您将主要使用%符号,因为它意味着匹配零个或多个字符。

#2


19  

The answer of ADTC works fine, but I've find another solution, so I post it here if someone wants something different.

ADTC的答案很好,但是我找到了另一个解决方案,所以如果有人想要不同的东西,我会在这里发布。

I think ADTC's solution is better, but mine's also works.

我认为ADTC的解决方案更好,但我的解决方案也有效。

Here is the other solution I found

这是我找到的另一个解决方案

select p.name
from   person p
where  instr(p.name,chr(8211)) > 0; --contains the character chr(8211) 
                                    --at least 1 time

Thank you.

谢谢。

#1


52  

By lines I assume you mean rows in the table person. What you're looking for is:

通过线我假设你的意思是表人的行。你在寻找的是:

select p.name
from   person p
where  p.name LIKE '%A%'; --contains the character 'A'

The above is case sensitive. For a case insensitive search, you can do:

以上是区分大小写的。对于不区分大小写的搜索,您可以执行以下操作:

select p.name
from   person p
where  UPPER(p.name) LIKE '%A%'; --contains the character 'A' or 'a'

For the special character, you can do:

对于特殊角色,您可以:

select p.name
from   person p
where  p.name LIKE '%'||chr(8211)||'%'; --contains the character chr(8211)

The LIKE operator matches a pattern. The syntax of this command is described in detail in the Oracle documentation. You will mostly use the % sign as it means match zero or more characters.

LIKE运算符匹配模式。 Oracle文档中详细介绍了此命令的语法。您将主要使用%符号,因为它意味着匹配零个或多个字符。

#2


19  

The answer of ADTC works fine, but I've find another solution, so I post it here if someone wants something different.

ADTC的答案很好,但是我找到了另一个解决方案,所以如果有人想要不同的东西,我会在这里发布。

I think ADTC's solution is better, but mine's also works.

我认为ADTC的解决方案更好,但我的解决方案也有效。

Here is the other solution I found

这是我找到的另一个解决方案

select p.name
from   person p
where  instr(p.name,chr(8211)) > 0; --contains the character chr(8211) 
                                    --at least 1 time

Thank you.

谢谢。