有一个表结构如下:userID userName email
001 aaa [email protected]
002 bbb [email protected]
003 ccc [email protected]
004 ddd [email protected]
005 eee [email protected]
就一个表,我想得到跟userID=003用户所用的email地址@后面一样的所有的用户名。
这个模糊查询该怎么写?
关键就是单引号没整明白。
备注:只用一个SQL,不能用接字符串比较的形式,只想用like模糊查询,因为我要用到这个处理,就用上面那个做例子了!。
001 aaa [email protected]
002 bbb [email protected]
003 ccc [email protected]
004 ddd [email protected]
005 eee [email protected]
就一个表,我想得到跟userID=003用户所用的email地址@后面一样的所有的用户名。
这个模糊查询该怎么写?
关键就是单引号没整明白。
备注:只用一个SQL,不能用接字符串比较的形式,只想用like模糊查询,因为我要用到这个处理,就用上面那个做例子了!。
select userid,username,email
from tb where userid='003' and email like '_[126.com]'
select userid,username,email
from tb where userid='003' and email like '_126.com'
SQL> with t as(
2 select '001' userID,'aaa' userName,'[email protected]' email from dual union all
3 select '002','bbb','[email protected]' from dual union all
4 select '003','ccc','[email protected]' from dual union all
5 select '004','ddd','[email protected]' from dual union all
6 select '005','eee','[email protected]' from dual
7 )
8 select userID,email
9 from t
10 where email like '%126%';USERID EMAIL
------ ------------
003 [email protected]
005 [email protected]
--126.com 通过下面的sql获取
select substrb(email,instrb(email,'@',1) + 1) from tb where userid='003';
with t as(
select '001' userID,'aaa' userName,'[email protected]' email from dual union all
select '002','bbb','[email protected]' from dual union all
select '003','ccc','[email protected]' from dual union all
select '004','ddd','[email protected]' from dual union all
select '005','eee','[email protected]' from dual)
select userID,userName,email
from t
where email like '%126.com';
/*
USERID USERNAME EMAIL
------ -------- ------------
003 ccc [email protected]
005 eee [email protected]
*/
我想得到跟userID=003用户所用的email地址@后面一样的所有的用户名。
如果 userid=003 但是@163.com 只有一个 .其余全是@126.com
我是取@126.com一样的呢 还是 取@163.com的呢?
( "USER_ID" VARCHAR2(20 BYTE),
"USER_NAME" VARCHAR2(20 BYTE),
"EMAIL" VARCHAR2(20 BYTE)
) TABLESPACE "USERS" ;
SQL> conn scott/tiger
已连接。
SQL> select user_name
2 from TEST_REGEX
3 where email like '%'||regexp_substr(
4 (
5 select email
6 from TEST_REGEX
7 where user_id='003'
8 )
9 ,'@.+'
10 );USER_NAME
--------------------
ccc
eee
select user_name
from TEST_REGEX
where email like '%'||regexp_substr(
(
select email
from TEST_REGEX
where user_id='003'
)
,'@.+'
)
FROM user_table a
WHERE EXISTS (
SELECT '*'
FROM user_table
WHERE SUBSTR (email, INSTR (email, '@') + 1) = SUBSTR (a.email, INSTR (a.email, '@') + 1)
AND userID = '003')
select * from table_name where regexp_like (email,'@126\.com','i');
万一userId=003的emial变成[email protected]了,岂不要修改sql?
SQL>
SQL> with t as(
2 select '001' userID,'aaa' userName,'[email protected]' email from dual union all
3 select '002','bbb','[email protected]' from dual union all
4 select '003','ccc','[email protected]' from dual union all
5 select '004','ddd','[email protected]' from dual union all
6 select '005','eee','[email protected]' from dual)
7 select t.*
8 from t,(select substr(email,instr(email,'@',-1)+1) email from t where userid='003') tb
9 where regexp_instr(t.email,'@'||tb.email)>0
10 /
USERID USERNAME EMAIL
------ -------- ------------
003 ccc [email protected]
005 eee [email protected]
SQL>
select username from table where substr(instr(email,'@')+1,LENGTH(email)) =
(select substr(instr(email,'@')+1,LENGTH(email)) from table where userName = '003')
(select substr(email,instr(email,'@')) from testt where userid = 3);
userID userName email
001 aaa [email protected]
002 bbb [email protected]
003 ccc [email protected]
004 ddd [email protected]
005 eee [email protected] t2.* from table1 t1,table1 t2
where t1.userID = '003' and
t2.email like '%'||substr(t1.email,instr(t1.email,'@')+1)
题目可能没写明白,此处userId是从前台动态输入的,
做沙发者都有点理解错误!
不过先谢谢各位了,7楼是我最想要的结果!!
其他也有好多答案挺好的,为啥我当时就想不出来呢~~
先结贴了~~谢谢各位!!