Category: SQL

Data Development, Database Administration, Databases, SQL

SQL Tips: How to rename a column in a table – Oracle database – Oracle SQL

At times you will need to change the name of a column in an existing table. If you are not changing the data type, it is just one statement / step that needs to be executed. However, I strongly recommend that you also do a backup step, especially if you’re making the change in a production environment, just in case of an unexpected issue.

If you choose to do the backup, you may perform this with a “create-table-as-select” statement in this form:

create table [table_name_backup] as select * from [table_name];

Here is an example of the above statement:

create table EMPLOYEES_BKUP as select * from EMPLOYEES;

Now that the table you are modifying is all backed up, you can proceed to rename the column.

The rename SQL statement would take this form:

alter table [table_name] rename column [existing_column_name] to [new_column_name];

An example of the statement:

alter table EMPLOYEES rename column SEX to GENDER;

Thanks for reading! I hope you found this information useful.

Data Development, SQL

SQL tips: Get last day and first day of month – Oracle SQL

At times you may need to dynamically determine the first or last day of a month based on the current date or some date in your data. This post provides a few options for deriving the First day of month, and Last day of month. And also, it provides a couple tweaks to get Last day of the next or previous month.

Get the current date (today’s date):
select sysdate from dual;

SYSDATE

16-MAY-22

Get the last day of the current month:
select trunc(last_day(sysdate)) as LastDayOfMonth from dual;

LASTDAYOFMONTH

31-MAY-22

Side note: The last_day() function can also be useful for dynamically determining leap years (that is, whether February has 28 or 29 days)
select
last_day(date ‘2020-02-01’) LastDayOfFeb2020, — leap year
last_day(date ‘2021-02-01’) LastDayOfFeb2021 — not a leap year
from dual;

LASTDAYOFFEB2020 LASTDAYOFFEB2021

29-FEB-20 28-FEB-21

Get the last day of the next month:
select add_months(trunc(last_day(sysdate)), 1) as LastDayOfNextMonth from dual;

LASTDAYOFNEXTMONTH

30-JUN-22

Get the last day of the previous month:
select add_months(trunc(last_day(sysdate)), -1) as LastDayOfPreviousMonth from dual;

LASTDAYOFPREVIOUSMONTH

30-APR-22

Get the first day of the current month:

select trunc(sysdate,’month’) as FirstDayOfMonth from dual;
select trunc(sysdate,’MM’) as FirstDayOfMonth from dual;

FIRSTDAYOFMONTH

01-MAY-22

Get the first day of the next month:

select add_months(trunc(sysdate,’MM’), 1) as FirstDayOfNextMonth from dual;

FIRSTDAYOFNEXTMONTH

01-JUN-22

You can also get the first day of the next month using this:
select trunc(last_day(sysdate)+1) as FirstDayOfNextMonth from dual;

FIRSTDAYOFNEXTMONTH

01-JUN-22

Get the first day of the previous month:

select add_months(trunc(sysdate,’MM’), -1) as FirstDayOfPreviousMonth from dual;

FIRSTDAYOFPREVIOUSMONTH

01-APR-22

Here are 3 resources for getting the first and last day of the month values on a SQL Server database:

https://zarez.net/?p=2462

SQL SERVER – Simple Method to Find FIRST and LAST Day of Current Date
SQL Server EOMONTH Function

Thanks for reading! I hope you found this information useful!

Good luck on your data journey.

Data Development, SQL

SQL Tips: How to replace a character or a part of a string in a column value – Oracle SQL

If you frequently work with data, you will occasionally need to replace a part of the values in a column for some reason. In our case, we recently had some data that was loaded into a table from a file source and all the values had an unwanted leading and trailing tab character.

We needed to remove these tabs and preferably without resourcing and reloading the data. The solution was to use the REPLACE SQL function.

This is the form of the UPDATE statement that uses the REPLACE function

Update TABLENAME Set COLUMN_TO_BE_UPDATED = REPLACE(COLUMN_TO_BE_UPDATED, ‘Char_or_String_to_replace_in_column_values’, ‘Char_or_String_to_replace_it_with’);

Here is an example that replaces the long street names in the ADDRESS column with their abbreviated corresponding name:

update EMPLOYEE set ADDRESS = REPLACE(ADDRESS, ‘Street’, ‘St’ ), ADDRESS = REPLACE(ADDRESS, ‘Avenue’, ‘Ave’ ), ADDRESS = REPLACE(CITY, ‘Circle’, ‘Cir’ ) where COUNTRY = ‘United States’;

To solve the issue we had, the SQL statement below was used to update the EMPLOYEE column values by replacing the tab character with NULL – as shown below.

Important Note: Before making any major updates to a table, it’s a best practice to back up the table first.

update EMPLOYEE_EXPENSE_DATA set EMPLOYEE = REPLACE(EMPLOYEE, ‘    ‘, NULL) where EMPLOYEE like ‘  %’;

Important Note: Verify your update before you commit the changes.

We repeated this SQL for each column that had the “bad” (unwanted leading and trailing tabs) values.

Thanks for reading! I hope you found this information useful.

Data Development, SQL

SQL Tips: How to generate a delimited list from the values in an Oracle database table column

This is a quick post that shows how to generate the values from a table column in a delimited list format.

If you have a table named CONTACTS that has the following columns and values:

select * from CONTACTS;

And you wanted to generate a delimited list of the names in the NAMES column like this:

Adam, Jane, John, Lisa, Mark, Mary, Noah

Or like this:

Adam | Jane | John | Lisa | Mark | Mary | Noah

You would use SQL statements like these to generate the above output – note the delimiter in each case, and also note that you can sort the output:

— comma delimiter example …

SELECT listagg(NAME, ', ') within group (order by NAME) FROM CONTACTS;

Adam, Jane, John, Lisa, Mark, Mary, Noah

— pipe delimiter example …

SELECT listagg(NAME, ' | ') within group (order by NAME) FROM CONTACTS;

Adam | Jane | John | Lisa | Mark | Mary | Noah

You can find more information about the listagg function here.

Thanks for reading. I hope you found this useful.