[RESOLVED] Help with regexp

mrbaseball34

I need cases for /, #, spaces, and commas
I am horrible at regex's...

UPDATE linv_inventory
SET stripped_part_number = (
CASE 
 WHEN inventory_part_number REGEXP '.*-.*' THEN REPLACE(inventory_part_number,'-','')
END);

bradgrafelman

What type of cases? I could think of all kinds of cases that involve those characters, but you're probably not going to benefit from any of them unless you explain more about what it is you're trying to do.

mrbaseball34

Below is my replace array in PHP, I'd just rather do it in SQL...

$replace = array('-', ',', ' ', '*', '#', '/', '\\', '"', ':', ';', '!', '\'', '.', '?', '&', '^', '%', '$', '~', '`', '[', ']', '{', '}', '|', '+', '=', '_', '(', ')');

What I meant was that I needed the REGEXP term for each of the cases in my replace array above.

bradgrafelman

Is this a one-time only thing? If so, it might be easier to loop through the DB and use PHP to do the replacing... otherwise, you're going to have a rather long SQL query since SQL can't do regular expression replacing.

If it's not a one-time thing... why isn't it? Why is the data being inserted in a format that you don't want?

mrbaseball34

First, it isn't a one-time thing because our customers upload their inventory files and then we parse them using fgetcsv and import them into the DB.

...SQL can't do regular expression replacing

Not exactly...The SQL in the original post includes code to do the replace based on a regexp locate.

I'm not sure if it is any faster than nested replaces but most anything would be faster than the UDF posted here:
Strip Non Alpha UDF

mySQL does have it's limitations. ;-)

bradgrafelman

So you're in charge of parsing the uploaded files? In that case, I'd have to reiterate my earlier question:

bradgrafelman wrote:
Why is the data being inserted in a format that you don't want?

Why are you inserting garbage into your DB, and then later trying to go back through and clean it up?

Garbage in, garbage out... right? :p

mrbaseball34

bradgrafelman;10951854 wrote:
So you're in charge of parsing the uploaded files? In that case, I'd have to reiterate my earlier question:

Why are you inserting garbage into your DB, and then later trying to go back through and clean it up?

Garbage in, garbage out... right? :p

Well, the program was written by some worthless jerk several months ago and I just came on board last week.

I'M the one trying to get it to do things like they SHOULD be done.

If you don't know the real story, don't accuse.

Don't fret, I wrote a PHP script to do the bulk changes and added the same code to the import process.

I just liked the way it was done with the CASE but since everyone here likes to argue about WHY instead of posting a HOW, I decided to do it a different way altogether.

bradgrafelman

mrbaseball34 wrote:
If you don't know the real story, don't accuse.

"Accuse" ? Not sure what you think I was accusing you of, I was merely trying to understand why you weren't making the changes to the data as you parsed it, rather than inserting something that wasn't in the format you wanted.

mrbaseball34 wrote:
everyone here likes to argue about WHY instead of posting a HOW

So you'd rather everyone just posts code snippets all day long that do exactly what someone is trying to do rather than find the most logical/efficient/etc. way of solving a problem? :rolleyes:

Weedpacket

It was a perfectly reasonable (and obvious) question.

mrbaseball34 wrote:
everyone here likes to argue about WHY instead of posting a HOW

It's your problem; we're not sitting at your computer. If you want us to help you with it we have to understand the situation, right? Or shall we just waste our time and yours making suggestions you already knew wouldn't work but didn't tell us WHY?

mrbaseball34

While I don't want to continue this argument, was it not obvious from the original question what I wanted to do?

I clearly posted SQL code doing, almost, what I wanted to do. All I asked for was the REGEXP strings for the other chars I wanted to replace.

Then it got boiled up into the "Why aren't you cleaning it up in the first place?" kind of accusations.

The questions was clear and to the point, what was there not to understand?

Weedpacket

What was there not to understand? Why you were not cleaning it up in the first place. Primarily because it helps to frame the problem with more context, but I suspect there's a bit of the old-fashioned curiosity that led some of us to studying the subject in the first place.

Here's a quote from a Douglas Adams novel that illustrates the point:

Dirk Gently wrote:
"Let me give you an example. I fyou go to an acupuncturist with toothache he sticks a needle instead into your thigh. Do you know why he does that, Mrs Rawlinson? ... No, neither do I, Mrs Rawlinson, but we intend to find out."

Or something I saw on bash.org once:

<glyph> wrote:
For example - if you came in here asking "how do I use a jackhammer" we might ask "why do you need to use a jackhammer"

If the answer to the latter question is "to knock my grandmother's head off to let out the evil spirits that gave her cancer", then maybe the problem is actually unrelated to jackhammers

mrbaseball34

I am a recognized expert on Experts-Exchange in several topic areas. I recall that when I see questions similar to mine, it is not my place to ask WHY the OP needs this information, but leave it to him to justify the answer that I give. The only time I've ever asked WHY was when the asked questions concerning security circumvention.

The question I asked was clear if taken IN CONTEXT with the code that I posted.

Maybe your 18,000, and Brad's 13,000 posts have not taught either of you how to read that in people's questions, huh??

bradgrafelman

mrbaseball34;10952117 wrote:
While I don't want to continue this argument]

And yet you continued to argue... ?

mrbaseball34;10952117 wrote:
it is not my place to ask WHY the OP needs this information, but leave it to him to justify the answer that I give.

And what happens if the OP doesn't realize that there is a much better way of going about doing what he wants done?

mrbaseball34;10952117 wrote:
Maybe your 18,000, and Brad's 13,000 posts have not taught either of you how to read that in people's questions, huh??

Maybe... or perhaps you simply haven't learned how to be courteous to those trying to help you.

Maybe a 1 week break from the forums will help?