데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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
1990 클러스터된 SQL 서버 인스턴스에 대한 호스트 이름 확인 jevida(강성욱) 2017.01.11 1366
1989 sys.dm_tran_locks 를 이용한 잠금 정보 확인 jevida(강성욱) 2017.01.11 1523
1988 외래키 제약 조건 삭제 후 재작성 스크립트 생성하기 jevida(강성욱) 2017.01.11 1779
1987 페이지 ID로 테이블 이름 찾기 jevida(강성욱) 2017.01.11 1434
1986 DBCC CHECKPRIMARYFILE 사용법 jevida(강성욱) 2017.01.11 1326
1985 컬럼스토어 인덱스 대용량 데이터 로드 jevida(강성욱) 2017.01.11 1743
1984 컬럼스토어 인덱스 INSERT 작업과 동시성 jevida(강성욱) 2017.01.11 1491
1983 컬럼스토어 인덱스 동시성 jevida(강성욱) 2017.01.11 1780
1982 컬럼스토어 인덱스 ROW와 ROWGROUP 영향 jevida(강성욱) 2016.11.23 2943
1981 테이블 변수와 TF 2453 jevida(강성욱) 2016.11.23 3184
1980 Sp_trace_create MaxfileSize 오류 jevida(강성욱) 2016.11.23 2393
1979 RANDBETWEEN 함수 만들기 jevida(강성욱) 2016.11.23 4578
1978 Optimize for hint 쿼리 최적화 jevida(강성욱) 2016.11.23 3509
1977 TempDB 파일 사이즈 증가 시 경고 받기 jevida(강성욱) 2016.11.23 3044
1976 블록킹 세션을 찾아 우선순위 낮은 세션 종료하기 jevida(강성욱) 2016.11.23 3243
» 다양한 포맷의 이름 파싱 하기 jevida(강성욱) 2016.11.23 2662
1974 비결정적 사용자 정의 함수 사용으로 인한 느린 쿼리 jevida(강성욱) 2016.11.23 3148
1973 스냅숏 격리 수준(SNAPSHOT ISOLATION LEVEL) jevida(강성욱) 2016.11.23 4248
1972 SQL Server 파라메터 스니핑의 다양한 접근 jevida(강성욱) 2016.11.23 3120
1971 잘못된 개체를 참조하는 SQL Server 개체 찾기 jevida(강성욱) 2016.11.23 1940





XE Login