Êý¾Ý¿âÖÐÓиö×Ö¶ÎQZFWÊÇvarcharÀàÐÍ£¬´æ·ÅµÄÊý¾Ý¸ñʽÊÇ"Êý×Ö±àºÅ"-"Êý×Ö±àºÅ" È磺0012-1345 ÏÖÔÚÎÒÏëÊäÈë¸öÌõ¼þa,ʹ×Ö¶ÎÂú×ã QZFWÖÐ'-'Ç°ÃæµÄÊý×Ö<= a <= QZFWÖС®-¡¯ºóÃæµÄÊý×Ö¡£¡£
ÎÒдµÄsqlÈçÏ£º55000ÊÇÌõ¼þa,,,
select * from (select to_number(substr(QZFW,0,instr(QZFW,'-')-1)) nbefore,
to_number(substr(QZFW,instr(QZFW,'-')+1,length(QZFW))) nafter,
QZFW as qzfw_C from TBL_01 Where translate(QZFW,'\1234567890-','\') is null and (length(QZFW) - length(replace(QZFW,'-')))/length('-')=1) t
where t.nbefore<=55000 and t.nafter >=55000
µ«ÊÇÈç¹ûÊäÈëÌõ¼þÌ«´óÈ磺304000582 ¾Í»á±¨¡°ORA-01722: ÎÞЧÊý×Ö¡±´íÎ󡣡£¡£
Ìõ¼þСµÄ»°²»»á±¨Â𣿻᲻»áÊÇÄãµÄÊý¾ÝÓÐÎÊÌâÄØ
¿ÉÄÜÄãµÄQZFW³ýÁË'-'ÏßÍ⣬²»ÄÜת»»ÎªÊý×ֵķǷ¨Êý¾Ý¡£
ÁíÍ⣬Õâ¸öÈ¡ºóÃæ²¿·ÖµÄÊý×Ö£¬²»ÐèÒªlength(QZFW)
to_number(substr(QZFW,instr(QZFW,'-')+1))
Ìõ¼þС²»»á±¨´í¡£¡£Êý¾ÝÎÒ¶¼¼ì²é¹ýÁË£¬¶¼Ã»ÓÐÎÊÌâ¡£¡£
translate(QZFW,'\1234567890-','\') is null ¾ÍÊǽ«ËùÓÐµÄ Êý×Ö-Êý×Ö ¸ñʽµÄÊý¾Ý¶¼²é³öÀ´£¬½«º¬ÆäËû×Ö·ûµÄ¹ýÂ˵ô
(length(QZFW) - length(replace(QZFW,'-')))/length('-')=1) Õâ¸öµÄÄ¿µÄÊÇÈ·±£ QZFW×ֶΰüº¬Ò»¸ö'-'×Ö·û
(length(QZFW) - length(replace(QZFW,'-')))/length('-')=1) Õâ¸öµÄÄ¿µÄÊÇÈ·±£ QZFW×ֶΰüº¬Ò»¸ö'-'×Ö·û
Êý¾ÝÓ¦¸ÃÓÐÎÊÌ⣡
ÊÇ·ñ´æÔÚ£º
-8345
345-
ÕâÑùµÄÊý¾Ý£¿£¿£¿£¿£¿
ÊÔÏÂÔö¼ÓÌõ¼þ£º
and instr(QZFW,'-') > 1
and instr(QZFW,'-') <