If the string will always be in that general form, use substring & position
functions (see "String Functions and Operators" in the docs.
Example: unit_number := substr(team_number, strpos(team_number, ''-'') + 1);
If you don't want the leading zero, you could make make the "+1" into "+2".
If you might have more than one leading zero, you could use ltrim:
unit_number := ltrim(unit_number, ''0'');
> -----Original Message-----
> From: Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
> Sent: Wednesday, July 18, 2001 9:10 AM
> To: pgsql-sql
> Subject: pl/pgsql - code review + question
>
> Hi all, I've just written my first pl/pgsql function (code included below
> for
> you to pull apart).
>
> It takes an int4 mid (e.g. 15) and then using a select pulls out the team
> number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit
>
> number NE/012-02.
>
> I now want to write the reverse function, where I can enter 'NE/012-02'
> and
> get back the mid 15. The bit I'm stuck on is now I split the team part
> from
> the member part so that I can build the select statement.
>
> TIA Gary
>
> __BEGIN__
> CREATE FUNCTION getunitno(int4) RETURNS varchar AS '
> DECLARE
> mid ALIAS FOR $1;
> results RECORD;
> BEGIN
> select into results t.tnumber as tnumber, m.mnumber as mnumber
> from teams t, members m
> where t.tid = m.mteam and m.mid = mid;
> if results.mnumber < 10 then
> return results.tnumber || ''-0'' || results.mnumber;
> else
> return results.tnumber || ''-'' || results.mnumber;
> end if;
> END;
> ' LANGUAGE 'plpgsql';
> __END__
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
