[SQL] 특정 문자 이후의 모든 문자열 제거

안녕하세요. 이번 포스팅에서는 한 컬럼에서 특정 문자 이후에 오는 모든 문자열을 제거하는 방법에 대해 알아보겠습니다.

먼저 간단한 예시를 들어보겠습니다.

 

SELECT keyword
FROM my_table
WHERE length(keyword) > 50

성남시, 한국전자기술연구원, 열화상카메라, IR, thermal camera, thermal, camera, 자율주행, Autonomous car, Self-driving
지구단위계획구역현황WMS조회,지구단위계획구역현황WFS조회,지구단위계획구역현황조회,지구단위규제현황WMS조회,지구단위규제현황WFS조회
사업면적, 용적율, 평가지표, 법정주차면적, 건페율, 화물주차, 장애인주차, 확장형주차, 계획면적, 경형주차, 주차수요, 주차면적, 주차, 일반주차, 대지면적, 건축면적
최소배차간격, 울산정보산업진흥원, 버스, 교통, 배차, 첫차출발시각, 일일배차, 최대배차간격, 운행요일, 배차시간표, 막차출발시각
출발지점ID, 종료, 운행위반정보, 기록, 기기상태코드, 시작, 버스, 잔여거리, 운행, 교통, 누적운행거리, 운행상태정보, 울산정보산업진흥원, 도착지점ID
축제시작일자, 죽제종료일자, 승차인원수 여객, 한글 역명 여객, 역코드 여객, 역간거리(KM) 여객, 축제명, 축제기간, 일평균 층차인원수 여객, 개최장소, 도착역 코드 여객, 주관기관
주차장 기본요금, KST Place, 전국 기준, 하루 최대 요금, 유료여부, 주차장 영업시간, 평일 요금, 경도, 휴일 요금, 주차장 이름, 주차장위치, 교통, 주차 장의 주소와 좌표, 위도
교통량, 교차로, 시간, 균일지체, 제어지체, 증분지체, 추가지체, 신호제어, 차로군, 정지, 감속, 통행시간, 평균제어지체, 신호 교차로
출발, 지점순번, 발생시각, 울산정보산업진흥원, 누적운행시간, 이력, 버스단말기번호, 속도, BIS, 정류장순번, 교통, 여행시간, 도착, CDMA감도, 검출순번, 지점ID, 방위각, 버스, 노선ID, 도착이력
단말기,  버스정보시스템, BIS, 버스, 노드, BUS, 버스 운전자, 휴대전화, 시내버스, 전광판, 정류장, 교통, 링크, Bus Information System, 대전시, 버스 내 승객, 링크 매칭 정보, 정류소 대기 승객, 인터넷

 

제 테이블에는 keyword 컬럼이 있습니다.  이 때 keyword 컬럼에서 50자 이상인 데이터는 사용할 수 없다고 가정할 때 검색해서 나오는 데이터는 위와 같습니다. 이제 이 데이터를 50자 이하로 잘라주면 되지만, 컴마로 구분되어 있는 문자열을 무턱대고 50자 이내로 잘라버리면 의미가 사라질 수 있습니다. 의미를 살려주기 위해 단어를 기준으로 잘라야 하는 경우가 생길 수 밖에 없습니다.

 

이번 포스팅에서는 postgresql 데이터베이스 기준으로 문자열을 제거하는 방법을 알아보겠습니다.

 

 

1. position 

먼저, postgresql 의 position 함수에 대해 알아보겠습니다. position 함수는 이름 그대로 어떤 문자열의 위치를 찾아주는 함수입니다.

예를 들어 'abc'라는 데이터가 있을때 position('c')은 3을 반환합니다.

이제 위에서 보았던 테이블에 컴마의 위치를 찾아보겠습니다.

SELECT POSITION(',' IN keyword)
FROM my_table 
WHERE length(keyword) > 50;

4
16
5
7
7
7
9
4
3
4

 

위에서 나온 결과와 비교해보면 컴마의 위치가 정확하게 추출된 것 같습니다.

하지만 제가 해야할 건 가장 앞에 있는 컴마의 위치가 아닌 가장 뒤에 있는 컴마의 위치를 파악해야합니다.

position 함수를 통해 특정 문자가 가장 처음 발생하는 위치를 뽑아낼 수 있다는 것을 알았습니다. 이제 이 함수를 응용해서 가장 뒤에 있는 컴마의 위치를 알아보도록 하겠습니다.

 

 

2. reverse

reverse 함수는 말 그대로 문자를 뒤집어주는 함수입니다. 앞서 알아보았던 position함수에 reverse된 키워드 컬럼을 넣어주면 가장 뒤에 있는 컴마의 위치를 뽑아낼 수 있습니다.

SELECT reverse(keyword)
FROM my_table 
WHERE length(keyword) > 60

gnivird-fleS ,rac suomonotuA ,행주율자 ,aremac ,lamreht ,aremac lamreht ,RI ,라메카상화열 ,원구연술기자전국한 ,시남성
회조SFW황현제규위단구지,회조SMW황현제규위단구지,회조황현역구획계위단구지,회조SFW황현역구획계위단구지,회조SMW황현역구획계위단구지
적면축건 ,적면지대 ,차주반일 ,차주 ,적면차주 ,요수차주 ,차주형경 ,적면획계 ,차주형장확 ,차주인애장 ,차주물화 ,율페건 ,적면차주정법 ,표지가평 ,율적용 ,적면업사
각시발출차막 ,표간시차배 ,일요행운 ,격간차배대최 ,차배일일 ,각시발출차첫 ,차배 ,통교 ,스버 ,원흥진업산보정산울 ,격간차배소최
DI점지착도 ,원흥진업산보정산울 ,보정태상행운 ,리거행운적누 ,통교 ,행운 ,리거여잔 ,스버 ,작시 ,드코태상기기 ,록기 ,보정반위행운 ,료종 ,DI점지발출
관기관주 ,객여 드코 역착도 ,소장최개 ,객여 수원인차층 균평일 ,간기제축 ,명제축 ,객여 )MK(리거간역 ,객여 드코역 ,객여 명역 글한 ,객여 수원인차승 ,자일료종제죽 ,자일작시제축
도위 ,표좌 와소주 의장 차주 ,통교 ,치위장차주 ,름이 장차주 ,금요 일휴 ,도경 ,금요 일평 ,간시업영 장차주 ,부여료유 ,금요 대최 루하 ,준기 국전 ,ecalP TSK ,금요본기 장차주
로차교 호신 ,체지어제균평 ,간시행통 ,속감 ,지정 ,군로차 ,어제호신 ,체지가추 ,체지분증 ,체지어제 ,체지일균 ,간시 ,로차교 ,량통교
력이착도 ,DI선노 ,스버 ,각위방 ,DI점지 ,번순출검 ,도감AMDC ,착도 ,간시행여 ,통교 ,번순장류정 ,SIB ,도속 ,호번기말단스버 ,력이 ,간시행운적누 ,원흥진업산보정산울 ,각시생발 ,번순점지 ,발출
넷터인 ,객승 기대 소류정 ,보정 칭매 크링 ,객승 내 스버 ,시전대 ,metsyS noitamrofnI suB ,크링 ,통교 ,장류정 ,판광전 ,스버내시 ,화전대휴 ,자전운 스버 ,SUB ,드노 ,스버 ,SIB ,템스시보정스버  ,기말단

 

SELECT POSITION(',' IN reverse(keyword))
FROM my_table 
WHERE length(keyword) > 60

14
14
6
8
8
6
4
8
6
5

 

이제 뒤에서 시작하는 컴마의 첫 위치를 알았으니, 전체 길이에서 맨 뒤 컴마의 인덱스를 빼줍니다. 이렇게 하면 마지막 쉼표까지의 문자열 길이(삭제되지 않을)를 계산할 수 있습니다. 

 

SELECT length(keyword) - POSITION(',' IN reverse(keyword))
FROM my_table 
WHERE length(keyword) > 60

 

2. substring

substring 함수는 시작 위치, 길이를 기준으로 문자열을 잘라서 보여주는 함수입니다.

substring(keyword, 1, 10)처럼 사용하면 from 및 where 조건절에서 가져오는 데이터의 첫번째 문자열부터 10번째 문자열까지 보여주겠다는 의미입니다.

substring(keyword from 1 for 10)과 같이 사용할 수도 있습니다.

 

위에서 추출한 인덱스의 번호를 사용해서 컴마 단위 단어가 제거된 결과를 확인해보겠습니다.

 

SELECT substring(keyword, 1, length(keyword) - POSITION(',' IN reverse(keyword)))
FROM my_table 
WHERE length(keyword) > 60

 

성남시, 한국전자기술연구원, 열화상카메라, IR, thermal camera, thermal, camera, 자율주행, Autonomous car
지구단위계획구역현황WMS조회,지구단위계획구역현황WFS조회,지구단위계획구역현황조회,지구단위규제현황WMS조회
사업면적, 용적율, 평가지표, 법정주차면적, 건페율, 화물주차, 장애인주차, 확장형주차, 계획면적, 경형주차, 주차수요, 주차면적, 주차, 일반주차, 대지면적
최소배차간격, 울산정보산업진흥원, 버스, 교통, 배차, 첫차출발시각, 일일배차, 최대배차간격, 운행요일, 배차시간표
출발지점ID, 종료, 운행위반정보, 기록, 기기상태코드, 시작, 버스, 잔여거리, 운행, 교통, 누적운행거리, 운행상태정보, 울산정보산업진흥원
축제시작일자, 죽제종료일자, 승차인원수 여객, 한글 역명 여객, 역코드 여객, 역간거리(KM) 여객, 축제명, 축제기간, 일평균 층차인원수 여객, 개최장소, 도착역 코드 여객
주차장 기본요금, KST Place, 전국 기준, 하루 최대 요금, 유료여부, 주차장 영업시간, 평일 요금, 경도, 휴일 요금, 주차장 이름, 주차장위치, 교통, 주차 장의 주소와 좌표
교통량, 교차로, 시간, 균일지체, 제어지체, 증분지체, 추가지체, 신호제어, 차로군, 정지, 감속, 통행시간, 평균제어지체
출발, 지점순번, 발생시각, 울산정보산업진흥원, 누적운행시간, 이력, 버스단말기번호, 속도, BIS, 정류장순번, 교통, 여행시간, 도착, CDMA감도, 검출순번, 지점ID, 방위각, 버스, 노선ID
단말기,  버스정보시스템, BIS, 버스, 노드, BUS, 버스 운전자, 휴대전화, 시내버스, 전광판, 정류장, 교통, 링크, Bus Information System, 대전시, 버스 내 승객, 링크 매칭 정보, 정류소 대기 승객

 

가장 뒤에 있던 컴마와 그 뒤 단어까지 모두 깔끔하게 제거된 것을 확인할 수 있습니다.

이제 해당 컬럼을 업데이트하는 구문을 작성해주고, 원하는 문자열의 길이가 될 때까지 반복해주면 됩니다.

 

UPDATE my_table
SET keyword = substring(keyword, 1, length(keyword)-position(',' in reverse(keyword))
WHERE length(keyword) > 60

 

 

이상으로 문자열 기준으로 컬럼 데이터를 잘라내는 방법을 알아보았습니다.