need help understanding REGEXP_REPLACE in Oracle
I've been slowing letting REGEXP in Oracle sink in, from several recent and helpful EE posts.
For these two examples, they seem to me like they do the same but there are exact opposite results:
for 'SMI$TH' this
SELECT REGEXP_REPLACE(UPPER('&cus
returns 'SMITH'
- doesn't this mean: "Starting at the beginning of the string (^), find any characters in A-Z or a-z and replace it with nothing." Which would mean result = $, but obviously it's the OPPOSITE of that.
for 'SMI$TH' this
SELECT REGEXP_REPLACE(UPPER('&cus
returns $
- this means to me: "Find any alpha in the string and replace it with nothing". And so it does.
so I must be missing the proper interpretation of the first example . . .
For these two examples, they seem to me like they do the same but there are exact opposite results:
for 'SMI$TH' this
SELECT REGEXP_REPLACE(UPPER('&cus
returns 'SMITH'
- doesn't this mean: "Starting at the beginning of the string (^), find any characters in A-Z or a-z and replace it with nothing." Which would mean result = $, but obviously it's the OPPOSITE of that.
for 'SMI$TH' this
SELECT REGEXP_REPLACE(UPPER('&cus
returns $
- this means to me: "Find any alpha in the string and replace it with nothing". And so it does.
so I must be missing the proper interpretation of the first example . . .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great ! thanks a lot, that's also not explained in the book . . .
ASKER
It just seems the " ^ " should be outside the brackets, but oh well.
>>It just seems the " ^ " should be outside the brackets, but oh well.
It 'should' when you want the beginning of the line.
When dealing with character classes, it is inside and means 'NOT'.
Consider it an 'overloaded' operator...
I bet it is in your book somewhere...
It 'should' when you want the beginning of the line.
When dealing with character classes, it is inside and means 'NOT'.
Consider it an 'overloaded' operator...
I bet it is in your book somewhere...
ASKER
>>I bet it is in your book somewhere...
- I've studied this inside and out, I don't see it. He's got 7 pages on REGEXP, compared to the 600+ page time your or sdstuber recommended. Plus the tutorial you just linked is a bit longer than 7 pages.
But good on this, thanks.
- I've studied this inside and out, I don't see it. He's got 7 pages on REGEXP, compared to the 600+ page time your or sdstuber recommended. Plus the tutorial you just linked is a bit longer than 7 pages.
But good on this, thanks.
The '^' is acted as NOT inside the braces as Slightwv stated in his post.
[^A-Za-z] matches any single character that is not a upper case letter "A" to "Z" or lowercase letter from "a" to "z".
SELECT REGEXP_REPLACE(UPPER('&cus
these will return $ as output.
here is regular expression details.
http://en.wikipedia.org/wiki/Regular_expression
[^A-Za-z] matches any single character that is not a upper case letter "A" to "Z" or lowercase letter from "a" to "z".
SELECT REGEXP_REPLACE(UPPER('&cus
these will return $ as output.
here is regular expression details.
http://en.wikipedia.org/wiki/Regular_expression
>>- I've studied this inside and out, I don't see it. He's got 7 pages on REGEXP
You need a better 'book'... ;)
Remember the O'Reilly book I suggest in a previous question or was that a different Member? If not: I like the O'Reilly book on regular expressions. It covers some aspects of regular expressions that Oracle doesn't allow but still a really good book.
There is WAY too much to cover on regular expression than can fit in 7 pages. That is why there are entire books just on them not just a section in a single book.
You need a better 'book'... ;)
Remember the O'Reilly book I suggest in a previous question or was that a different Member? If not: I like the O'Reilly book on regular expressions. It covers some aspects of regular expressions that Oracle doesn't allow but still a really good book.
There is WAY too much to cover on regular expression than can fit in 7 pages. That is why there are entire books just on them not just a section in a single book.
ASKER
praveencpk, thanks for the tips. sorry I had already awarded points.
>>Remember the O'Reilly book I suggest in a previous question
- yes, I do. I just don't think 600 pages is needed for Regular Expression for Oracle. Maybe 20 ? or 50 ? but 600 ???
>>There is WAY too much to cover on regular expression than can fit in 7 pages.
- as I now see. The author should make reference to it, but it just presents it like it's substr and then moves along.
I've been programming for a while, mostly in Oracle. Nothing fancy, but I've always been able to figure out something with little effort, as it normally makes "sense", like good programming should. But this RegExp is like the geeks gone wild, kind of like when you had to give hieroglyphics to print landscape in Lotus123. It shouldn't be that complicated. But I'll keep studying.
If there's an Oracle book, like 100 pages or so, that's what I would go for.
>>Remember the O'Reilly book I suggest in a previous question
- yes, I do. I just don't think 600 pages is needed for Regular Expression for Oracle. Maybe 20 ? or 50 ? but 600 ???
>>There is WAY too much to cover on regular expression than can fit in 7 pages.
- as I now see. The author should make reference to it, but it just presents it like it's substr and then moves along.
I've been programming for a while, mostly in Oracle. Nothing fancy, but I've always been able to figure out something with little effort, as it normally makes "sense", like good programming should. But this RegExp is like the geeks gone wild, kind of like when you had to give hieroglyphics to print landscape in Lotus123. It shouldn't be that complicated. But I'll keep studying.
If there's an Oracle book, like 100 pages or so, that's what I would go for.
>>But this RegExp is like the geeks gone wild
Anything powerful is complex.
Buy the O'reilly book and skip every 3rd page?
You don't need to read it word for word. Just scan it to get the 'basics' that will trigger a "I think I read that" response when you stumble.
To be honest, I never made it ALL the way through it. I started scanning about half the way through.
Want to see how far down the rabbit hole you can go?
Have you looked at some of the examples for something you would think is simple: Validate an IPV4 address.
Note: Not everything on this site is Oracle Compatible but most things can be 'ported'.
http://www.regexlib.com/Search.aspx?k=IP+address&AspxAutoDetectCookieSupport=1
Want to double down? Try email addresses...
There are some good ones on the regexlib site!!!!!
Anything powerful is complex.
Buy the O'reilly book and skip every 3rd page?
You don't need to read it word for word. Just scan it to get the 'basics' that will trigger a "I think I read that" response when you stumble.
To be honest, I never made it ALL the way through it. I started scanning about half the way through.
Want to see how far down the rabbit hole you can go?
Have you looked at some of the examples for something you would think is simple: Validate an IPV4 address.
Note: Not everything on this site is Oracle Compatible but most things can be 'ported'.
http://www.regexlib.com/Search.aspx?k=IP+address&AspxAutoDetectCookieSupport=1
Want to double down? Try email addresses...
There are some good ones on the regexlib site!!!!!
ASKER
slightvw,
>>Anything powerful is complex.
- like love ???
>>Buy the O'reilly book and skip every 3rd page?
- but the price isn't 2/3rds . . .
>>Validate an IPV4 address.
- maybe next week, but good point.
>>Want to double down? Try email addresses...
- good exercise, but hasn't someone already done that ??? that's the method I want . . . :-)
>>Anything powerful is complex.
- like love ???
>>Buy the O'reilly book and skip every 3rd page?
- but the price isn't 2/3rds . . .
>>Validate an IPV4 address.
- maybe next week, but good point.
>>Want to double down? Try email addresses...
- good exercise, but hasn't someone already done that ??? that's the method I want . . . :-)
>>- maybe next week,
>>- good exercise
I didn't mean for you to 'try' it as an exercise. I meant look at the ones posted on regexlib to get an idea of how complex what seems 'simple' can be.
But feel free to try one of your own before you go looking to see what others have posted.
>>- but the price isn't 2/3rds . . .
There is some online stuff and I remember sdstuber pointed you to a website that had good references but...
$20 with shipping used on Amazon. How many hours would you need to find 'decent' stuff online that may or may not be correct then try to absorb what it was telling you versus the hours to sit down with a good book.
Starting out: The Oracle online docs and most websites are HORRIBLE for learning. They are good for reference or helping fill in the gaps but you need to basics to know where to look.
>>- like love ???
*blushes* Sorry but I'm spoken for.... Can we just be friends?
;) KIDDING!!!!!!
>>- good exercise
I didn't mean for you to 'try' it as an exercise. I meant look at the ones posted on regexlib to get an idea of how complex what seems 'simple' can be.
But feel free to try one of your own before you go looking to see what others have posted.
>>- but the price isn't 2/3rds . . .
There is some online stuff and I remember sdstuber pointed you to a website that had good references but...
$20 with shipping used on Amazon. How many hours would you need to find 'decent' stuff online that may or may not be correct then try to absorb what it was telling you versus the hours to sit down with a good book.
Starting out: The Oracle online docs and most websites are HORRIBLE for learning. They are good for reference or helping fill in the gaps but you need to basics to know where to look.
>>- like love ???
*blushes* Sorry but I'm spoken for.... Can we just be friends?
;) KIDDING!!!!!!
ASKER
all good, thanks.
I did find this, but it's Oracle 10g / 2003. Seems like someone needs to do an update.
>>Can we just be friends?
of course ! I'm past my prime, anyway . . .
I did find this, but it's Oracle 10g / 2003. Seems like someone needs to do an update.
>>Can we just be friends?
of course ! I'm past my prime, anyway . . .
>>I did find this,
Cannot review that one. I prefer O'Reilly books so it probably isn't a waste of $$$. Just never seen it. Mastering (the Owl), I own so I can suggest it.
All I can say I don't think that regular expressions have made major leaps in recent years. Sort of like basic Unix commands: If you know them from 10 years ago, you are probably safe today. Should be the same with regex. I wouldn't be afraid of learning the basics from something that old.
Then again, I've not kept up on regex trends.
The last time I had a complex regex question I asked it here in the Perl Topic Area. The Perl folks tend to be the best that I've found when it comes to regex.
The problem with that is you'll need to have the basics because Perl regex isn't 100% compatible with Oracle's. I had to port what they gave me and learn to ask in translated Perl syntax.
It was an interesting question...
Cannot review that one. I prefer O'Reilly books so it probably isn't a waste of $$$. Just never seen it. Mastering (the Owl), I own so I can suggest it.
All I can say I don't think that regular expressions have made major leaps in recent years. Sort of like basic Unix commands: If you know them from 10 years ago, you are probably safe today. Should be the same with regex. I wouldn't be afraid of learning the basics from something that old.
Then again, I've not kept up on regex trends.
The last time I had a complex regex question I asked it here in the Perl Topic Area. The Perl folks tend to be the best that I've found when it comes to regex.
The problem with that is you'll need to have the basics because Perl regex isn't 100% compatible with Oracle's. I had to port what they gave me and learn to ask in translated Perl syntax.
It was an interesting question...
ASKER
I posted the wrong one, here's the 66-page book that looks good, but only through 10g. I will probably start with this. I did notice, however, that Regexp_Count is new to 11g.
Thx.
Thx.
The problem with that book is it is a 'reference'. Might as well stick to the Oracle docs and online references.
For example: You will see the '^' in the index with a few pages that talk about it and might see a simple example but it probably won't explain much behind it.
For example: You will see the '^' in the index with a few pages that talk about it and might see a simple example but it probably won't explain much behind it.
ASKER
Ok, sounds good, thx.
ASKER
makes perfect sense, and answers a question I had a while back, how to say "not this pattern", but I never posted the question.
I checked my book first, and all I saw was "^ = matches the position at the start of the string".
What do the brackets mean ?