Concat with sql function

Question

Im trying to write a SQL function which will get an ID as an input and concat the last two diğits of year with the term .

For example if I call the function and type 112 I want it to give me output as 00X1 . How do I do that?

Thanks

 id  | unswid | year | term |   name    |       longname       |  starting  |   ending   | startbrk | endbrk | endwd |  endenrol  |   census   
-----+--------+------+------+-----------+----------------------+------------+------------+----------+--------+-------+------------+------------
 112 |   5001 | 2000 | X1   | Summ 2000 | Summer Semester 2000 | 1999-12-01 | 2000-01-31 |          |        |       |            | 1999-12-24
 114 |   5004 | 2000 | S1   | Sem1 2000 | Semester 1 2000      | 2000-02-01 | 2000-06-30 |          |        |       | 2000-03-11 | 2000-03-31
 113 |   5005 | 2000 | X2   | Wint 2000 | Winter Semester 2000 | 2000-06-14 | 2000-07-14 |          |        |       |            | 
 115 |   5007 | 2000 | S2   | Sem2 2000 | Semester 2 2000      | 2000-07-01 | 2000-12-31 |          |        |       | 2000-07-28 | 2000-08-31
 117 |   5011 | 2001 | X1   | Summ 2001 | Summer Semester 2001 | 2000-12-01 | 2001-02-25 |          |        |       | 2001-01-30 | 2001-01-31

Patrick · Accepted Answer · 2016-04-15 10:36:59Z

1

Assuming the field year is an integer you can do:

CREATE FUNCTION year_term(n integer) RETURNS text AS $$
  SELECT (year % 100)::text || term FROM my_table WHERE id = n;
$$ LANGUAGE sql STRICT;

If the field year is a varchar or text you can do:

CREATE FUNCTION year_term(n integer) RETURNS text AS $$
  SELECT right(year, 2) || term FROM my_table WHERE id = n;
$$ LANGUAGE sql STRICT;

answered Apr 15, 2016 at 10:36

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concat with sql function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related