甲骨文喜欢和两者之间的使用

I thought this would have been an easy google search but couldn't find any solutions. Is there a way to use the Like and Between together in a query?

我原以为这是一个简单的谷歌搜索，但找不到任何解决方案。是否有一种方法可以在查询中使用Like和Between在一起?

Example

例子

REASON_CODES

A00 VMC B10
A00 RTD B19
.
.
.
A99 RNT B40

I am trying to write a query like:

我正在尝试编写这样的查询:

Select count(*) from table_1 where REASON_CODES like between '%A10%' and '%A25%'

Is there a solution to do this? I was reading "convert" may do the trick but I had no luck.

有解决办法吗?我读的是“convert”这个词，但我没有运气。

Thanks

谢谢

3 个解决方案

#1

You can use substring

您可以使用子串

  Select count(*) from table_1 
  where   substr(reason_codes, 1,3) between 'A10' and 'A25';

#2

If you're just trying to match the beginning of the REASON_CODE strings, you can do:

如果您只是试图匹配推理代码字符串的开头，您可以这样做:

SELECT COUNT(*)
FROM table_1
WHERE REASON_CODE >= 'A10' AND REASON_CODE < 'A26'

This is equivalent to scaisEdge's answer, but it can take advantage of an index on the REASON_CODE column, which cannot be used if you first call SUBSTR().

这等价于scaisEdge的答案，但是它可以利用ration_code列上的索引，如果您首先调用SUBSTR()，则不能使用该索引。

You have to use >= and < because BETWEEN includes both endpoints, and you want to match everything up to, but not including A26. You can't use BETWEEN 'A10' AND 'A25' because strings beginning with A25 and having additional characters are higher than that.

您必须使用>=和<，因为BETWEEN包含两个端点，您希望匹配所有的端点，但不包括A26。你不能在'A10'和'A25'之间使用，因为以A25开头的字符串的字符比A25要多。

#3

Oracle Setup:

甲骨文设置:

CREATE TABLE TABLE_NAME ( REASON_CODES ) AS
SELECT 'A00 VMC B10' FROM DUAL UNION ALL
SELECT 'A00 RTD B19' FROM DUAL UNION ALL
SELECT 'A09 RTD B19' FROM DUAL UNION ALL
SELECT 'ASD A10 B19' FROM DUAL UNION ALL
SELECT 'XYZ A20 RTD' FROM DUAL UNION ALL
SELECT 'ABC XYZ A25' FROM DUAL UNION ALL
SELECT 'A26 RTD B19' FROM DUAL UNION ALL
SELECT 'A99 RNT B40' FROM DUAL;

Query:

查询:

SELECT *
FROM   TABLE_NAME
WHERE  REGEXP_SUBSTR( REASON_CODES, 'A\d{2}' ) BETWEEN 'A10' AND 'A25';

Output:

输出:

REASON_CODES
------------
ASD A10 B19  
XYZ A20 RTD  
ABC XYZ A25

#1