[SQL] simple SQL question
I have a column with the following values (example below) 5673 4731 4462 5422 756 3060 I want the column to display the numbers as follows: 56.73 47.31 44.62 54.22 7.56 30.60 I have been playing around with string functions but cannot seem to figure out a quick solution. Does anyone have any suggestions? Please let me know. Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] simple SQL question
On 6/25/07, Joshua <[EMAIL PROTECTED]> wrote: I have a column with the following values (example below) 5673 4731 4462 5422 756 3060 I want the column to display the numbers as follows: 56.73 47.31 44.62 54.22 7.56 30.60 I have been playing around with string functions but cannot seem to figure out a quick solution. Does anyone have any suggestions? Please let me know. Thanks. Divide by 100. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] simple SQL question
Joshua написа: > I have a column with the following values (example below) > > 5673 > 4731 > 4462 > 5422 > 756 > 3060 > > I want the column to display the numbers as follows: > > 56.73 > 47.31 > 44.62 > 54.22 > 7.56 > 30.60 > > I have been playing around with string functions but cannot seem to > figure out a quick solution. Does anyone have any suggestions? Use "to_char(int, text)", details here - http://www.postgresql.org/docs/current/static/functions-formatting.html -- Milen A. Radev ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] simple SQL question
On Monday 25 June 2007 09:38:39 Milen A. Radev wrote: > Joshua написа: > > I have a column with the following values (example below) > > > > 5673 > > 4731 > > 4462 > > 5422 > > 756 > > 3060 > > > > I want the column to display the numbers as follows: > > > > 56.73 > > 47.31 > > 44.62 > > 54.22 > > 7.56 > > 30.60 > > > > I have been playing around with string functions but cannot seem to > > figure out a quick solution. Does anyone have any suggestions? > > Use "to_char(int, text)", details here - > http://www.postgresql.org/docs/current/static/functions-formatting.html Don't use to_char unless you actually want character data though. Just try this... SELECT (column/100) FROM table; -- ~ manchicken <>< (A)bort, (R)etry, (I)nfluence with large hammer. 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] simple SQL question
> > I have a column with the following values (example below) > > > > 5673 > > 4731 > > 4462 > > 5422 > > 756 > > 3060 > > > > I want the column to display the numbers as follows: > > > > 56.73 > > 47.31 > > 44.62 > > 54.22 > > 7.56 > > 30.60 > > > > I have been playing around with string functions but cannot seem to > > figure out a quick solution. Does anyone have any suggestions? > > Don't use to_char unless you actually want character data though. > > Just try this... > > SELECT (column/100) FROM table; Cast the column in order to get the decimal part: SELECT (column::real/100) FROM table; ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] another simple SQL question
Ok here is another simple question from a novice Here is what my table looks like firstname lastname fullname -- -- --- smith, john green, susan white, jeff How can I break the fullname field into firstname lastname fields so it looks like the following: firstname lastname fullname - - - john smith smith, john susan green green, susan jeff white white, jeff Please let me know. Sorry for such simple novice questions, I appreciate your support. THANKS! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] simple SQL question
Joshua napisał(a): I have a column with the following values (example below) 5673 4731 4462 5422 756 3060 I want the column to display the numbers as follows: 56.73 47.31 44.62 54.22 7.56 30.60 I have been playing around with string functions but cannot seem to figure out a quick solution. Does anyone have any suggestions? if I read you correctly, I might suggest: select round(your_column/100.0, 2) as divided_by_100 from your_table; (note dividing by 100.0 (decimal), not just by 100 (integer)) Please let me know. Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] another simple SQL question
Joshua wrote: > Ok here is another simple question from a novice > > Here is what my table looks like > > firstname lastname fullname > -- -- --- > smith, john > green, susan > white, jeff > > > How can I break the fullname field into firstname lastname fields so it > looks like the following: > > firstname lastname fullname > - - - > john smith smith, john > susan green green, susan > jeff white white, jeff > > Please let me know. Sorry for such simple novice questions, I appreciate > your support. > How I would do it would be to write a trivial application program to do it. I spent a long time working on databases of telephone directory information, and we needed to look up people by name, by address, by town, etc. It turned out that the best way to handle finding-name fields was to leave the first, middle, and last names in one field. A big problem is multicultural. Some people (e.g., Chinese) tend to give family name first followed by given name. Others (e.g., English) tend to give given names first, followed by family name. Telephone operating companies do not get these things correct, so it better just to keep them together. The relational database management system we used, that I originally wrote for UNIX, allowed partial matching on fields, and I even put a SOUNDEX scheme on the name fields. Thus, a query like 'ristorante italiano' would locate 'Moms Pizza Italian Restaurant' without doing a sequential search of the entire database. Similarly, 'T S Eliot' would locate 'Eliot Thomas Stearns'. Problems remain, such as 'Doug Mc Ilroy' would not find 'M Douglas McIlroy" the way we built the program below. You might look here: http://ieeexplore.ieee.org/xpl/freeabs_all.jsp?tp=&arnumber=810466&isnumber=16537 for one way to do this. It explains briefly how to make a suitable index for it. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 12:25:02 up 4 days, 20:00, 3 users, load average: 4.25, 4.14, 4.12 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] simple SQL question
On Monday 25 June 2007 10:19:49 Fernando Hevia wrote: > > > I have a column with the following values (example below) > > > > > > 5673 > > > 4731 > > > 4462 > > > 5422 > > > 756 > > > 3060 > > > > > > I want the column to display the numbers as follows: > > > > > > 56.73 > > > 47.31 > > > 44.62 > > > 54.22 > > > 7.56 > > > 30.60 > > > > > > I have been playing around with string functions but cannot seem to > > > figure out a quick solution. Does anyone have any suggestions? > > > > Don't use to_char unless you actually want character data though. > > > > Just try this... > > > > SELECT (column/100) FROM table; > > Cast the column in order to get the decimal part: > > SELECT (column::real/100) FROM table; > > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend Good point. Thanks for the catch :) -- ~ manchicken <>< (A)bort, (R)etry, (I)nfluence with large hammer. 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] another simple SQL question
On Monday 25 June 2007 10:28:40 Joshua wrote: > Ok here is another simple question from a novice > > Here is what my table looks like > > firstname lastname fullname > -- -- --- > smith, john > green, susan > white, jeff > > > How can I break the fullname field into firstname lastname fields so it > looks like the following: > > firstname lastname fullname > - - - > john smith smith, john > susan green green, susan > jeff white white, jeff > > Please let me know. Sorry for such simple novice questions, I appreciate > your support. > > THANKS! > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend You may want to consider breaking those out into individual columns. It's much easier to put those together later on than it is to separate them out. -- ~ manchicken <>< (A)bort, (R)etry, (I)nfluence with large hammer. 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] yet another simple SQL question
Ok, You guys must be getting sick of these newbie questions, but I can't resist since I am learning a lot from these email lists and getting results quick! Thanks to everyone for their contributions. Here is my questions I have a column that looks like this firstname - John B Mark A Jennifer D Basically I have the first name followed by a middle initial. Is there a quick command I can run to strip the middle initial? Basically, I just need to delete the middle initial so the column would then look like the following: firstname --- John Mark Jennifer Thanks again for all of your help today. Everything you guys have been sending has produced successful results. Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] yet another simple SQL question
On Monday 25 June 2007 12:44:25 Joshua wrote: > Ok, > > You guys must be getting sick of these newbie questions, but I can't > resist since I am learning a lot from these email lists and getting > results quick! Thanks to everyone for their contributions. > > Here is my questions > > I have a column that looks like this > > firstname > - > John B > Mark A > Jennifer D > > Basically I have the first name followed by a middle initial. Is there a > quick command I can run to strip the middle initial? Basically, I just > need to delete the middle initial so the column would then look like the > following: > > firstname > --- > John > Mark > Jennifer > > Thanks again for all of your help today. Everything you guys have been > sending has produced successful results. > > Thanks. > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq Well, the simple way I could think of to do this would to be a simple regex (the query mock-up below is untested)... select regexp_replace(COLUMN, '(.*)\\s\\w$', '\\1', 'g') ... This doesn't seem like a difficult thing to do in application code. It seems like it makes more sense to do it there. -- ~ manchicken <>< (A)bort, (R)etry, (I)nfluence with large hammer. 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html ---(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] yet another simple SQL question
am Mon, dem 25.06.2007, um 12:44:25 -0500 mailte Joshua folgendes:
> Ok,
>
> You guys must be getting sick of these newbie questions, but I can't
> resist since I am learning a lot from these email lists and getting
> results quick! Thanks to everyone for their contributions.
>
> Here is my questions
>
> I have a column that looks like this
>
> firstname
> -
> John B
> Mark A
> Jennifer D
>
> Basically I have the first name followed by a middle initial. Is there a
> quick command I can run to strip the middle initial? Basically, I just
> need to delete the middle initial so the column would then look like the
> following:
>
> firstname
> ---
> John
> Mark
> Jennifer
Yes, of course:
test=# select split_part('My Name', ' ', 1);
split_part
My
(1 row)
And now, i think, you should read our fine manual:
http://www.postgresql.org/docs/current/interactive/
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 6: explain analyze is your friend
Re: [SQL] yet another simple SQL question
On mán, 2007-06-25 at 12:44 -0500, Joshua wrote: > I have a column that looks like this > > firstname > - > John B > Mark A > Jennifer D > > Basically I have the first name followed by a middle initial. Is there a > quick command I can run to strip the middle initial? how about: select regexp_replace(firstname,' .*','') as firstname from footable; or: select substring(firstname FROM '(.*) ') as firstname from footable; gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] yet another simple SQL question
On Jun 25, 2007, at 12:44 PM, Joshua wrote: Ok, You guys must be getting sick of these newbie questions, but I can't resist since I am learning a lot from these email lists and getting results quick! Thanks to everyone for their contributions. Here is my questions I have a column that looks like this firstname - John B Mark A Jennifer D Basically I have the first name followed by a middle initial. Is there a quick command I can run to strip the middle initial? Basically, I just need to delete the middle initial so the column would then look like the following: firstname --- John Mark Jennifer Thanks again for all of your help today. Everything you guys have been sending has produced successful results. Try something along the lines of: SELECT substring(firstname from '^(\w*)\W') from table_name; Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] NO DATA FOUND Exception
Hi. Im taking my first steps with plpgsql. I want my function to react to the result of a query in the following way: begin select column into variable from table where condition; exception when <> then return variable; when <> then <> ; when <> then <> ; end ; Is something like this possible en plpgsql without recurring to a select count(*) to check how many results I will get? Actual code is: CREATE OR REPLACE FUNCTION test(p_line text) RETURNS text AS $body$ DECLARE v_len integer DEFAULT 8; v_search varchar; v_register num_geo%ROWTYPE; BEGIN -- Search loop WHILE v_len > 0 LOOP v_search := substring(p_line, 1, v_len); begin SELECT * INTO v_register WHERE prefix = v_search; exception when no_data then -- Getting error here continue; when others then return v_register.prefix; end; v_len := v_len - 1; END LOOP; raise 'Not found'; END; $body$ LANGUAGE 'plpgsql' VOLATILE ; ERROR: unrecognized exception condition "no_data" SQL state: 42704 Context: compile of PL/pgSQL function "test" near line 14 Thanks, Fernando. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] NO DATA FOUND Exception
On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote: > when <> then return variable; > when <> then <> ; > when <> then <> ; Check out the FOUND variable in the documentation for the first two, and the "trapping errors" section for the latter. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] NO DATA FOUND Exception
>On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote: >> when <> then return variable; >> when <> then <> ; >> when <> then <> ; > >Check out the FOUND variable in the documentation for the first two, >and the "trapping errors" section for the latter. > >Andrew Sullivan | [EMAIL PROTECTED] Thanks for the tip. I was looking in the wrong place. The FOUND variable is explained in chapter "37.6.6. Obtaining the Result Status". Thanks again, Fernando. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] NO DATA FOUND Exception
[Please create a new message to post about a new topic, rather than replying to and changing the subject of a previous message. This will allow mail clients which understand the References: header to properly thread replies.] On Jun 25, 2007, at 14:20 , Fernando Hevia wrote: Is something like this possible en plpgsql without recurring to a select count(*) to check how many results I will get? I think you want to look at FOUND. http://www.postgresql.org/docs/8.2/interactive/plpgsql- statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS For example: # select * from foos; foo - bar baz bat (3 rows) # CREATE FUNCTION foos_exist() RETURNS boolean LANGUAGE plpgsql AS $body$ DECLARE v_foo TEXT; BEGIN SELECT INTO v_foo foo FROM foos; IF FOUND THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $body$; CREATE FUNCTION # select foos_exist(); foos_exist t (1 row) # truncate foos; TRUNCATE TABLE test=# select foos_exist(); foos_exist f (1 row) Actual code is: CREATE OR REPLACE FUNCTION test(p_line text) RETURNS text AS $body$ DECLARE v_len integer DEFAULT 8; v_search varchar; v_register num_geo%ROWTYPE; BEGIN -- Search loop WHILE v_len > 0 LOOP v_search := substring(p_line, 1, v_len); begin SELECT * INTO v_register WHERE prefix = v_search; exception when no_data then -- Getting error here continue; when others then return v_register.prefix; end; v_len := v_len - 1; END LOOP; I think you might want to rewrite this using some of the information here: http://www.postgresql.org/docs/8.2/interactive/plpgsql-control- structures.html#PLPGSQL-RECORDS-ITERATING For example, your inner loop could loop could look something like this: FOR v_register IN SELECT * FROM WHERE prefix = v_search LOOP return v_register.prefix; END LOOP; If no data is found, the loop won't do anything. However, it looks like you're trying to return a set of results (i.e., many rows), rather than just a single row. You'll want to look at set returning functions. One approach (probably not the best) would be to expand p_line into all of the possible v_search items and append that to your query, which would look something like: SELECT prefix FROM WHERE prefix IN (). Another way to do this might be to not use a function at all, but a query along the lines of SELECT prefix FROM WHERE p_line LIKE prefix || '%'; Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
