[MSSQL] 문자열에서 숫자만 추출하는 2가지 방법

SQL Server에서는 문자열에서 숫자만 추출하기 위해서는 SUBSTRING, PATINDEX 함수를 사용하여 숫자 부분을 자르거나, TRANSLATE 함수를 사용하여 문자를 공백으로 치환하여 숫자만 추출하는 방법이 있다. MSSQL에서는 REGEXP_REPLACE 같은 정규식 함수가 없기 때문에 위의 방법을 사용하거나, 문자열의 추출하는 사용자 함수를 생성하여 숫자를 추출해야 한다. 아래의 예제는 문자열에 하나의 숫자만 존재할 경우 유효한 방법이니 이점을 유의하여 참고하기 바란다.

 

목차 문자열 자르기로 숫자를 추출하는 방법 문자 치환을 사용하여 숫자를 추출하는 방법

 

문자열 자르기로 숫자를 추출하는 방법

WITH SampleData  AS (
    SELECT 'cc_7000'     AS StrVal UNION ALL
    SELECT 'dd_10000_aa' AS StrVal UNION ALL
    SELECT '8000 bb'     AS StrVal
)

SELECT StrVal
     , CASE WHEN PATINDEX('%[^0-9]%', StrVal) = 0 THEN StrVal
            ELSE SUBSTRING( StrVal
                          , PATINDEX('%[0-9]%', StrVal)
                          , PATINDEX('%[^0-9]%', SUBSTRING(StrVal + ' ', PATINDEX('%[0-9]%', StrVal), LEN(StrVal))) - 1
                          )
       END AS NumVal    
  FROM SampleData

 

 

문자열에 숫자가 아닌 문자열이 포함되어 있을 경우 PATINDEX 함수를 사용하여 숫자의 위치를 찾아서 SUBSTRING 함수를 숫자만 잘라서 추출하는 예제이다.

 

위의 방법은 문자열에 1개의 숫자만 있을 경우 정상적으로 작동하며, 여러 개의 숫자가 있을 경우 첫 번째 숫자만 추출되니 유의해서 사용해야 한다.

 

문자 치환을 사용하여 숫자를 추출하는 방법

WITH SampleData  AS (
    SELECT 'cc_7000'     AS StrVal UNION ALL
    SELECT 'dd_10000_aa' AS StrVal UNION ALL
    SELECT '8000 bb'     AS StrVal
)

SELECT StrVal
     , LTRIM(RTRIM(TRANSLATE(StrVal 
                            ,'abcdefghijklmnopqrstuvwxyz_ '
                            ,'                            '
       ))) AS NumVal
  FROM SampleData

 

 

문자열에 제거해야 할 문자의 범위가 제한적일 경우 위의 방법을 사용하면 조금 더 간편하게 숫자만 추출할 수 있다. 그러나 제거해야 할 문자의 범위가 광범위하다면 위의 방법은 비효율적일 수 있다.

 

위의 방법은 문자열에 숫자가 여러 개 존재할 경우 정상적으로 작동하지 않을 수 있으니 유의해서 사용해야 한다.

 

[MSSQL] 문자열 자르기 (SUBSTRING, LEFT, RIGHT)

[MSSQL] PATINDEX 함수 사용법 (패턴 위치 찾기)

[MSSQL] TRANSLATE 함수 사용법 (여러개 치환, 다중 치환)