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?
 
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
 
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
 
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
 
sidwelleAuthor Commented:
Thanks, works

That's what I needed to see.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.