Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-08 : 08:41:36
|
| Roger writes "I would like to search a text field (char,varchar ...) and select all the rows containing an IP address somewhere in the text field. for example I would like to select a row contianing.'123.1.2.3' as well as'http://123.123.123.123/default.htm'but I don't want to match '1.1234.2345.1234'is this possible in SQL server?" |
|
|
leedoolan
Starting Member
12 Posts |
Posted - 2002-07-08 : 08:45:49
|
| You need to use a like statement:i.e. select *from tablenamewhere colname like '%123.123.123%' or colname like '%123etc%'hope this helps |
 |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-07-08 : 10:32:32
|
| This should get you closer to the results:declare @tblIP table (fldIP varchar(20))insert @tblIP values('125.0.9.3')insert @tblIP values('125.0.9.3.0.88.9')insert @tblIP values('http://123.1.2.3')insert @tblIP values('62.123.123.123')insert @tblIP values('123.123.123.123')select * from @tblIPwhere fldIP like '%[0-9]%.[0-9]%.[0-9]%.[0-9]%' -- only numbers allowedand len(fldIP) - len(replace(fldIP, '.','')) = 3 -- only 3 dotsIt will work on varchar and char fields, but not text. It is not a complete solution, as it will display incorrect IPs and similiar patterns.HTH |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-08 : 10:41:55
|
Not sure about this...quote:
select * from @tblIP where fldIP like '%[0-9]%.[0-9]%.[0-9]%.[0-9]%' -- only numbers allowed and len(fldIP) - len(replace(fldIP, '.','')) = 3 -- only 3 dots
Would it not match 12A.13B.14C.1DE???? I think it would be very difficult to write an IP parser that will work in every scenario...HTHDaniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
|
|
|
Text Search containing IP address