MSSQL 데이터 수정(보정) 쿼리문

April 11, 2025

특정 문자 대체
이메일 유효값 보정
결론

웹 개발을 하다보면 디비이전을 하는 경우가 종종있다.

내가 작업하는 환경은 mysql(mariadb) 환경이라 같은 db사이에서는 무난하게 이전작업을 할 수 있긴하다.

하지만 외부 데이터를 이전할때 이전 db가 mssql인 경우도 정말 많다.

대표적으로 ASP로 구현된 윈도우 서버의 경우엔 mssql을 db로 쓰는 사이트가 많기때문이다.

mssql 자체가 애초에 구형 디비여서 mssql 위에서 무슨 작업을 한다기보다는 빠르게 추출해서 mysql로 옮긴다음에 작업하는게 편하긴하다.

대신 데이터 보정이 필요한 경우에는 이를 좀 미리 수정해둔다음 추출하는게 훨씬 편한 부분도 많다.

요즘은 GPT를 통해서 쉽게 쿼리문을 만들 수 있어서 참 편하긴한데, 많이 반복적으로 쓰는 쿼리문은 여기다가 기록해놓고 나중에 응용할 수 있도록 해보려고 한다.

특정 문자 대체

보통 전화번호 작업에서 나오는 보정작업 건이다.

옛날 홈페이지들은 전화번호를 그냥 일반 text로 받는경우가 많아서 "010-0000-0000" 처럼 데이터가 있는가하면 누구는 "010)0000-0000", "01000000000" 이런식으로 데이터를 저장하기도한다.

이를 보정하기위해서는 먼저 대쉬가 아닌 특수문자를 변경하는 쿼리문을 아래와 같이 작성해볼 수 있다.

SET Memb_MeHP = REPLACE(Memb_MeHP, ')', '-')
WHERE Memb_MeHP LIKE '%)%';
UPDATE member
SET Memb_MeHP = REPLACE(Memb_MeHP, '=', '-')
WHERE Memb_MeHP LIKE '%=%';
UPDATE member
SET Memb_MeHP = REPLACE(Memb_MeHP, ')', '-')
WHERE Memb_MeHP LIKE '%)%';Copy

그리고 변환된 데이터를 바탕으로 너무짧은 전번(잘못입력한경우)나 대쉬를 사용하지 않는 전번등을 검색해 추가 보정을 진행할 수 있다.

-- 너무 짧은 값 제거
SELECT *
FROM member
WHERE LEN(Memb_MeHP) < 8;

-- 전번 대쉬 보정
SELECT *
FROM member
WHERE Memb_MeHP NOT LIKE '%-%';Copy

특히 옛날에는 "0311234567", "03112345678"처럼 10자리 전화번호와 11자리 전화번호의 경우에는 글자수에 따라서 3-3-4, 3-4-4 구조로 넣는 업데이트문을 일일히 만들어서 사용했는데, 요즘은 이런 부분을 지피티한테 시켜서 변환된 데이터를 다시 직접 갱신해주면되서 겁나 편해졌다.

그래도 초대량데이터의 경우엔 아직도 쿼리문으로 처리해야하는 부분이 있긴하지만 말이다.

UPDATE users
SET phone = 
    CASE 
        WHEN LEN(phone) = 10 THEN
            SUBSTRING(phone, 1, 3) + '-' + SUBSTRING(phone, 4, 3) + '-' + SUBSTRING(phone, 7, 4)
        WHEN LEN(phone) = 11 THEN
            SUBSTRING(phone, 1, 3) + '-' + SUBSTRING(phone, 4, 4) + '-' + SUBSTRING(phone, 8, 4)
        ELSE
            phone -- 10자리, 11자리가 아닌 경우는 변경하지 않음
    END
WHERE LEN(phone) IN (10, 11);Copy

위 기능을 유사하게 쿼리문으로 만든다면 이런느낌으로 만들 수 있을것이다.

이메일 유효값 보정

보통 회원계정찾기를하면 가장많이 쓰는 폼이 이메일과 휴대번호다.

그래서 이 두가지는 이전하기전에 꼭 유효값인지 처리하는 습관이 나한텐 있다.

이메일의 경우에는 당연히 악의적인 가입 이메일을 걸러내는 작업도해야하지만, 원초적으로 이메일란에 이메일형식을 취하지 못한 데이터를 걸러내는게 1순위다.

-- 이메일 보정
SELECT *
FROM member
WHERE Memb_Mail IS NOT NULL
  AND (
    Memb_Mail NOT LIKE '%@%.%'
        OR Memb_Mail LIKE '%@%@%'
        OR Memb_Mail LIKE '@%'
        OR Memb_Mail LIKE '%@'
        OR Memb_Mail LIKE '%.@%'
        OR Memb_Mail LIKE '%@%.'
        OR LEN(Memb_Mail) < 5
    );Copy

이런경우는 위와같은 셀렉문을 통해서 잘못된 이메일을 추려낼 수 있고, 해당 레코드를 나는 삭제하는 방식으로 보정하고 있다.

결론

이 개발기록을 작성한 이유는 내가 평소에 자주쓰는 mysql이 아닌 mssql에서의 쿼리문을 기록하기 위해서다.

아무리 둘다 기본적은 sql 쿼리문의 형식을 띈다해도 조금만 복잡한 단계로 넘어가면 은근히 사용할 수 있는 함수명이 다르다던지, 자료형의 제약이 존재하기 때문이다.

항목	MySQL	MSSQL (SQL Server)
문자열 길이 계산	CHAR_LENGTH( ), LENGTH( )	LEN( )
문자열 연결	CONCAT(str1, str2)	+ or CONCAT(str1, str2)
조건문	IF(condition, true_value, false_value)	CASE WHEN condition THEN true_value ELSE false_value END
현재 날짜/시간	NOW( )	GETDATE( )
AUTO_INCREMENT	AUTO_INCREMENT	IDENTITY(1, 1)
주석	-- or # or /* */	-- or /* */