다양한 포맷의 이름 파싱 하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

비즈니스 파트마다 다양한 형식의 이름(또는 전화번호) 세트를 사용한다. 이러한 경우 성과 이름을 어떻게 구분하여 정리 업무를 수행하는 사람이라면 한 번쯤 고민해보았을 것이다.

 

이번 시간에는 구분 할 수 있는 특정한 기호를 기준으로 이름을 파싱하여 사용하는 방법에 대해서 알아본다.

 

[공백으로 구분된 경우]

성과 이름 사이에 공백이 있는 포맷을 파싱하여 사용하는 방법이다. 실습용 테이블을 생성하고 데이터를 입력 한다.

-- create temporary table for storing source name strings

-- and their associated name parts

IF OBJECT_ID('tempdb..##NamesFromDifferentClients_1') IS NOT NULL

DROP TABLE ##NamesFromDifferentClients_1

 

CREATE TABLE ##NamesFromDifferentClients_1

(

ID BIGINT IDENTITY(1,1),

ClientID varchar(20),

SourceString varchar(75),

FirstName varchar(20),

MiddleName varchar(20),

LastName varchar(30),

Suffix varchar(5)

)

 

-- populate temporary table with source name strings

INSERT INTO ##NamesFromDifferentClients_1

(

ClientID,

SourceString

)

VALUES

('A', 'Tim Bits'),

('A', 'Ken dePaul Jones'),

('A', 'Sally S Cats'),

('A', 'Mike George Mountains JR')

 

다음 스크립트를 실행하면 공백을 기준으로 파싱할 문자열의 길이를 확인 할 수 있다.

SELECT

ID, ClientID, SourceString,

CHARINDEX(' ',SourceString,1) space_1_loc,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1) space_2_loc,

CASE

WHEN

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)=0

THEN 0

ELSE

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)

+1)

END space_3_loc

FROM ##NamesFromDifferentClients_1

 

 

파싱할 문자열의 길이에 따라 문자를 추출하고 공백을 제거하면 성과 이름을 구분한다.

-- map extracted strings to name parts

SELECT

ID

,ClientID

,SourceString

,first_string FirstName

,CASE

WHEN third_string = '' THEN CAST('' AS varchar(20))

ELSE CAST(second_string AS varchar(20))

END MiddleName

,CASE

WHEN third_string = '' THEN CAST(second_string AS varchar(30))

ELSE CAST(third_string AS varchar(30))

END LastName

-- Remove leading blank

,LTRIM(CASE

WHEN fourth_string = '' THEN CAST('' AS varchar(5))

ELSE CAST(fourth_string AS varchar(5))

END) Suffix

FROM

(

-- extract strings

SELECT

ID

,ClientID

,SourceString

,LEFT(SourceString,space_1_loc-1) first_string

,CASE

WHEN space_2_loc > 0

THEN

CAST(

SUBSTRING(SourceString,

space_1_loc+1,space_2_loc-space_1_loc)

AS varchar(20)

)

ELSE

CAST(

RIGHT(SourceString,

DATALENGTH(SourceString)-space_1_loc)

AS varchar(20)

)

END second_string

,CASE

WHEN space_2_loc = 0

THEN CAST('' AS varchar(30))

WHEN space_3_loc = 0

THEN SUBSTRING(SourceString,space_2_loc+1,

DATALENGTH(SourceString))

WHEN space_3_loc > 0

THEN

CAST(

SUBSTRING(SourceString,

space_2_loc+1,

space_3_loc-space_2_loc)

AS varchar(30)

)

END third_string

,CASE

WHEN space_2_loc = 0

THEN CAST('' AS varchar(30))

WHEN space_3_loc = 0

THEN CAST('' AS varchar(30))

ELSE

CAST(

SUBSTRING(SourceString,

space_3_loc,

DATALENGTH(SourceString))

AS varchar(5))

END fourth_string

FROM

(

-- extract delimiter locations

SELECT

ID, ClientID, SourceString,

CHARINDEX(' ',SourceString,1) space_1_loc,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1) space_2_loc,

CASE

WHEN

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)=0

THEN 0

ELSE

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)

+1)

END space_3_loc

FROM ##NamesFromDifferentClients_1

) SourceAndDelimiters

) SourceAndExtractedStrings

 

 

 

[콤마와 공백으로 구분된 경우]

성에는 콤마와 공백으로 구분되어 있고 중간 이름과 마지막 이름은 공백으로 구분되어 있는 경우이다. 실습용 테이블을 생성하고 데이터를 입력 한다.

-- create temporary table for storing source name strings

-- and their associated name parts

IF OBJECT_ID('tempdb..##NamesFromDifferentClients_1') IS NOT NULL

DROP TABLE ##NamesFromDifferentClients_1

 

CREATE TABLE ##NamesFromDifferentClients_1

(

ID BIGINT IDENTITY(1,1),

ClientID varchar(20),

SourceString varchar(75),

FirstName varchar(20),

MiddleName varchar(20),

LastName varchar(30),

Suffix varchar(5)

)

 

INSERT INTO ##NamesFromDifferentClients_1

(

ClientID,

SourceString

)

VALUES

('B', 'Bits, Tim'),

('B', 'Jones, Ken dePaul'),

('B', 'Cats, Sally S'),

('B', 'Mountains JR, Mike George'),

('B', 'Mountains, Mary Anne Bits')

 

다음 스크립트를 실행하면 콤마와 공백을 기준으로 파싱할 문자열의 길이를 확인 할 수 있다.

SELECT

ID, ClientID, SourceString,

CHARINDEX(',',SourceString,1) comma_loc,

CHARINDEX(' ',SourceString,1) space_1_loc,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1) space_2_loc,

CASE

WHEN

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)=0

THEN 0

ELSE

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)

+1)

END space_3_loc

FROM ##NamesFromDifferentClients_1

 

 

콤마의 위치와 공백의 위치를 참고 하여 문자열을 파싱 한다.

SELECT

ID

,ClientID

,SourceString

,CASE

WHEN space_3_loc = 0 THEN second_string

WHEN comma_loc < space_1_loc AND space_3_loc > 0

THEN second_string + ' ' + third_string

ELSE third_string

END FirstName

,LTRIM(CASE

WHEN space_2_loc = 0 THEN ''

WHEN space_3_loc = 0 THEN third_string

ELSE fourth_string

END) MiddleName

,first_string LastName

,CASE

WHEN comma_loc > space_1_loc THEN second_string

ELSE ''

END Suffix

FROM

(

-- extract strings

SELECT

ID

,ClientID

,SourceString

,comma_loc

,space_1_loc

,space_2_loc

,space_3_loc

-- Remove trailing comma from either first or second string

,REPLACE(LEFT(SourceString,space_1_loc-1)

,',','') first_string

,REPLACE(

CASE

WHEN space_2_loc > 0

THEN

CAST(

SUBSTRING(SourceString,

space_1_loc+1,space_2_loc-space_1_loc)

AS varchar(20)

)

ELSE

CAST(

RIGHT(SourceString,

DATALENGTH(SourceString)-space_1_loc)

AS varchar(20)

)

END,

',','') second_string

,CASE

WHEN space_2_loc = 0 THEN CAST('' AS varchar(30))

WHEN space_3_loc = 0 THEN

SUBSTRING(SourceString,

space_2_loc+1,DATALENGTH(SourceString))

WHEN space_3_loc > 0 THEN

CAST(

SUBSTRING(SourceString,

space_2_loc+1,

space_3_loc-space_2_loc)

AS varchar(30)

)

END third_string

,CASE

WHEN space_2_loc = 0 THEN CAST('' AS varchar(30))

WHEN space_3_loc = 0 THEN CAST('' AS varchar(30))

ELSE

CAST(

SUBSTRING(SourceString,

space_3_loc,

DATALENGTH(SourceString))

AS varchar(20))

END fourth_string

FROM

(

-- extract delimiter locations

SELECT

ID, ClientID, SourceString,

CHARINDEX(',',SourceString,1) comma_loc,

CHARINDEX(' ',SourceString,1) space_1_loc,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1) space_2_loc,

CASE

WHEN

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)=0

THEN 0

ELSE

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)

+1)

END space_3_loc

FROM ##NamesFromDifferentClients_1

) SourceAndDelimiters

) SourceAndExtractedStrings

 

 

 

[이름의 길이가 다른고 다양한 포맷이 혼합된 경우]

일반적인 형식과 다른 이름 형식이거나 다양한 형식의 포맷이 섞여 있는 경우이다.

-- create temporary table for storing source name strings

-- and their associated name parts

IF OBJECT_ID('tempdb..##NamesFromDifferentClients_1') IS NOT NULL

DROP TABLE ##NamesFromDifferentClients_1

 

CREATE TABLE ##NamesFromDifferentClients_1

(

ID BIGINT IDENTITY(1,1),

ClientID varchar(20),

SourceString varchar(75),

FirstName varchar(20),

MiddleName varchar(20),

LastName varchar(30),

Suffix varchar(5)

)

 

INSERT INTO ##NamesFromDifferentClients_1

(

ClientID,

SourceString

)

VALUES

('B', 'Bits, Tim'),

('B', 'Jones, Ken dePaul'),

('B', 'Cats, Sally S'),

('B', 'Mountains JR, Mike George'),

('B', 'Mountains, Mary Anne Bits')

 

/*

String formats

first_string, second_string

as LastName, FirstName

first_string, second_string third_string

as LastName, FirstName MiddleName

first_string second_string, third_string fourth_string

as LastName Suffix, FirstName MiddleName

first_string, second_string third_string fourth_string

as LastName, FirstName MiddleName

*/

 

SELECT

ID

,ClientID

,SourceString

,CASE

WHEN space_3_loc = 0 THEN second_string

WHEN comma_loc < space_1_loc AND space_3_loc > 0

THEN second_string + ' ' + third_string

ELSE third_string

END FirstName

,LTRIM(CASE

WHEN space_2_loc = 0 THEN ''

WHEN space_3_loc = 0 THEN third_string

ELSE fourth_string

END) MiddleName

,first_string LastName

,CASE

WHEN comma_loc > space_1_loc THEN second_string

ELSE ''

END Suffix

FROM

(

-- extract strings

SELECT

ID

,ClientID

,SourceString

,comma_loc

,space_1_loc

,space_2_loc

,space_3_loc

-- Remove trailing comma from either first or second string

,REPLACE(LEFT(SourceString,space_1_loc-1)

,',','') first_string

,REPLACE(

CASE

WHEN space_2_loc > 0

THEN

CAST(

SUBSTRING(SourceString,

space_1_loc+1,space_2_loc-space_1_loc)

AS varchar(20)

)

ELSE

CAST(

RIGHT(SourceString,

DATALENGTH(SourceString)-space_1_loc)

AS varchar(20)

)

END,

',','') second_string

,CASE

WHEN space_2_loc = 0 THEN CAST('' AS varchar(30))

WHEN space_3_loc = 0 THEN

SUBSTRING(SourceString,

space_2_loc+1,DATALENGTH(SourceString))

WHEN space_3_loc > 0 THEN

CAST(

SUBSTRING(SourceString,

space_2_loc+1,

space_3_loc-space_2_loc)

AS varchar(30)

)

END third_string

,CASE

WHEN space_2_loc = 0 THEN CAST('' AS varchar(30))

WHEN space_3_loc = 0 THEN CAST('' AS varchar(30))

ELSE

CAST(

SUBSTRING(SourceString,

space_3_loc,

DATALENGTH(SourceString))

AS varchar(20))

END fourth_string

FROM

(

-- extract delimiter locations

SELECT

ID, ClientID, SourceString,

CHARINDEX(',',SourceString,1) comma_loc,

CHARINDEX(' ',SourceString,1) space_1_loc,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1) space_2_loc,

CASE

WHEN

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)=0

THEN 0

ELSE

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,

CHARINDEX(' ',SourceString,1)+1)

+1)

END space_3_loc

FROM ##NamesFromDifferentClients_1

) SourceAndDelimiters

) SourceAndExtractedStrings

 

 

 

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3283/name-parsing-for-result-sets-with-different-name-formats/

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 34970
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 17247
1974 비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 jevida(강성욱) 2016.11.23 3226
1973 스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL) jevida(강성욱) 2016.11.23 4667
1972 SQL Server 파라메터 스니핑의 다양한 접근 jevida(강성욱) 2016.11.23 3551
1971 잘못된 개체를 참조하는 SQL Server 개체 찾기 jevida(강성욱) 2016.11.23 2076
1970 전체 백업에서 포함되는 트랜잭션 범위 jevida(강성욱) 2016.11.23 2684
1969 REPL_SCHEMA_ACCESS 대기 유형 jevida(강성욱) 2016.11.23 2213
1968 Database 손상시 Emergency 모드로 복구하기 jevida(강성욱) 2016.11.23 3134
1967 서버 그룹을 이용한 다중서버 쿼리하기 jevida(강성욱) 2016.11.23 2160
1966 SQL Server Agent에서 CmdExec 오류 jevida(강성욱) 2016.11.23 2411
1965 Verbose SQL Server Agent Logging jevida(강성욱) 2016.11.23 1783
1964 Windows Event Log에 SQL Server Agent Log 기록 jevida(강성욱) 2016.11.23 2413
1963 SQL Server Agent Error log 위치 변경 jevida(강성욱) 2016.11.23 2646
1962 SQL Server에서 차단을 확인하는 다양한 방법 jevida(강성욱) 2016.10.18 4805
1961 SQL Server CPU 사용률이 높은 프로세서 및 쿼리 찾기 jevida(강성욱) 2016.10.18 7333
1960 Collation 변경 jevida(강성욱) 2016.10.18 4755
1959 인덱스 리빌드 동작 (Gather Streams from SORT) jevida(강성욱) 2016.10.18 3540
1958 최소한의 다운타임으로 데이터베이스 이동하기 jevida(강성욱) 2016.10.18 3178
1957 백업 LSN 이해하기 jevida(강성욱) 2016.10.18 3506
1956 암호화 오버헤드 (작성자의 주관적인 자료임) jevida(강성욱) 2016.10.18 2898
1955 데이터베이스 연결 정보 수집 jevida(강성욱) 2016.10.18 3238





XE Login