Covert string to valid integer

select cast(coalesce(to_number('1a2'), -1) as int)

Inner Join

Will list rows where left and right match

select
emp.id,
emp.name,
dept.name
from employee emp
inner join department dept
on emp.dept_id = dept.dept_id

Will list only those employees who have a department which is found in the dept table.

Right Join

Every rows from right table and matching records from left

select
emp.id,
emp.name,
dept.name
from employee emp
right join department dept
on emp.dept_id = dept.dept_id

Even if the department doesn't have employees, it will be listed. If an employee doesn't have a department, it will not be listed.

Left Join

Every rows from left table and matching records from right

select
emp.id,
emp.name,
dept.name
from employee emp
left join department dept
on emp.dept_id = dept.dept_id

Even if the employee doesn't have a department, he/she will be listed here.

Replace

SELECT oreplace('Hello There','Hello','Hi')

This converts Hello There to Hi There.

Teradata: Extract month and year from date

 extract (year from date1)

 extract (month from date1)

Teradata: Backup table with data

create table table1_bkup as
table1 with data

Teradata: List columns of a table

select
*
from dbc.columns
where tablename ='table1'
and databasename = 'database1'

Teradata: List tables in database

select
*
from dbc.tables
where databasename='database'
and tablekind = 'T'

Teradata: Add column with default value

alter table table1 add col1 int default 0

Teradata: Rename table

rename table table1 to table2

Teradata: Add column

alter table table1 add x int;

Teradata SQL: Drop column

alter table table1 drop col1

Teradata SQL: Update column name

alter table table1 rename col1 to col1_updated

Teradata SQL: Create table using Select statement

Execute following 2 SQLs:

1. create table tablex as
(
select
a,
b,
c
from table1
)
with no data;

2. insert into tablex
select
a,
b,
c
from table1;

Teradata recursive sql

Suppose we have a table:
create table accont_tbl
acct_id int,
manager_acct_id int,
name varchar(50)
)
which contains employee records with their supervisor.

Then the recursive sql is:
with recursive mgr_tbl(acct_id, manager_acct_id, mgr_name, depth) as
(
select
a.acct_id,
a.manager_acct_id,
a1.name as mgr_upn,
1 as depth
from account_tbl a
inner join account_tbl a1
on a.manager_acct_id = a1.acct_id
where a.acct_id = 8976
union all
select
mgr_tbl.acct_id,
a.manager_acct_id,
a1.name,
mgr_tbl.depth+1
from mgr_tbl
inner join account_tbl a
on mgr_tbl.manager_acct_id = a.acct_id
inner join account_tbl a1
on a.manager_acct_id = a1.acct_id
-- where mgr_tbl.mgr_upn <> 'email of root if root's manager is same as accout'
)
select * from mgr_tbl order by depth

Regular expression to remove blank lines

If you are using Notepad++, replace ^[\n\r]+ with nothing in the replace

Regular expression to remove html and get only values from a select list

Regular expression : ^<.*>

Replace ^<.*> with blank if we use Notepad++

Example:
<option value="/html/A.htm">A
<option value="/html/B.com">B
<option value="/html/C.htm">C

The replace will produce:
A
B
C

Teradata sql - retrieve top and bottom 50 percentage of the records

Top 50%
----------
select top 50 percent  *
from table1
order by id asc

Bottom 50%
---------------
select top 50 percent  *
from table1
order by id desc

Update from select

Update table1
from
(
select
t1.x,
t1.y
from table2 t2
left join table3 t3
on t2.id = t3.id
) input
set f1 = input.x
where f2 = input.y