[SQL문] 숫자만 가져오기

[DB2]
http://translate.google.co.kr/translate?hl=ko&sl=en&tl=ko&u=http%3A%2F%2Fwww.dbforums.com%2Fdb2%2F1202593-check-numeric-db2-sql.html&anno=2
http://www.dbforums.com/db2/1202593-check-numeric-db2-sql.html

  ------------------------------ Commands Entered ------------------------------
 SELECT polnumber
      , CASE TRANSLATE(polnumber , '*' , ' 0123456789')
        WHEN '' THEN 'Valid.'
        ELSE 'Not valid.'
        END AS judge
  FROM (VALUES '1234'
              , '01234'
              , '012 34'
              , '012abc'
              , '2325128192'
              , ' 01234'
              , '01234 '
              , ' 01234 '
        ) policy(polnumber)
 ;
 ------------------------------------------------------------------------------

 POLNUMBER JUDGE     
 ---------- ----------
 1234 Valid.    
 01234 Valid.    
 012 34 Not valid.
 012abc Not valid.
 2325128192 Valid.    
   01234 Not valid.
 01234 Not valid.
  01234 Not valid.

   8 record(s) selected. 

http://blog.yahoo.com/_2ZBAUZR4EMVWRSQDNTHFMSNYEA/articles/174517

 

select LTRIM(TRANSLATE(컬럼명, TRANSLATE(컬럼명,'X0123456789','X'),' ')) from 테이블

select translate(str, translate(str, '.1234567890','A'), '1') 

from (

select 'C1.12.53A.a7.고b4 뮻 033 ' str from dual

)

select 

translate(SITENAME, translate(SITENAME, '1234567890','A'), '1') 

from IBQM_CELLDB_SITE

order by

select translate(SITENAME, translate(SITENAME, '1234567890','A'), '1') FROM IBQM_CELLDB_SITE 

order by substr( translate(SITENAME, translate(SITENAME, '1234567890','A'), '1') ,1,1),INSTR( translate(SITENAME, translate(SITENAME, '1234567890','A'), '1') ,'.',-1),to_number(substr( translate(SITENAME, translate(SITENAME, '1234567890','A'), '1') ,INSTR(SITENAME,'.',-1)+1));

substr( translate(SITENAME, translate(SITENAME, '1234567890','A'), '1') ,1,1),INSTR( translate(SITENAME, translate(SITENAME, '1234567890','A'), '1') ,'.',-1),to_number(substr( translate(SITENAME, translate(SITENAME, '1234567890','A'), '1') ,INSTR(SITENAME,'.',-1)+1))

http://webjoy.kr/259

오늘은 SQL 스크립트로 숫자만 뽑아내는 함수를 만들고 실행해보자

맨 아래의 사용자정의함수를 먼저 만들고 아래의 select 구문들을 실행해보면 문자는 빠지고 숫자만 나오게 됨을 알 수 있다.

select dbo.udf_findallnumbers ('12sfg34')
--results
--1234
select dbo.udf_findallnumbers ('asas123')
--results
--123
select dbo.udf_findallnumbers ('assd123jdh556jdfd4j56j78')
--results
--12355645678
select dbo.udf_findallnumbers ('sadasd')
--results
--0
select dbo.udf_findallnumbers ('sadasds65')
--results
--65
 
create table findnumber_test (name varchar(100))
insert into findnumber_test select 'A2b4b2b5bb6bb8bb9'
insert into findnumber_test select 'MAK9974'
insert into findnumber_test select 'Eiko36DKoike'
 
select dbo.udf_findallnumbers (name) from findnumber_test
--results
--2425689
--9974
--36

사용자정의 함수 만드는 SQL 스크립트 소스는 다음과 같다.

Create function dbo.udf_findallnumbers (@inputstring varchar(100)) returns bigint as begin --변수를 정의한다. declare @count1 smallint declare @len1 smallint declare @word varchar(100) declare @char1 char --변수에 값을 할당한다. set @word='' set @count1=1 set @len1 = datalength(@inputstring)           While @count1 <=@len1         begin         set @char1 =substring(@inputstring,@count1,1)         if ascii(@char1) between 48 and 57          begin         set @word=@word+substring(@inputstring,@count1,1)         end         set @count1=@count1+1         end return convert(bigint,@word)   end

출처 : korea.internet.com