(MySQL) Using REGEXP with TRIM()

[LadyLinuX]

Sorry if this isn't in the right forum - it's really a MySQL question.

I might be trying to go about this wrong, or maybe it's not do-able.

I have a database where some records have information at the end one of the fields (in this case, the "name" field) that I don't want displayed - in this case it's a 1-3 digit number, followed by a space, followed by a string.

This expression does that in Coldfusion after the SQL statement has been executed, before displaying the output to the web page :

#ReReplace(name,'([0-9]+)([[:space:]]somestring$)','', 'ALL')#

I'd really like to do it in the SQL statement instead and then alias the result as a column so I can sort on it:

I tried this :

SELECT NAME, TRIM(NAME REGEXP ([0-9]+)([[:space:]]somestring$) FROM NAME) AS NEW_NAME

FROM table_name

ORDER BY NEW_NAME

But I'm getting a MySQL Syntax error. I'm sure part of the issue is bracketing - I tried various bracketing combinations, but still can't hit on the right combination and I'm not sure if TRIM is the function I should be using for this. Any help is greatly appreciated.

TIA,

LL

[coopster]

REGEXP [dev.mysql.com] will return 1 when a match is found, otherwise zero (0). It won't return where the pattern was found.

Can you give some example(s) of possible data in the name that better describes what you are trying to do?

[killroy]

I think what you want to do is a locate for the first space, or since you're dealing with just 3 cases "[a-z] " or "[a-z][a-z] " or "[a-z][a-z][a-z] " you might use a nested if.

SN

[jmccormac]

SELECT NAME, TRIM(NAME REGEXP ([0-9]+)([[:space:]]somestring$) FROM NAME) AS NEW_NAME

I am not familiar with the MySQL regexp but the double bracket thing looks wrong. Did you try a more standard [\\s]somestring$ instead? I think the double \\ is necessary because MySQL strips off the first \ when parsing the query.

Regards...jmcc

[LadyLinuX]

(thank you everyone for your answers)

(The double bracket & colons [[: :]] are POSIX) :)

Can you give some example(s) of possible data in the name that better describes what you are trying to do?

I'm being provided product data from about 10 different sources. One source includes quantity information in the product name column. This best approximates the data in that column without giving any clues about what project I am working on to my competitors (who also read WebmasterWorld) ;)

Joe's Canary Seed
Joe's Canary Seed 8 ounces
Joe's Canary Seed 12 ounces
Jane Doe Canary Seed
Jane Doe Canary Seed 16 ounces
John Smith Budgie Seedcakes
John Smith Budgie Seedcakes 24 ounces

Where "ounces" would be the equivalent of somestring in my example. What I want to do is filter out the quantity information - num ounces - or to SELECT the wanted information and give that an alias NEW_NAMEso that I can group the output by NEW_NAME... so it would return like this:

Joe's Canary Seed
Jane Doe Canary Seed
John Smith Budgie Seedcakes

Thanks again.

LL.

[killroy]

Well this returns " John Smith Budgie Seedcakes for both strings:

SET @a="John Smith Budgie Seedcakes 24 ounces";
SELECT IF(@a REGEXP " [0-9]+ ounces$"
,TRIM(TRAILING SUBSTRING_INDEX(@a," ",-2) FROM @a)
,@a);

SET @a="John Smith Budgie Seedcakes";
SELECT IF(@a REGEXP " [0-9]+ ounces$"
,TRIM(TRAILING SUBSTRING_INDEX(@a," ",-2) FROM @a)
,@a);

Just paste the whole thing into mysql to see the samples work.

SN

[jmccormac]

The simplest solution would be to pre-process the data before it even gets into the database. Thus you could slice the data so that anything with a quantity gets chopped with a regexp like [\s][1-9].*$ . Assuming the number is a marker and a valid required string does not have a number as part of it is should be easy to rip the raw data with a single sed or perl line.

Regards...jmcc

[killroy]

Did you try my code? I'd love to hear if you used it. I love doing long processing bits right in mysql. The engine is pretty efficient, and sometiems it's faster to do it there then in th escripting language afterwards.

I have some two page scripts that do nifty processing all in a single query...

SN