在表中获取所有可能的组合

Hello guys I have a small predicatement that has me a bit stumped. I have a table like the following.(This is a sample of my real table. I use this to explain since the original table has sensitive data.)

你好，伙计们，我有个小的预测，让我有点为难。我有这样一张桌子。这是我的真实表格的一个例子。我用这个来解释，因为原始表有敏感数据。

CREATE TABLE TEST01( 
TUID VARCHAR2(50),
FUND VARCHAR2(50),
ORG  VARCHAR2(50));
Insert into TEST01 (TUID,FUND,ORG) values ('9102416AB','1XXXXX','6XXXXX');
Insert into TEST01 (TUID,FUND,ORG) values ('9102416CC','100000','67130');
Insert into TEST01 (TUID,FUND,ORG) values ('955542224','1500XX','67150');
Insert into TEST01 (TUID,FUND,ORG) values ('915522211','1000XX','67XXX');
Insert into TEST01 (TUID,FUND,ORG) values ('566653456','xxxxxx','xxxxx');
Insert into TEST01 (TUID,FUND,ORG) values ('9148859fff','1XXXXXX','X6XXX');

table data after insert

表数据后插入

"TUID"                        "FUND"                        "ORG"                         
"9102416AB"                   "1XXXXX"                      "6XXXXX"                      
"9102416CC"                   "100000"                      "67130"                       
"955542224"                   "1500XX"                      "67150"                       
"915522211"                   "1000XX"                      "67XXX"                       
"566653456"                   "xxxxxx"                      "xxxxx"                       
"9148859fff"                  "1XXXXXX"                     "X6XXX"

The "X"'s are wild card elements*( I inherit this and i cannot change the table format)* i would like to make a query like the following

“X”是外卡元素*(我继承了这个，我不能改变表格的格式)*我想做如下的查询。

select tuid from test01 where fund= '100000' and org= '67130'

however what i really like to do is retrieve any records that have have those segements in them including 'X's

但是，我真正想做的是检索任何有这些分段的记录，包括'X'

in other words the expected output here would be 

"TUID"                        "FUND"                        "ORG"                         
"9102416AB"                   "1XXXXX"                      "6XXXXX"                      
"9102416CC"                   "100000"                      "67130"                       
"915522211"                   "1000XX"                      "67XXX"                       
"566653456"                   "xxxxxx"                      "xxxxx"

i have started to write a massive sql statement that would have like 12 like statement in it since i would have to compare the org and fund every possible way. This is where im headed. but im wondering if there is a better way.

我已经开始编写一个巨大的sql语句，它将有12个类似的语句，因为我必须要对org和基金进行比较。这就是我要去的地方。但我想知道有没有更好的办法。

select * from test02
where fund = '100000' and org = '67130'
or fund like '1%' and org like '6%'
or fund like '1%' and org like '67%'
or fund like '1%' and org like '671%'
or fund like '1%' and org like '6713%'
or fund like '1%' and org like '67130'
or fund like '10%' and org like '6%'...etc

/*seems like there should be a better way..*/

can anyone give me a hand coming up with this sql statement...

有人能帮我想出这个sql语句吗……

by the way notice that

顺便说一下

 "9148859fff"                  "1XXXXXX"                     "X6XXX"

is excluded from the expected resul since the second digit in org is "6" and im looking for anything that looks like "67130"

因为在org上第二个数字是“6”，而我在找任何看起来像“67130”的东西

3 个解决方案

#1

You can use the REPLACE() function to replace x with wildcard character _ and then use LIKE:

可以使用REPLACE()函数将x替换为通配符_，然后使用LIKE:

SELECT * 
FROM test01
WHERE '100000' LIKE REPLACE(REPLACE(fund, 'x', '_'), 'X', '_')
  AND  '67130' LIKE REPLACE(REPLACE(org,  'x', '_'), 'X', '_') ;

Tested at SQL-Fiddle

测试在SQL-Fiddle

#2

Assuming that you want to match both "X" and "x":

假设你想同时匹配“X”和“X”:

SELECT *
FROM Test01 t1
WHERE REGEXP_LIKE(t1.fund, '^[Xx1][Xx0][Xx0][Xx0][Xx0][X0x]$')  AND  
      REGEXP_LIKE(t1.org, '^[Xx6][Xx7][Xx1][Xx3][Xx0]$') 
;

My regular expressions control the total number of characters in each field: 6 in fund and 5 in org.

我的正则表达式控制了每个字段中的字符总数:6个在基金中，5个在org中。

Here's SQL Fiddle.

这是SQL小提琴。

#3

This might work

这可能工作

Select * from test01 t   
    where ((t.fund like ('1%') or t.fund like ('X%'))   
    and (t.org like ('6%') or t.org like ('X%'))   
    and t.org not Like('%6%')

#1