Hi
Is there a standard postgres method of replacing empty strings.
In Oracle, nvl handles nulls and empty strings, as does ifnull() in
MySQL, but with postgres coalesce only handles null strings.
If, not is the best solution to create a plpgsql function, ie
CREATE FUNCTION isEmpty (character varying, character varying) RETURNS
character varying
AS '
declare
fnRetTrue alias for $1;
fnRetFalse alias for $2;
begin
if fnRetTrue = '' or fnRetTrue is not null then
return fnRetTrue;
else
return fnRetFalse;
end if;
end;
'
LANGUAGE plpgsql;
Thanks
Simon
--
Simon Windsor
Email: si***********@c ornfield.org.uk
Tel: 01454 617689
Mob: 07960 321599
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Mailscanner thanks transtec Computers for their support.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html 5 14139
Simon Windsor wrote: Hi
Is there a standard postgres method of replacing empty strings.
In Oracle, nvl handles nulls and empty strings, as does ifnull() in MySQL, but with postgres coalesce only handles null strings.
If, not is the best solution to create a plpgsql function, ie
CREATE FUNCTION isEmpty (character varying, character varying) RETURNS character varying
This all depends upon what you mean by handle. Do you want to treat
empty strings as NULL or NULL as empty strings? As you said, you can
treat NULL as empty strings using COALESCE:
SELECT COALESCE(x, '');
You can treat empty strings as NULL
SELECT NULLIF(x, '');
But I'd guess most on this list are wondering why you want to equate
an empty string with NULL, as they have two distinct meanings.
Oracle's treatment of empty strings as NULL is world-renowned for
being insane...
HTH
Mike Mascari
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Hi
I understand that null and '' are different, and MySQL and Oracle
functions are confusing, but my question was not about replacing NULL
but replacing Empty strings. These are handled in MySQL/Oracle by the
same functions that do NULL checks.
Is there a standard function in Postgres that replaces Empty strings, as
against a NULL value.
Simon
On Wed, 2004-06-23 at 18:05, Mike Mascari wrote: Simon Windsor wrote: Hi
Is there a standard postgres method of replacing empty strings.
In Oracle, nvl handles nulls and empty strings, as does ifnull() in MySQL, but with postgres coalesce only handles null strings.
If, not is the best solution to create a plpgsql function, ie
CREATE FUNCTION isEmpty (character varying, character varying) RETURNS character varying
This all depends upon what you mean by handle. Do you want to treat empty strings as NULL or NULL as empty strings? As you said, you can treat NULL as empty strings using COALESCE:
SELECT COALESCE(x, '');
You can treat empty strings as NULL
SELECT NULLIF(x, '');
But I'd guess most on this list are wondering why you want to equate an empty string with NULL, as they have two distinct meanings. Oracle's treatment of empty strings as NULL is world-renowned for being insane...
HTH
Mike Mascari
--
Simon Windsor
Email: si***********@c ornfield.org.uk
Tel: 01454 617689
Mob: 07960 321599
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Mailscanner thanks transtec Computers for their support.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Simon Windsor <si***********@ cornfield.org.u k> writes: Hi
I understand that null and '' are different, and MySQL and Oracle functions are confusing, but my question was not about replacing NULL but replacing Empty strings. These are handled in MySQL/Oracle by the same functions that do NULL checks.
Is there a standard function in Postgres that replaces Empty strings, as against a NULL value.
I don't think so, but you could use a CASE clause for this.
-Doug
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
On Wed, Jun 23, 2004 at 19:10:05 +0100,
Simon Windsor <si***********@ cornfield.org.u k> wrote: Hi
I understand that null and '' are different, and MySQL and Oracle functions are confusing, but my question was not about replacing NULL but replacing Empty strings. These are handled in MySQL/Oracle by the same functions that do NULL checks.
Is there a standard function in Postgres that replaces Empty strings, as against a NULL value.
Something like the following may suit your purposes:
coallesce(nulli f(inputstring,' '), 'replacement value')
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Hi
Thanks, I should have thought of that myself!
Simon
On Wed, 2004-06-23 at 19:43, Bruno Wolff III wrote: On Wed, Jun 23, 2004 at 19:10:05 +0100, Simon Windsor <si***********@ cornfield.org.u k> wrote: Hi
I understand that null and '' are different, and MySQL and Oracle functions are confusing, but my question was not about replacing NULL but replacing Empty strings. These are handled in MySQL/Oracle by the same functions that do NULL checks.
Is there a standard function in Postgres that replaces Empty strings, as against a NULL value.
Something like the following may suit your purposes: coallesce(nulli f(inputstring,' '), 'replacement value')
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
Simon Windsor
Email: si***********@c ornfield.org.uk
Tel: 01454 617689
Mob: 07960 321599
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Mailscanner thanks transtec Computers for their support.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Bob Bedford |
last post by:
I've a query where I retrieve 2 codes.
select ida, idb from mytable.
I've an other table where I've some "labels".
id:1 text:shoes
id:2 text:socks
.....
|
by: Spark |
last post by:
Hi,
Situation:
Need a query to return number of ticket records by month of open in a
log table where the ticket open record is older than 24 hours then the
ticket pending or ticket closed record. Tickets can also only have a
closed record with no open record.
Sample data table:
ticket_id date_log status_name status_id
|
by: Jeff Roughgarden |
last post by:
I am using a clever product called SQL Expert Pro to optimize
problematic SQL statements. It works by generating syntactically
identical variations of the original SQL, finding all unique execution
plans, and batch testing them.
Anyway, it generally turns out that a very odd change dramatically
improves performance (for a PeopleSoft database on SQL Server 7). Run
time goes from 52 seconds to 8 seconds.
The change is to replace a join...
|
by: xixi |
last post by:
when i create a join view like this
create view JV104FZ.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05,
PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select
JV104FZ.APPTINM.APAM32, JV104FZ.APPTINM.APNO20,
JV104FZ.APPTINM.APQY05, JV104FZ.APPTINM.PONO01,
JV104FZ.APPTINM.PONO05, JV104FZ.APPTINM.PONO19,
COALESCE(JV104FZ.POPTOL.POCD01, ' '), COALESCE(JV104FZ.POPTOL.POCD13,
' '), JV104FZ.POPTOL.systimestamp,...
|
by: John Greve |
last post by:
Any ideas on why my the result 'green' row
column DELTA does not yield -1 for?
That is not how I expected COALESCE( ... ) to work.
Every other row for DELTA (including 'black')
comes out the way I expected.
Thanks for considering this,
John G.
| |
by: darrel |
last post by:
I have three tables making a many-to-many relationship:
Companies
CoID | CoName
Link
CoID | InsID
Insurers
InsID | InsName
|
by: db2group88 |
last post by:
hi, we are using db2 v8.2 EE on windows, with jcc driver. try to create
a join view, after that i check the syscat.columns table, for those
decimal field use COALESCE method, all the length become 11 instead of
the actual length of the field inside table, can anyone explain this to
me ? thank you
create view ML101PD.ARJMBAL1 (ARNM05, ARNO01, ARNM01, ARNO07, ARNO08,
ARNO09, ARFL17, ARFL03, ARMO12, ARDY12, ARCC12, ARYR12, ARNO15,...
|
by: Veeru71 |
last post by:
We have the following query and is taking considerable longer time to
run.
select A.*, B.name,B.user_type,C.task_id, c.queue_id, c.workid
FROM A, B, C
WHERE (B.user_type <2 and B.user_type is not null)
AND C.task_id = A.task_id
AND B.queue_id = C.queue_id
AND C.queue_id IN (1, 5, 9)
AND C.ready_status IN (1,2,3)
|
by: leebridgewater |
last post by:
Hello Group,
I'm new to SQL sever but I have managed to create a SQL based online
application and I just have a question that I'm sure someone here will
be able to help me with.
I have a database setup and from within this database I would like to
run a query to produce a simple report showing me the enquiry details
along with the network the enquiry member is from.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |