WITH tab AS( SELECT '1' num FROM dual UNION ALL SELECT '3' num FROM dual UNION ALL SELECT '6' num FROM dual UNION ALL SELECT 'a' num FROM dual UNION ALL SELECT 'bc' num FROM dual UNION ALL SELECT 'd' num FROM dual ) SELECT * FROM ( SELECT * FROM tab WHERE regexp_like(num,'[[:digit:]]') ) WHERE num<5结果: NUM ------- 1
你的记录是 id asd1 1select * from tb where translate(col,'asd',' ')+0<5select * from tb regexp_replace(col,'[[:alpha:]]','')<5还是 1 abc select * from tb where regexp_like(col,'[[:digit:]]')
自己搞定 了 我就纳闷了 为什么两个语句拼起来就报 无效数字 select dlzh 登陆账号,t.NAME 姓名 from v_wz_hyb t where 1=1 and t.WZ_HYB_ID in(select r.WZ_HYB_ID,r.barcode,y.xmmc,y.xmjg from wz_hy_reg r,V_wz_result_item_Num y where r.wz_hy_reg_id=y.wz_hy_reg_id and r.jglx=99 and r.djsj >= to_date('1910-11-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and r.djsj <=to_date('2010-11-10 23:59:59','yyyy-mm-dd hh24:mi:ss') and y.xmmc='低密度脂蛋白胆固醇(LDL)'and y.xmjg <5 )
WITH tab AS(
SELECT '1' num FROM dual UNION ALL
SELECT '3' num FROM dual UNION ALL
SELECT '6' num FROM dual UNION ALL
SELECT 'a' num FROM dual UNION ALL
SELECT 'bc' num FROM dual UNION ALL
SELECT 'd' num FROM dual
)
SELECT * FROM (
SELECT * FROM tab WHERE regexp_like(num,'[[:digit:]]')
)
WHERE num<5结果:
NUM
-------
1
上面结果贴错了,少贴了3结果:
NUM
-------
1
3
你的记录是
id
asd1
1select * from tb where translate(col,'asd',' ')+0<5select * from tb regexp_replace(col,'[[:alpha:]]','')<5还是
1
abc
select * from tb where regexp_like(col,'[[:digit:]]')
哪里嘛,晓得点皮毛而已,最近在学习正则表达式,我收藏了几篇网摘,minitoy空间转的,你可以看下,觉得还是很不错的。
select dlzh 登陆账号,t.NAME 姓名 from v_wz_hyb t where 1=1 and t.WZ_HYB_ID in(select r.WZ_HYB_ID,r.barcode,y.xmmc,y.xmjg from wz_hy_reg r,V_wz_result_item_Num y where r.wz_hy_reg_id=y.wz_hy_reg_id and r.jglx=99 and r.djsj >= to_date('1910-11-10 00:00:00','yyyy-mm-dd hh24:mi:ss') and r.djsj <=to_date('2010-11-10 23:59:59','yyyy-mm-dd hh24:mi:ss') and y.xmmc='低密度脂蛋白胆固醇(LDL)'and y.xmjg <5 )