Thread: Trailing spaces - is there an easier way?
I'm sure this has been asked before, but I couldn't find it:<br /><br /> I have a "zzz CHAR (8)" field. It needs to beCHAR because trailing spaces need to be ignored for most operations. However, I need to concatenate it with another (literal)string and need the spaces to be significant in that operation. The <b>ONLY WAY</b> I could find to do it in v9.0.1was (represented in the following function):<br /><br /><font color="#000099"><tt>CREATE OR REPLACE FUNCTION padded(field CHAR ) RETURNS TEXT<br /> RETURNS NULL ON NULL INPUT<br /> IMMUTABLE<br /> LANGUAGE SQL AS $SQL$<br/> SELECT RPAD( $1, OCTET_LENGTH( $1 ) )<br /> $SQL$;</tt></font><br /><br /> And then of course I write:<br/><br /><font color="#000099"><tt>SELECT padded( zzz ) || '/' || ...<br /></tt></font><br /> Is there a better way?<br/><br />
2010/11/4 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>: > I'm sure this has been asked before, but I couldn't find it: > > I have a "zzz CHAR (8)" field. It needs to be CHAR because trailing spaces > need to be ignored for most operations. However, I need to concatenate it > with another (literal) string and need the spaces to be significant in that > operation. The ONLY WAY I could find to do it in v9.0.1 was (represented > in the following function): > > CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT > RETURNS NULL ON NULL INPUT > IMMUTABLE > LANGUAGE SQL AS $SQL$ > SELECT RPAD( $1, OCTET_LENGTH( $1 ) ) > $SQL$; > > And then of course I write: > > SELECT padded( zzz ) || '/' || ... > > Is there a better way? > > nic=# SELECT 'AAAA '::char(6) || 'bbbb';?column? ----------AAAAbbbb (1 row) Time: 2.710 ms nic=# SELECT 'AAAA '::char(6)::cstring || 'bbbb'; ?column? ------------AAAA bbbb (1 row) regards Pavel Stehule
On Thu, Nov 04, 2010 at 08:42:21PM +0100, Pavel Stehule wrote: > 2010/11/4 Dean Gibson (DB Administrator) <postgresql@ultimeth.com>: > > I'm sure this has been asked before, but I couldn't find it: > > > > I have a "zzz CHAR (8)" field.?? It needs to be CHAR because trailing spaces > > need to be ignored for most operations.?? However, I need to concatenate it > > with another (literal) string and need the spaces to be significant in that > > operation.?? The ONLY WAY I could find to do it?? in v9.0.1 was (represented > > in the following function): > > > > CREATE OR REPLACE FUNCTION padded( field CHAR ) RETURNS TEXT > > ?????? RETURNS NULL ON NULL INPUT > > ?????? IMMUTABLE > > ?????? LANGUAGE SQL AS $SQL$ > > ?????????????? SELECT RPAD( $1, OCTET_LENGTH( $1 ) ) > > ?????? $SQL$; > > > > And then of course I write: > > > > SELECT padded( zzz ) || '/' || ... > > > > Is there a better way? > > > > > > nic=# SELECT 'AAAA '::char(6) || 'bbbb'; > ?column? > ---------- > AAAAbbbb > (1 row) > > Time: 2.710 ms > nic=# SELECT 'AAAA '::char(6)::cstring || 'bbbb'; > ?column? > ------------ > AAAA bbbb > (1 row) > > > regards > > Pavel Stehule > Here is a link to some useful other pseudo types: http://www.postgresql.org/docs/9.0/interactive/datatype-pseudo.html Regards, Ken