MySQL ReGex statement

Question

I have never come across RegEx in mySQL, In a column I have over 500 different server versions. is it possible to only fetch anything before a forward slash so that i can detect the most common servers by filtering out the versions?

Microsoft-IIS/7.5
Microsoft-IIS/6.0
Apache/2.2.24 (Unix)
Apache/2.2.15 (CentO
Apache/1.3.42 (Unix)
Apache/2.2.22 (Ubunt
Resin/3.0.23
Apache/2.2.0 (Linux/

Do you need a regex for this? It seems like 'index of' and 'substring' operations for / and ( are sufficient. — Patashu
– Patashu, Commented May 10, 2013 at 0:32

John Woo · Accepted Answer · 2013-05-10 00:33:32Z

2

here,

SELECT DISTINCT SUBSTRING(version,1,LOCATE('/', version)-1) FROM TableName

SQLFiddle Demo

because of DISTINCT, only unique rows are displayed.

answered May 10, 2013 at 0:33

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

David Garcia Over a year ago

Wow i just searched and noticed that some of my gathered website server has "'; DROP TABLE server" could this be done purposely by webmasters?

John Woo Over a year ago

someone has tried to mess with your database, how did you code in your php file?

David Garcia Over a year ago

Actually my database is run on my localhost, no outside access

David Garcia Over a year ago

i got it SELECT SUBSTRING(version,1,LOCATE('/', version)-1) as server, count(*) FROM TableName

David Garcia Over a year ago

I never new sql had those capabilities, well it has all come to fruition now, I wantto show you my results, oi41.tinypic.com/2qcic82.jpg what do you think, is for my disertation

|

zerkms · Accepted Answer · 2013-05-10 00:33:12Z

2

In mysql regex cannot return a matched substring, but only return a boolean if the string matches a regular expression or not.

What you need is just a SUBSTR

SUBSTRING(col, 1, LOCATE('/', col) - 1)

but keep in mind that if there is no / in the string you'll end up with an empty string as a result

answered May 10, 2013 at 0:33

zerkms

256k73 gold badges447 silver badges551 bronze badges

2 Comments

Christian Stewart Over a year ago

Yes, but that boolean can be used in a WHERE statement

zerkms Over a year ago

@Christian Stewart: can be, so? "is it possible to only fetch anything before a forward slash"

Collectives™ on Stack Overflow

MySQL ReGex statement

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related