select field values w/a special prefix using regExpression type match.

DB = Postgress

I want to select only values that are prefixed with  "d_mc". example of field values are  "d_mc###", "d_mm###", "d_ma###"

How do I do that form an a Select statement ?  All of the following just seem to return everything.

select relname, substring(relname, '\d+$') as my_Number 
     from pg_stat_user_tables
     -- where substring(relname, 'd_mc\d+$')
     -- where relname like "d_mc\D%"
    where relname like 'd_mc[0-9]+'

Open in new window


Thanks
sidwelleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OMC2000Commented:
select relname, substring(relname, '\d+$') as my_Number
from pg_stat_user_tables where relname1 like 'd_mc%'
0
sidwelleAuthor Commented:
Yes, but that expression returns values that meet that condition and more.  
I want on only the values that specifically have a prefix of "d_mc#+"
Examples of table names:  d_mc255,  d_mm255, dm255 ....

I don't want: d_mm255, dm255 ...

Thanks
0
OMC2000Commented:
This query returns only values havin prefix 'd_mc', it does not return values d_mm255, dm255.
Do you mean filter for non numeric values like 'd_mcabc'?
0
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

sidwelleAuthor Commented:
The query that I ran that's you suggested did return the values of:  d_mc255,  d_mm255, dm255 ....

What I do want to see in my results: d_mc255, d_mc104, d_mc321...
what I don't want to see in results: d_mm255, d_dm104,  d_ma321...

Thank you.
0
OMC2000Commented:
It's very strange. I can't rerun the query right now, but it could not return values like d_mm. I'll check it later again
0
OMC2000Commented:
I checked this query

select relname, substring(relname, '\d+$') as my_Number 
from pg_stat_user_tables 
where relname like 'd_mc%'

Open in new window


again, it returns

d_mc255

and does not return

 d_mm255, dm255
0
sidwelleAuthor Commented:
-- When I run this cmd, it works as expected:
select relname, substring(relname, '\d+$') as Ch_Number
    from pg_stat_user_tables
       where relname like 'd_mc%'

-- but also brings values like:  d_mcm255, dmcc104  <-- I don't want these values.

-- When I run this cmd, it returns everything !:
select relname, substring(relname, '\d+$') as Ch_Number , last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
    from pg_stat_user_tables
        where relname like 'd_mc%'
0
OMC2000Commented:
try this

select relname, substring(relname, '\d+$') as Ch_Number 
    from pg_stat_user_tables
       where relname  ~ 'd_mc\d+$'

Open in new window


and the number of rows returned could not differ in dependence of the number of fields in select clause
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sidwelleAuthor Commented:
Thanks, works

That's what I needed to see.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Regular Expressions

From novice to tech pro — start learning today.