Re: [GENERAL] ask: select right(column) ???

2009-02-16 Thread Ludwig Kniprath 
Hello Hendra,
there is no function right(column, n-Chars), but you can use 
substring(column-name from offset for num_chars) in combination with 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ask: select right(column) ???

2009-02-16 Thread Sam Mason 
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/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ask: select right(column) ???

2009-02-16 Thread Lennin Caro 
 
 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'





  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ask: select right(column) ???

2009-02-16 Thread Sam Mason 
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,10) 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.21.09
  b109.90.75
  c140.21.19
  d569.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/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general