There is a column with department name. Department name consists of a name followed by a hyphen, followed by the dept number. In some cases the department name has a hyphen in it as well. So at max, 2 hyphens in a value. I need to create two separate columns, department name and department number.
I was able to separate the department number using a right function since the department number is always 5 numbers. The department name however is what I’m not able to figure out.
Generally, if the number is always 5-digits long then:
- the name is the sub-string excluding the last 6 characters; and
- the number is the sub-string containing the last 5 characters.
In Oracle, you can use:
SELECT department_name,
SUBSTR(department_name, 1, LENGTH(department_name) - 6) AS name,
SUBSTR(department_name, -5) AS department_number
FROM departments;
Which, for the sample data:
CREATE TABLE departments (department_name VARCHAR(200));
INSERT INTO departments (department_name) VALUES ('accounting-00001');
INSERT INTO departments (department_name) VALUES ('human-resources-00002');
Outputs:
| DEPARTMENT_NAME | NAME | DEPARTMENT_NUMBER |
|---|---|---|
| accounting-00001 | accounting | 00001 |
| human-resources-00002 | human-resources | 00002 |
Oracle fiddle
In SQL Server, you can use:
SELECT department_name,
LEFT(department_name, LEN(department_name) - 6) AS name,
RIGHT(department_name, 5) AS department_number
FROM departments;
Which outputs:
| department_name | name | department_number |
|---|---|---|
| accounting-00001 | accounting | 00001 |
| human-resources-00002 | human-resources | 00002 |
SQL Server fiddle