Thread: ask: select right(column) ???

Dear all,

I have simple question
I tried following code

 select right(column, number_of_character) from table

but it didn't work, saying that pg doesn't have the function
is there any way to achieve such output?

honestly I have no idea that such simple feature doesn't exist in postgresql
or am I wrong? since I look at SQL Key Words table and it's written as reserved

Thank you
Regards
Hendra

Hello Hendra,
there is no function right(column, n-Chars), but you can use substring(column-name from offset for num_chars) in
combinationwith char_length for getting the right-n-characters as f. e.: 

select substring(column from (char_length(column) - 3) for 4) from table

Ludwig



>Dear all,
>
>I have simple question
>I tried following code
>
> select right(column, number_of_character) from table
>
>but it didn't work, saying that pg doesn't have the function
>is there any way to achieve such output?
>
>honestly I have no idea that such simple feature doesn't exist in postgresql
>or am I wrong? since I look at SQL Key Words table and it's written as
>reserved
>
>Thank you
>Regards
>Hendra

On Mon, Feb 16, 2009 at 03:21:20PM +0700, hendra kusuma wrote:
>  select right(column, number_of_character) from table
[..]
> honestly I have no idea that such simple feature doesn't exist in postgresql
> or am I wrong? since I look at SQL Key Words table and it's written as
> reserved

AFAIK, it's reserved because "right" is used in outer join syntax; i.e.
table RIGHT OUTER JOIN table, and not because of the function name.

If you want this in Postgres, you could always do:

  CREATE FUNCTION right(TEXT,INTEGER) RETURNS TEXT
    LANGUAGE sql IMMUTABLE STRICT AS $$
      SELECT substring($1 FROM char_length($1)-$2+1) $$;

Using "right" to extract the right most characters from a string sounds
very much like BASIC to me but may have moved to other languages now.
Humm, lets have a look; BASIC has Right$(), Pascal has RightStr(), MySql
has Right(), MS Sql Server has Right().  The other languages I chose to
look at (C, C++, Java, PHP, Lua, Ruby, Javascript) all naively expose a
substring function, like PG and Oracle, and leave anything like Right()
up to the user.

--
  Sam  http://samason.me.uk/

> I have simple question
> I tried following code
>
>  select right(column, number_of_character) from table
>
> but it didn't work, saying that pg doesn't have the
> function
> is there any way to achieve such output?
>
> honestly I have no idea that such simple feature
> doesn't exist in postgresql
> or am I wrong? since I look at SQL Key Words table and
> it's written as
> reserved
>
> Thank you
> Regards
> Hendra

you are right but you can use the substring function, like this

select 'test123',substring('test123' from '...$')

this return '123'

On Mon, Feb 16, 2009 at 07:10:11AM -0800, Lennin Caro wrote:
> you can use the substring function, like this
>
> select 'test123',substring('test123' from '...$')
>
> this return '123'

Note that regexps are slower than substrings; as an example, I did:

  SELECT COUNT(s) FROM (
    SELECT 'test'::text AS s
    FROM generate_series(1,100000) n
    OFFSET 0) x;

We have to put the "OFFSET 0" in to force evaluation otherwise PG is
smart enough to optimize code away and invalidate the test.  I replaced
the "COUNT(s) with various exressions to see how it performed:

  test  expression
  a     COUNT(s)
  b     COUNT(substr(s))
  c     COUNT(substr(s,char_length(s)-3+1))
  d     COUNT(substring(s from '...$'))

Over several iterations:

  test  mean  stddev
  a     72.2    1.09
  b    109.9    0.75
  c    140.2    1.19
  d    569.2   59.46

Not sure why I'm getting so much variance on the last run, strange.
Anyway...  Also note that because PG is nice about expanding SQL
functions, test "c" is the same as calling the right() function I
defined earlier.  I got a mean of 146.2 and a standard deviation of 9.04
so they're basically the same.

The basic string functions (substr and char_length) take about 0.4
microseconds to execute on my computer, and the regex function about 10
times as long at just under 0.5 microseconds.

The useful result being that substring(s from pattern) is easy to use,
and for small numbers of rows (i.e. less than a few thousand) you're
not going to notice much difference in performance.  It's only when you
start dealing with a hundred thousand or so rows the difference is going
to be really noticeable.

--
  Sam  http://samason.me.uk/