Need Regex Help

scottalanmiller

@scottalanmiller PostreSQL regex

Oh wow, I've never used regex in a database before. No idea of that syntax or where/when you'd use it.

1337

@scottalanmiller said in Need Regex Help:

@dustinb3403 said in Need Regex Help:

@scottalanmiller PostreSQL regex

Oh wow, I've never used regex in a database before. No idea of that syntax or where/when you'd use it.

It either used for string manipulation or string matching.

From the top of my head with syntax errors included.

For example manipulating strings (convert email to domain name)

select name, regexp_match(email,'.+@(.+)') as domain
from customers

For example matching to a simple regexp (all customers that have the has John somewhere in their name):

select * from customers where customer_name ~ 'John'

1337

@dustinb3403 said in Need Regex Help:

@scottalanmiller PostreSQL regex

It's easy to solve this problem with SQL - if that is a possibility.
You just need to use a subquery.

I'm assuming a table that contains one column with computer name and another with software installed. One row for each software installed.

For example a query to find all computers that have the software installed:

select computer_name 
from yourtable
where 
   software_installed='Microsoft' or
   software_installed='Anti-virus'
group by computer_name

Now we'll use that as a subquery to show all the computers that doesn't have the software installed:

select computer_name from yourtable
where 
   computer_name<>(
      select computer_name from yourtable 
      where 
         software_installed='Microsoft' or 
         software_installed='Anti-virus'
      group by computer_name
   )
group by computer_name

The group by is to show a computer name just one time, even if you have several rows that contains that name.

Note: Syntax errors probably included in my examples.

DustinB3403

@pete-s Thank you for the help, unfortunately it appears I'm limited to PostgresSQL regex like below or directly selecting the installed software from the list of thousands of items.

\b(?!:Microsoft|Anti-Virus)\b

I have no options for a subquery, at least that I can see at the moment.

1337

@dustinb3403 said in Need Regex Help:

@pete-s Thank you for the help, unfortunately it appears I'm limited to PostgresSQL regex like below or directly selecting the installed software from the list of thousands of items.

I have no options for a subquery, at least that I can see at the moment.

Yeah, well you're not going to find a regex solution. Simply because you need to look at several rows of information to determine if the computer has software or not. That's why it's not going to work.

If you could put have all the software into one column then you could use regex to find the rows that doesn't have the software installed. So if you had a string with "Microsoft,Libre,MyTax,Anti-virus " you could use regex on that and it would work fine.

DustinB3403

@pete-s yeah that is what I'm feeling as well. . . sadly. This really needs to be somehow capable with the tooling, even if I'm not able to implement it.

1337

@dustinb3403 said in Need Regex Help:

@pete-s yeah that is what I'm feeling as well. . . sadly. This really needs to be somehow capable with the tooling, even if I'm not able to implement it.

I haven't heard of BrightGauge before. Maybe it's big in the market, I don't know.

Regardless of that, it looks very capable on their website but that's because that's what marketing does - makes things look good.

The actual technical capability of the product is unknown until it's put to use. In your case unfortunately it looks like the product can't get the job done.

Maybe you're trying to hammer with a screwdriver or the hammer you have is just crap. In either case the support should be able to help you.

DustinB3403

@pete-s said in Need Regex Help:

@dustinb3403 said in Need Regex Help:

@pete-s yeah that is what I'm feeling as well. . . sadly. This really needs to be somehow capable with the tooling, even if I'm not able to implement it.

I haven't heard of BrightGauge before. Maybe it's big in the market, I don't know.

Regardless of that, it looks very capable on their website but that's because that's what marketing does - makes things look good.

The actual technical capability of the product is unknown until it's put to use. In your case unfortunately it looks like the product can't get the job done.

Maybe you're trying to hammer with a screwdriver or the hammer you have is just crap. In either case the support should be able to help you.

It very well may not be up to task, or it could very well be my limited use of the tool and familiarity with it.

travisdh1

@dustinb3403 said in Need Regex Help:

@pete-s Thank you for the help, unfortunately it appears I'm limited to PostgresSQL regex like below or directly selecting the installed software from the list of thousands of items.
\b(?!:Microsoft|Anti-Virus)\b
I have no options for a subquery, at least that I can see at the moment.

Are you able to access the database outside of whatever tooling they provide? Being PostgreSQL, you should be able to. Get to the point where you can write your own query and go from there (you can do most anything with the data you want then.)

DustinB3403

@travisdh1 No, backend database access isn't available to me.

travisdh1

@dustinb3403 said in Need Regex Help:

@travisdh1 No, backend database access isn't available to me.

Well, that's a little ****y. No db gui has given me everything I wanted to do with a database.

Unsolved Need Regex Help