[DB] MSSQL에서 문자열 처리하기

<서론>

프로젝트에서 사용하던 DB가 MSSQL로 되어 있어서 MSSQL을 사용해볼 수 있었다. 그러다보니 문자열을 자르거나, 문자열을 찾거나 하는 등의 처리를 하는 일이 있었는데, 자주 쓰기보다 어쩌다 한 번 나오는 경우라 기억하지 못하고 계속 구글링으로 찾아봤다. 그렇게 계속 찾기엔 귀찮아서 내 블로그에 정리하려 한다. 앞으로는 내 블로그 봐야지 히힛.

 

 

---

 

<본론>

1. 문자 길이 확인 (LEN, DATALENGTH)

1-1. 글자 수 확인

LEN( string_expression ) / return int
ex) LEN( 'Hello' ) / return 5

하지만 LEN은 자료형이 CHAR, VARCHAR인 경우에 사용할 수 있다(N이 붙어도 가능). 이 점을 잊고 쿼리를 짜다간 깊은 혼란에 빠질 수 있다.

 

1-2. byte 수 확인

DATALENGTH( expression ) / return int
ex) DATALENGTH( 'Hello' ) / return 5

입력 변수에 TEXT나 NTEXT 등도 올 수 있으며 심지어 IMAGE도 가능하다고 한다.

Byte를 체크하기 때문에 한글인 경우에는 글자 수보다 크게 나온다.

 

2. 문자열 자르기(LEFT, RIGHT, SUBSTRING)

2-1. 문자 앞(왼쪽)에서부터 자르기

LEFT( string_expression, integer_expression ) / return (varchar or nvarchar)
ex) LEFT( '123456', 3 ) / return '123'

자르기를 원하는 문자열을 첫번째 파라메터로 넣고, 몇글자 자를 건 지 숫자를 두번째 파라메터로 넣는다.

입력하는 문자열은 varchar, nvarchar, char, nchar 만 가능하다. text나 ntext는 사용할 수 없다.

입력하는 문자열이 유니코드인 문자 데이터 형식인 경우 nvarchar를 return

입력하는 문자열이 유니코드가 아닌 문자 데이터 형식인 경우 varchar를 return

 

2-2. 문자 뒤(오른쪽)에서부터 문자 자르기

RIGHT( string_expression, integer_expression ) / return (varchar or nvarchar)
ex) RIGHT( '123456', 3 ) / return '456'

문자 앞에서부터 자르는 방식과 동일하다.

 

2-3. 중간부터 자르기

SUSBSTRING( expression, start, length ) / return (varchar or nvarchar or varbinary)
ex) SUBSTRING( '123456', 3, 2 ) / return '34'

입력받은 문자열의 중간을 잘라낼 때 사용. 시작값은 0이 아니라 1이다.

입력할 수 있는 데이터 형식은 char, binary, text, image가 가능하다.

입력값의 데이터 형식에 따른 리턴값의 데이터 형식은 다음과 같다.

char, varchar, text / return varchar

nchar, nvarchar, ntext / return nvarchar

binary, varbinary, image / reutnr varbinary

 

3. 숫자형인지 확인(ISNUMERIC)

ISNUMERIC( expression ) / return int
ex) ISNUMERIC( '123456' ) / return 1
ex2) ISNUMERIC('123k456') / return 0

입력한 문자열이 숫자형으로 변환이 가능한 지 확인하는 함수.

입력한 문자열이 숫자형으로 변환이 가능한 경우 1을 불가능한 경우에는 0을 반환한다.

 

4. 문자열 치환(REPLACE)

REPLACE( string_expression, string_pattern, string_replacement ) / return (varchar or nvarchar)
ex) REPLACE( '123456789123', '123', 'test' ) / return 'test456789test'

문자열 치환은 텍스트 에디터에서 쓰는 기능과 동일하다. 다만 하나만 바꾸는 것이 아니라 찾은 패턴은 모두 치환해 버린다.

입력한 문자열이 varchar나 nvarchar의 최댓값을 초과하는 값이라면 반환값을 8,000 byte에서 자른다.

 

5. 문자열 찾기(CHARINDEX, PATINDEX)

CHARINDEX( expression_to_find, expression_to_search [, start_location] ) / return (varchar or bigint or int)
ex) CHARINDEX( '123', '123456789123' ) / return 1
ex2) CHARINDEX( '123', '123456789123', 2) / return 10

처음 입력값을 두번째 입력한 값 내에서 찾고 첫번째 위치를 반환하는 함수. 세번째 입력 값으로 검색하는 위치를 조정할 수도 있다. 찾는 값이 없을 경우, 0을 반환한다.

 

5-1. 패턴 처음 위치

PATINEDX( '%pattern%', expression ) / return (varchar or nvarchar or bigint or int)
ex) PATINDEX( '%123%', '123456789123' ) / return 1
ex) PATINDEX( '123', '123456789123' ) / return 0

CHARINDEX와 비슷하지만 패턴을 LIKE 처럼 찾는다. CHARINDEX와 마찬가지로 찾는 패턴이 없을 경우 0을 반환한다.

 

6. 문자열 끝의 공백 지우기(LTRIM, RTRIM)

6-1. 문자 앞(왼쪽) 공백 지우기

LTRIM( character_expression ) / return (varchar or nvarchar)
ex) LTRIM( '        he llo  ') / return 'he llo  '

문자 앞(왼쪽)에 있는 공백을 제거하는 함수. 문자 끝(오른쪽)이나 중간의 공백은 제거하지 않는다.

 

6-2. 문자 뒤(오른쪽) 공백 지우기

RTRIM( character_expression ) / return (varchar or nvarchar)
ex) LTRIM( '        he llo  ') / return '        he llo')

문자 앞(왼쪽)에 있는 공백을 제거하는 함수. 문자 끝(오른쪽)이나 중간의 공백은 제거하지 않는다.

 

번외. 문자열 어디에 있든 공백을 한꺼번에 지울 땐 REPLACE를 사용

 

7. 공백 넣기(SPACE)

SPACE( integer_expression ) / return varchar
ex) SPACE( 3 ) / return '   '

입력한 숫자만큼 공백을 반환하는 함수. 만약 숫자가 음수라면 Null 을 반환한다.

 

번외. 유니코드 데이터에 공백을 포함하거나 8,000개가 넘는 공백을 반환하려면 SPACE 대신 REPLICATE를 사용

 

8. ASCII 코드 변환(ASCII, CHAR)

ASCII( character_expression ) / return int
CHAR ( integer_expression / return char
ex) ASCII( 'A' ) / return 65
ex2) CHAR( 65 ) / return 'A'

ASCII와 문자로 변환하는 함수.

CHAR와 관련해서는 몇 가지 제어 문자를 삽입할 수 있다.

 

CHAR(9)   : 탭

CHAR(10: : 줄바꿈

CHAR(13) : 캐리지 리턴

 

9. 대소문자 변환(UPPER, LOWER)

UPPER( character_expression ) / return (varchar or nvarchar)
LOWER( character_expression ) / return (varchar or nvarchar)
ex) UPPER( 'AbcdE' ) / return 'ABCDE'
ex2) LOWER( 'AbcdE' ) / return 'abcde'

입력받은 문자열을 모두 대문자 혹은 소문자로 변경하는 함수. 대소문자가 섞여있는 문자열을 비교할 때, 대문자 혹은 소문자로 맞추기 위해 사용하기도 한다.

 

10. 문자 반복(REPLICATE)

REPLICATE( string_expression, integer_expression ) / return (string_expression과 같은 형식)
ex) REPLICATE( '0', 4 ) / return '0000'

원하는 문자열을 원하는 횟수만큼 반복하는 함수. 숫자로 이루어진 데이터의 앞에 원하는 만큼 0을 넣어 자리를 맞춰주는 데 쓰일 수 있다.

 

11. 문자열을 반대로(REVERSE)

REVERSE( string_expression ) / return (varchar or nvarchar)
ex) REVERSE( 'abcdEABcdeFg' ) / return 'gFedcBAEdcba'

입력한 문자열을 거꾸로 반환하는 함수. 대소문자도 당연히 구분한다.

 

---

 

<결론>

문자열과 관련된 내장함수가 적진 않지만 자주 쓴다면 못 외울 것도 없다. 하지만 이렇게 많은 종류를 어쩌다 한 번씩 쓰니 일일히 외우고 다니기엔 여간 귀찮은 일이 아니다. 그리고 이번 프로젝트가 끝난다면 언제 또 MSSQL을 만나게 될지도 알 수 없다. 오라클 열심히 하자.

 

 

<참고자료>

1. https://docs.microsoft.com/ko-kr/sql/t-sql/language-reference?view=sql-server-ver15