[SQL] Needs Query
In a table, a column contains Suneelll Sudhakarr Vivekk Ramana But my output is Suneel Sudhakar Vivek Ramana I want a query to get my output like above Please solve my problem ASAP Regards penchal Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review at http://www.techmahindra.com/Disclaimer.html externally and http://tim.techmahindra.com/Disclaimer.html internally within Tech Mahindra. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] Needs Query
am Tue, dem 08.05.2007, um 17:48:47 +0530 mailte Penchalaiah P. folgendes: > > > > > > > > > In a table, a column contains > > > > Suneelll > > Sudhakarr > > Vivekk > > Ramana > > > > > > But my output is > > Suneel > > Sudhakar > > Vivek > > Ramana > > I want a query to get my output like above??.. write a stored proc. within this proc: (oversimplified) - calculate the length of the string with length() -> length - extract substring(string, length, length) -> last_char - regexp_replace(string, last_char||'*$', last_char) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] select slows from 3 seconds to 30 seconds
On Saturday 05 May 2007 01:57, Tom Lane wrote: > Gary Stainburn <[EMAIL PROTECTED]> writes: > > I have the query below which when run takes approx 3 seconds. However > > when I add the condition 'and w_ws_id = 10' onto the end changes to 30+ > > seconds. Can anyone see why? I've included the explain for the long > > select. > > You really ought to provide EXPLAIN ANALYZE output for both versions if > you want intelligent commentary --- otherwise we're just guessing. > > But I would guess the problem is that the planner is way off about the > > number of rows satisfying the joint condition --- it thinks two: > >-> Index Scan using walon_hide_index on walon > > (cost=0.00..85.04 rows=2 width=140) Index Cond: (w_hide = 0) > > Filter: (w_ws_id = 10) > > This plan is good if there really are only a couple rows, but degrades > quickly if there are many... > > regards, tom lane Thanks Tom I'll bear that in mind in future. The problem was fixed by running a 'vacuum analyse' so it looks like the planner was getting confused. Gary -- 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 2: Don't 'kill -9' the postmaster
Re: [SQL] Query RE using COPY
On 5/8/07, Paul Lambert <[EMAIL PROTECTED]> wrote: Right now the software that does the extracts is developed by our applications developers, but I'll be taking that onto my side in the near future, just looking for a workaround until then. Sorry, but there isn't one. An unqualified COPY expects all columns and the syntax I gave you is the only way to limit the columns for insertion. You could write your own server-side function to parse, split, and insert the data, but it would be slower than COPY. Likely, the easiest thing would be to change your company's software. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Needs Query
or if you want it in the one query: create table _t(f varchar(50)); select f, case when substr(f, length(f), 1)= substr(f, length(f)-1, 1) then regexp_replace(f, substr(f, length(f), 1)||'+$', substr(f, length(f), 1)) else f end from _t where length(f)>1; On 5/8/07, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Tue, dem 08.05.2007, um 17:48:47 +0530 mailte Penchalaiah P. folgendes: > > > > > > > > > In a table, a column contains > > > > Suneelll > > Sudhakarr > > Vivekk > > Ramana > > > > > > But my output is > > Suneel > > Sudhakar > > Vivek > > Ramana > > I want a query to get my output like above??.. write a stored proc. within this proc: (oversimplified) - calculate the length of the string with length() -> length - extract substring(string, length, length) -> last_char - regexp_replace(string, last_char||'*$', last_char) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Error: Input string was not in a correct format
Hi Michael & Tom, First of all, thanks to Michael for your response. I did enable logging of SQL queries but still I could find no SQL statements in the logs. This led me to believe it was a client-side error. Then I saw the response from Tom which confirmed what I was thinking, so my second set of thank-you's goes out to Tom. I declared a function as follows: test_func(integer, character varying, integer, character varying). I used Visual Studio's Server Explorer to view the function and it showed up as test_func(integer, integer, character varying, character varying). The problem is that Server Explorer groups parameters together according to data type. If you follow the function declaration according to Server Explorer, you will end up with mismatched parameters. If you follow the function declaration according to pgAdmin, your code will work. From now on, at least until the problem in .NET is fixed, I will be using pgAdmin to view my functions instead of Visual Studio's Server Explorer. FYI, I am using CoreLab's PostgreSQL.NET drivers, I've reported the issue to them as well. At the time I didn't know if it was an issue with the .NET drivers or with PostgreSQL so I reported my findings to this forum as well as theirs. Ok then, that's a mouthful, thanks again guys for the help. Rommel Edwards, Barbados, (sunny) Caribbean. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
