(SQL) POSITON, STRPOS 함수 개념 및 연습문제

지난 글에서 알아본 LEFT, RIGHT, SUBSTR 함수에 이어 같이 쓰면 편리한 POSITION, STRPOS 함수에 대해 알아보겠습니다.

 

🔻(참고)LEFT, RIGHT, SUBSTR함수🔻

(SQL) LEFT, RIGHT, SUBSTR 함수 및 연습문제

 

1. POSITION

• 문자 열에서 특정 문자열이 처음 나타나는 위치를 반환하는 함수입니다.
• 추출하려는 문자열을 지정한 다음 IN을 붙이고 그 뒤에 문자열 이름, 열 이름 또는 원하는 정보를 추출해낼 문자열 전체를 입력합니다.
• 하나의 열에 수많은 정보가 몰려 있을 때 사용합니다. 

출처 ❘ Udacity

• 아래의 예시보다 더 많은 정보가 하나의 열에 몰려있다고 생각해봅시다.

출처 ❘ Udacity

• 이때 해당 열에서 학생들의 급여 정보를 확인해야 한다고 가정해보면, 문자를 하나씩 세면서 급여 정보가 시작되는 위치를 파악하는 것은 힘들 것입니다. 
• 이런 경우 POSITION 함수를 사용하는 것입니다
• student_information 에서 $ 기호가 나타나는 위치를 확인한 다음 그 위치를 salary_starting_position 으로 명명할 수 있습니다.
• 그리고 그 결과를 SUBSTR 함수에 집어넣으면 모든 기록으로부터 급여 정보를 일괄 추출할 수 있습니다.

 

2. STRPOS

• STRPOS는 POSITION과 같은 기능을 수행합니다. 
• STRPOS란 하위 문자열의 위치를 뜻합니다. 
• 첫 번째 인수는 문자열 이름 또는 열 이름이며 두 번째 인수는 추출해 낼 하위 문자열 입니다.

 

3. 연습문제

문제1) accounts 테이블을 사용하여 primary_poc의 성과 이름을 포함하는 first 및 last 이름 열을 만듭니다.

SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1) first_name,
       RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name
FROM accounts;

문제2) sales_reps 테이블의 모든 담당자 name에 대해 동일한 작업을 수행하고 다시 first 및 last 이름 열을 제공합니다.

SELECT LEFT(name, STRPOS(name, ' ') -1) first_name,
	RIGHT(name, LENGTH(name) - STRPOS(name, ' ')) last_name
FROM sales_reps;

 

아래 문제에서부터는 CONCAT 함수도 함께 이용합니다.

 

🔻(참고)CONCAT 함수🔻

(SQL) CONCAT 함수 및 연습문제

 

 

문제3) accounts 테이블의 각 회사는 각 primary_poc에 대한 이메일 주소를 생성하려고 합니다. 이메일 주소는 primary_poc (성). (primary_poc)(이름) @ 회사 이름 .com의 이어야 합니다.

WITH t1 AS (
	SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1) first_name,
    	     RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
        FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', name, '.com')
FROM t1;

문제4) 이전 문제에서 회사 이름 중 일부에 공백이 포함되어 있는 것을 보셨을 것입니다. 이는 이메일 주소에서 확실히 작동하지 않습니다. 계정 '이름'의 모든 공백을 제거하여 작동하는 이메일 주소를 만들 수 있는지 확인하십시오. 

WITH t1 AS (
	SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1) first_name,
               RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
    FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', REPLACE(name, ' ', ''), '.com')
FROM t1;

 

=> BerkshirHathway에 띄어쓰기가 제거된 걸 볼 수 있습니다.

문제5) 최초 로그인 후 변경될 초기 비밀번호를 만들려고 합니다. 초기 비밀번호는 primary_poc의 이름의 첫 글자(소문자),  이름의 마지막 글자(소문자), 성의 첫 글자(소문자), 성의 마지막 글자(소문자), 이름의 글자 수, 성의 글자 수, 그들이 일하는 회사(모두 공백 없이 대문자)로 표시됩니다.

WITH t1 AS (
	SELECT LEFT(primary_poc, STRPOS(primary_poc, ' ') -1 ) first_name,  
    	RIGHT(primary_poc, LENGTH(primary_poc) - STRPOS(primary_poc, ' ')) last_name, name
	FROM accounts)
SELECT first_name, last_name, CONCAT(first_name, '.', last_name, '@', name, '.com'), 
		LEFT(LOWER(first_name), 1) || RIGHT(LOWER(first_name), 1) || LEFT(LOWER(last_name), 1) || RIGHT(LOWER(last_name), 1) || LENGTH(first_name) || LENGTH(last_name) || REPLACE(UPPER(name), ' ', '')
FROM t1;