MySQL 문자열에서 영숫자가 아닌 특수문자를 제거하는 방법

영숫자가 아닌 문자란?

영숫자(alphanumeric)가 아닌 문자란 알파벳과 숫자를 제외한 나머지 특수문자들을 의미하며, 대표적으로 다음과 같은 기호들이 있습니다.

@,!,#,&,(),?, /

사용자 정의 함수 생성하기

MySQL에는 문자열에서 영숫자가 아닌 문자를 한 번에 제거할 수 있는 내장 함수가 기본적으로 제공되지 않습니다. 따라서 이러한 문자들을 모두 걸러내는 사용자 정의 함수를 직접 생성해야 합니다. 아래는 'RemoveNonAlphaNumeric' 함수의 선언 및 정의 코드입니다.

mysql> delimiter //
mysql> CREATE FUNCTION RemoveNonAlphaNumeric( s CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
   -> BEGIN
   ->   DECLARE var1, length SMALLINT DEFAULT 1;
   ->   DECLARE result CHAR(255) DEFAULT '';
   ->   DECLARE ch CHAR(1);
   ->   SET length  = CHAR_LENGTH( s );
   ->   REPEAT
   ->     BEGIN
   ->       SET ch = MID( s, var1, 1 );
   ->       IF ch REGEXP '[[:alnum:]]' THEN
   ->         SET result =CONCAT(result ,ch);
   ->       END IF;
   ->       SET var1 = var1 + 1;
   ->     END;
   ->   UNTIL var1 >length  END REPEAT;
   ->   RETURN result ;
   -> END //
Query OK, 0 rows affected (0.10 sec)

함수의 작동 원리

이 함수는 입력받은 문자열을 MID() 함수로 한 글자씩 순회하면서, 각 문자가 정규 표현식 '[[:alnum:]]'(영숫자 패턴)에 해당하는 경우에만 결과 문자열에 추가하는 방식으로 동작합니다. 이 과정을 반복(REPEAT) 문으로 문자열 끝까지 수행한 뒤 최종 결과를 반환합니다.

함수 호출로 결과 확인하기

'RemoveNonAlphaNumeric' 함수는 문자열에서 영숫자가 아닌 모든 문자를 제거합니다. 실제로 잘 작동하는지 확인하기 위해 사용자 정의 함수를 호출해 보겠습니다.

mysql>delimiter ;
mysql>  select 'My Email id is test@123!',RemoveNonAlphaNumeric('My Email id is test@123!');

다음은 "RemoveNonAlphaNumeric" 함수를 적용한 결과입니다. 영숫자가 아닌 문자들이 성공적으로 제거된 것을 확인할 수 있습니다.

+--------------------------+---------------------------------------------------+
| My Email id is test@123! | removeNonAlphaNumeric('My Email id is test@123!') |
+--------------------------+---------------------------------------------------+
| My Email id is test@123! | MyEmailidistest123                                |
+--------------------------+---------------------------------------------------+
1 row in set (0.15 sec)

출력 결과인 'MyEmailidistest123' 문자열을 보면 원본에 포함되어 있던 @와 ! 기호가 더 이상 남아 있지 않습니다. 이는 함수가 의도대로 올바르게 작동하고 있음을 의미합니다. 이처럼 MySQL에서는 사용자 정의 함수와 정규 표현식을 활용하면 손쉽게 문자열을 정제할 수 있습니다.