¾È³çÇϼ¼¿ä.
¿À¶óŬ¿¡¼ ¿©·¯°¡Áö ÆÐÅÏÀÇ ¹®ÀÚ¿À» CASEº°·Î ÃßÃâÇغ¸·Á Çϴµ¥ À߾ȵǼ Áú¹®µå¸³´Ï´Ù.
1. ¾Æ·¡¿Í °°Àº ¹®ÀÚ¿Áß¿¡¼ »¡°£»öÀÇ ¼ýÀÚ¸¸ ÃßÃâÇÑ ÈÄ,
tel:0123456789;asdfasdfasdfasdf
tel:0123456789;xcvbxcvb123132xzcv
tel:234567891011;afasa123df13
tel:114;z1231cv
tel:114;asdfwessf123r
sip:34567891011@qwrsd123f
sip:56789101112@asdfasd123safas
sip:567891011124566@fczvzx6546cvhth
2. °á°ú´Â ¾Æ·¡¿Í °°ÀÌ ¹øÈ£¿Í Áߺ¹countµÇµµ·Ï Ãâ·ÂµÇ°Ô ÇÏ°í ½ÍÀºµ¥ SQL Äõ¸®¹®À» ¾Ë·ÁÁÖ½Ã¸é °¨»çÇÏ°Ú½À´Ï´Ù.
case, substr, instrÀ» »ç¿ëÇؼ Çغ¸°í Àִµ¥ À߾ȵdz׿ä.
¹øÈ£ count
0123456789 2
234567891011 1
114 2
34567891011 1
56789101112 1
567891011124566 1
|
FROM (SELECT SUBSTR(Ä÷³, INSTR(Ä÷³, ':') + 1, (INSTR(Ä÷³, ';') - INSTR(Ä÷³, ':') - 1))
FROM Å×À̺í
)
GROUP BY ¹øÈ£;
INSTR ÇÔ¼ö·Î ƯÁ¤¹®ÀÚ ±âÁØÀ¸·Î SUBSTRÇؼ µ¥ÀÌÅÍ ÃßÃâÇÏ¸é µÇ´Â µí Çϳ׿ä.
INLINE VIEW¿¡ °¡°øÄ÷³¿¡ ´ëÇÑ Ä÷³¸íÀ» ±â¼úÇÏÁö ¾Ê¾Æ¼ ¿¡·¯°¡ ¹ß»ýÇß³ªº¸³»¿ä.
@ǪÇÖ
´äº¯ °¨»çÇÕ´Ï´Ù. AS¹øÈ£¸¦ Ãß°¡Çϴϱî Àߵ˴ϴÙ. ^^
±×·±µ¥ À§ °æ¿ì´Â tel:0123456789;asdfasdfasdfasdf ¸¸ °¡´ÉÇѵ¥
sip:56789101112@asdfasd123safas ÀÎ °æ¿ì¿¡µµ °°ÀÌ Çѹø¿¡ ÃßÃâÇÒ ¼ö ÀÖ´Â ¹æ¹ýÀÌ ÀÖÀ»±î¿ä?
@eizo Á¤±Ô½Ä ¾²¼¼¿ä
select regexp_substr(iparam_to,'[0-9]+',1,1)
, count(*)
from Å×À̺í
group by regexp_substr(iparam_to,'[0-9]+',1,1)