SQL and Regular Expressions

[tbg130]

Hi,

I have a database that has a table with 2 fields; RegEx and Owner where RegEx is a regular expression of an IP address and and Owner is the text string name of the owner.

I need to be able to search through the RegEx field and match it to an IP address than return the Owner name.

Sample Table

^198\.103\.(3[2-9]|4[0-9]|5[0-3])\. || Nortel Networks

How can I get a match from an IP to a Regular Expression in SQL?

Ie.

Select Owner from TABLENAME where RegEx 'contains / equals / matches etc' '198.103.32.158'?

Is this at all possible?

Thanks in Advance.

Tyler

 

[rac2]

I used the search function on this page and found a link provided by JamesLean.

http://www.sqlteam.com/item.asp?ItemID=13947

 

[tbg130]

Looks promissing but that seems to be when the Regular Expression is known; I'm trying to match an IP to a Regular Expression that is not known; ie. it is a record in the database and I need to find any records that match it (and in theory, there should only ever be 1 match for an IP and Regular Expression).

Any other tips?

Thanks.

 

[rac2]

Well,

search for a regular pattern expression in a source string

could be taken to mean

search for a regular expression matching an IP address

although usually I would say a string matching a reg exp

In any case

SELECT Owner 
FROM MyTable
WHERE dbo.find_regular_expression('198.103.32.158', RegExp, 0) = 1

 

[tbg130]

This is definitely working now, however, I seem to have a strange issue. I have 2 tables for testing; one works perfectly, and the other doesn't! The first will match only 1 record and return the owner name, however, the second will return a whole pile of records; none of which match.

The only thing that has changed is that I have made a new table, added fields, and added more data.

Any ideas?

Thanks in Advance.

 

[tbg130]

Looks like anytime that there is a null in a field that you are comparing too, it matches; once I added 'n/a' to those fields, it worked.

Thanks again for all your help!

Tyler