Go Back   Cyber Tech Help Support Forums > Software > Web Development & Graphic Design

Notices

Reply
 
Topic Tools
  #1  
Old June 21st, 2011, 08:51 PM
data_hugger data_hugger is offline
New Member
 
Join Date: Jul 2006
Posts: 4
Regular Expression Help need!

Hi, I am attempting to select the first alphanumeric sequence from a specific field in an oracle database that is greater then 3 charcters and does not contain a '/' as one of the characters.

This what I have so far.
SELECT SUBSTR(address,1,(INSTR(address,' ',1,1)-1)) "First Word"
FROM Foo;

This sucessfully grabs the first word, but it returns any first word bounded by white space.
So I get all the first words like:
Giles
SO
e/w
147

I would like to ignore anything smaller then 2 character and anything that contains a '/'.

Any advice.
Thanks in advance for your time!
Reply With Quote


  #2  
Old June 22nd, 2011, 04:30 PM
z1p's Avatar
z1p z1p is offline
Cyber Tech Help Moderator
 
Join Date: Oct 2004
O/S: Windows Vista 64-bit
Location: Massachusetts
Posts: 6,668
Take a look at REGEX_SUBSTR. I haven't used it myself, butt it looks like what you need. A pattern something like '^[alnum]{3}[alnum]?[:space:]' should do it. (Start of line, followed by 3alphanumerics, followedby 0 or more alphanumerics, followed by white space)

Let us know how it goes or if you need more info or help.
Reply With Quote
  #3  
Old June 22nd, 2011, 09:11 PM
data_hugger data_hugger is offline
New Member
 
Join Date: Jul 2006
Posts: 4
Hey, Thanks for the response.

I attempted to use this
SELECT
REGEXP_SUBSTR('500 oracle parkway, redwood shores, ','^[alnum]{3}[alnum]?[:space:]') "REGEXPR_SUBSTR"
FROM DUAL;

This is the result I recieve when I do it:
REGEXPR_SUBSTR
null

I removed the carrot '^' from the front of the regular expression and It return the following result.

REGEXPR_SUBSTR
a

I was expecting it to return '500' at the least.

Anyway I still think I am on the right track now. Let me know if you have any other advice. I will try and report back here after I figure it out.
Reply With Quote
  #4  
Old June 22nd, 2011, 09:41 PM
data_hugger data_hugger is offline
New Member
 
Join Date: Jul 2006
Posts: 4
Okay This does what I want it to do so far.

SELECT
REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores','[[:alnum:]]+[[:space:]]') "REGEXPR_SUBSTR"
FROM DUAL;

Returns:
REGEXPR_SUBSTR
500

Okay this does exactlly what I need it to do.
So simple. Le sigh!

SELECT
REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores','[[:alnum:]]+') "REGEXPR_SUBSTR"
FROM DUAL;

Returns: (without the trailing whitespace)
REGEXPR_SUBSTR
500

No this does what I want it to do I think :-/:
SELECT
REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores','[[:alnum:]][[:alnum:]]+') "REGEXPR_SUBSTR"
FROM DUAL;


Okay I am for real this time: (This ignores any punction in the string and grabs the first word that appears without trailing white space) tada!
SELECT
REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores','[^[unct:]][[:alnum:]][[:alnum:]]+') "REGEXPR_SUBSTR"
FROM DUAL;

Last edited by data_hugger; June 22nd, 2011 at 09:57 PM.
Reply With Quote
  #5  
Old June 22nd, 2011, 09:49 PM
z1p's Avatar
z1p z1p is offline
Cyber Tech Help Moderator
 
Join Date: Oct 2004
O/S: Windows Vista 64-bit
Location: Massachusetts
Posts: 6,668
sorry apparently didn't get the syntax correct...

If we take the corrected syntax from your last post and apply it to my post we can come up with:
^[[:alnum:]]{3}[[:alnum:]]?[[:space:]]
Hopefully, this is the correct syntax and works better. The caret should anchor the matching to the beginning of the string and the two alnum items should insure that the match is at least 3 characters long.
Reply With Quote
Reply

Bookmarks

Topic Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump




All times are GMT +1. The time now is 05:01 PM.