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 |
|
gsd
Starting Member
1 Post |
 Posted - 2005-09-21 : 09:39:37
|
| Good day,In My database, I have pc's grouped by their subnets. The query below lists all my pc's that have the subnet values specified. I need help writing a query that shows me all my pc's based on the third octet value of the subnet. ie the 19, 20, 22, etc. SELECT T0.[Guid], T1.* FROM [vResource] T0 INNER JOIN [Inv_AeX_AC_TCPIP] T1 ON T0.[Guid] = T1.[_ResourceGuid] WHERE T1.[Subnet] LIKE '10.177.19.0' OR T1.[Subnet] LIKE '10.177.20.0' OR T1.[Subnet] LIKE '10.177.21.0' OR T1.[Subnet] LIKE '10.177.22.0' OR T1.[Subnet] LIKE '10.177.23.0' OR T1.[Subnet] LIKE '10.177.24.0' OR T1.[Subnet] LIKE '10.177.72.0'Grateful for your help. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-09-21 : 10:13:36
|
You can access the 3rd Octet value by using the substring functionSELECT SUBSTRING('10.177.19.0', 8, 2)SELECT SUBSTRING('10.177.20.0', 8, 2)SELECT SUBSTRING('10.177.21.0', 8, 2)SELECT SUBSTRING('10.177.22.0', 8, 2) SELECT SUBSTRING('10.177.23.0', 8, 2) SELECT SUBSTRING('10.177.24.0', 8, 2) SELECT SUBSTRING('10.177.72.0', 8, 2)Duane.  |
 |
|
|
rheitzman
Starting Member
16 Posts |
Posted - 2005-09-21 : 19:54:41
|
| The SUBSTRING solution will work if the number of digits is consitent.Using the wildcard is another option:LIKE '10.177.19.%' I assume you don't have subnets like:10.177.1910.277.1910.377.19that you are trying to group. If so another solution is needed. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-09-21 : 19:57:34
|
maybeLIKE '%.%.19.%' Go with the flow & have fun! Else fight the flow  |
|
|
|
|
|
|
|
|
query writing help