Comparing 2 Columns until the 1st "."

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing 2 Columns until the 1st "."

Author

Topic

tricer_mdot
Starting Member

3 Posts

Posted - 2014-02-05 : 09:21:53

Hello,

I am new to SQL programming and I am trying to figure out how to get a report to show a mismatch in System Names & DNS Names. Both of the columns are in a table called nodes.

System Name router-1-dc and the DNS would be router-1-dc.domain I am trying to find Nodes that don't match to the "." prior to the domain example for this would be
System Name "router-1-datacenter" and DNS Name "router-1-dc.domain" I would want this example to show on the report page.

Here is the SQL Query I built however it does not appear to be working as I need it too.

SELECT N. NodeID, N.Caption, N.SysName, N.DNS, N.IP_Address, N.Device_Type
FROM (
SELECT Nodes.NodeID, Nodes.Caption, Nodes.SysName, Nodes.DNS, Nodes.Device_Type, Nodes.IP_Address
FROM Nodes
WHERE CHARINDEX('.',Nodes.SysName)>0 AND CHARINDEX('.',Nodes.DNS)>0
) N
WHERE SUBSTRING(N.SysName, 1, CHARINDEX('.',N.SysName)-1) <> SUBSTRING(N.DNS, 1, CHARINDEX('.',N.DNS)-1)
AND N.Device_Type = 'UPS'

ORDER BY 5 ASC, 2 ASC

Thanks in advance for the help

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2014-02-05 : 09:34:22

[code]
create table Nodes (NodeID int,Caption varchar(200),SysName varchar(200), DNS varchar(20), IP_Address varchar(20), Device_type varchar(20) )

insert into Nodes (NodeID, Caption, SysName, DNS, IP_Address, Device_Type)
values (1,'numone','router-1-dc','router-1-dc.domain','123','UPS' )
,(2,'numtoo','router-1-datacenter','router-1-dc.domain','123','UPS' )

SELECT N. NodeID, N.Caption, N.SysName, N.DNS, N.IP_Address, N.Device_Type
FROM (
SELECT Nodes.NodeID, Nodes.Caption, Nodes.SysName, Nodes.DNS, Nodes.Device_Type, Nodes.IP_Address
FROM Nodes
WHERE CHARINDEX('.',Nodes.DNS)>0
) N
WHERE N.SysName <> SUBSTRING(N.DNS, 1, CHARINDEX('.',N.DNS)-1)
AND N.Device_Type = 'UPS'

ORDER BY 5 ASC, 2 ASC[/code]

There are no "." in your SysName column.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tricer_mdot
Starting Member

3 Posts

Posted - 2014-02-05 : 10:42:55

Don,

I think that worked however I noticed a few that are still showing up below are examples

tta-lr-denton-hssp-ups1 tta-denton-hssp-ups1.domain - this one is correct
tha-cab67-ups1.domain tha-cab67-ups1.domain

It seems to only be a few that are doing it and its the ones that have the .domain in the system name as well as the dns name.

Thanks
Rich

tricer_mdot
Starting Member

3 Posts

Posted - 2014-02-07 : 08:20:36

I think I may need 2 different where statements is that possible?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-02-07 : 17:21:48

quote:
Originally posted by tricer_mdot

I think I may need 2 different where statements is that possible?

Short answer "no". BUT you can use logic operators, such as AND or OR, to supply additional match conditions. Additionally, if you have two or more result sets, you can combine them using UNION or UNION ALL.

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources