Skip to main content

Regex within PostgreSQL

Regex (Regular expressions) are very useful. I had to make a SQL query to return only the firstname from a table where the field provides the full name.
ideia_onde=# SELECT name FROM users;
name
--------------------------------
Linus Torvalds
Randal Schwartz
Steve Wozniak
(3 rows)

ideia_onde=#
Now, with a simple substring using a regular expression:
SELECT SUBSTRING(name, '(.*?(\\s))') FROM users;
substring
-----------
Linus
Randal
Steve
(3 rows)

ideia_onde=#
What do I did?
The substring function extracts from the name field everything (.*) until (?) a space character (\\s).
Labels:

Comments

  1. Randal L. SchwartzOctober 9, 2009 at 4:14 PM

    Hey, I'm in good company there. thanks!

    And I've met both of the other guys! In fact, been on cruises with both of them, twice!

    ReplyDelete

Post a Comment

Popular posts from this blog

uSleep on windows (win32)

I am facing a terrible issue regarding timing on windows. Googling arround, I've found those infos: Using QueryPerformanceCounter and QueryPerformanceFrequency APIs in Dev-C++ ( http://yeohhs.blogspot.com/2005/08/using -queryperformancecounter-and_13.html ) QueryPerformanceCounter() vs. GetTickCount() http://www.delphifaq.com/faq/delphi_windows_API/f345.shtml How to time a block of code http://www.cryer.co.uk/brian/delphi/howto_time_code.htm And Results of some quick research on timing in Win32 http://www.geisswerks.com/ryan/FAQS/timing.html With that I'm trying to write something like a uSleep function for windows: # include<windows.h> void uSleep ( int waitTime){ __int64 time1 = 0, time2 = 0, sysFreq = 0; QueryPerformanceCounter((LARGE_INTEGER *)&time1); QueryPerformanceFrequency((LARGE_INTEGER *)&freq); do { QueryPerformanceCounter((LARGE_INTEGER *)&time2); // }while((((time2-time1)*1.0)/sysFreq)<waitTime); } while ( (time2-time1) <waitTime); } T...
Post a Comment
Read more

More trickery with gnuplot dumb terminal

In my post " Plotting memory usage on console " the chart doesn't pan the data. Now, using a named pipe, the effect got a little bit nicer. First, we have to run the memUsage.sh script to get a file filled with memory usage info: ./memUsage.sh > memUsage.dat & Then we have to create a named pipe: mkfifo pipe Now we have to run another process to tail only the last 64 lines from the memUsage.dat while [ 1 ]; do tail -64 memUsage.dat> pipe; done & And now we just have to plot the data from the pipe: watch -n 1 'gnuplot -e "set terminal dumb;p \"pipe\" with lines"' And that is it!
3 comments
Read more

Replace transparency in PNG images with white background (for lots of files...)

I had to remove transparency from a PNG image file from the command line... and stack overflow came into my help[1]... But I needed it for lots of files... then, adding a "while read line" did the job: ls -1 *.png |  cut -d . -f 1 | while read line; do convert -flatten $line.png flatten/$line.png ; done; [1] Replace transparency in PNG images with white background https://stackoverflow.com/questions/2322750/replace-transparency-in-png-images-with-white-background
Post a Comment
Read more