Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comparing 2 Columns until the 1st "." Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 11:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 6:42 AM
Points: 3, Visits: 4
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.

The tricky part is that some of the system names have the ".domain" and some don't.

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
Post #1539316
Posted Friday, February 7, 2014 11:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 3,630, Visits: 8,138
Could you post some sample data in the form of insert statements?
It would really help us to test possible solutions before posting.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539326
Posted Friday, February 7, 2014 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 6:42 AM
Points: 3, Visits: 4
qta-subbld4-swt1-ups1 qta-sub-bldg4-swt1-ups1.domain <-should show
qta-lr-china-hssp-ups1 qta-china-hssp-ups1.domain <-should show
sha-cab67-ups1.domain sha-cab67-ups1.domain <-should NOT how, but does
sha-cab78-ups1.domain sha-cab78-ups1.domain <-should NOT how, but does
sha-cab4009-pdu1.doamin sha-cab4009-ups1.domain <-should show

There you go

Thanks
Post #1539344
Posted Friday, February 7, 2014 1:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 13,002, Visits: 12,419
rtrice (2/7/2014)
qta-subbld4-swt1-ups1 qta-sub-bldg4-swt1-ups1.domain <-should show
qta-lr-china-hssp-ups1 qta-china-hssp-ups1.domain <-should show
sha-cab67-ups1.domain sha-cab67-ups1.domain <-should NOT how, but does
sha-cab78-ups1.domain sha-cab78-ups1.domain <-should NOT how, but does
sha-cab4009-pdu1.doamin sha-cab4009-ups1.domain <-should show

There you go

Thanks


There is nothing like insert statements, and this is nothing like insert statements. Please understand that we want to help you but we don't have tables or data to work with. Is this two columns in a table, is this a single column with multiple values?

Please take a few minutes and read the first link in my signature for best practices when posting questions. Yours is pretty simple but we can't help with coding because we can't write it ourselves.

BTW, you said you are new to sql. You should stop the habit immediately of using ordinal position in your order by. Use the column name instead. The benefits are two fold. First you don't have to start counting columns to see which column it is (does it start with 0 or 1? that is a question you need to know 100%). Secondly, you don't have to change your order by if you change the order of columns in the query.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1539377
Posted Friday, February 7, 2014 1:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 12,902, Visits: 32,141
since what he pasted was almost, but not quite, tab delmited, i rant it through my macro to cleanup;
MAYBE the data is two columns, but here's what i think he meant to post:
;WITH MyCTE([col1],[col2],[note])
AS
(
SELECT 'qta-subbld4-swt1-ups1','qta-sub-bldg4-swt1-ups1.domain','<-should show' UNION ALL
SELECT 'qta-lr-china-hssp-ups1','qta-china-hssp-ups1.domain','<-should show' UNION ALL
SELECT 'sha-cab67-ups1.domain','sha-cab67-ups1.domain','<-should NOT how, but does' UNION ALL
SELECT 'sha-cab78-ups1.domain','sha-cab78-ups1.domain','<-should NOT how, but does' UNION ALL
SELECT 'sha-cab4009-pdu1.doamin','sha-cab4009-ups1.domain','<-should show'
)
SELECT * FROM MyCTE;



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1539379
Posted Friday, February 7, 2014 1:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 3,630, Visits: 8,138
I'm giving you an answer, but I need you to understand some things first.
The solution given is not an optimal solution because it's non-SARGable. It means that it won't use any indexes available.
To get better and faster answers, you should follow some guidelines on how to post sample data. These guidelines are explained on the article linked in my signature. I'm posting it for you as an example. It's not mentioned in the article, but it would be nice as well to use the IFCode tags [ code="sql"][/code] to encapsulate the code and keep formatting.
Unless you're obfuscating something else, you don't need to use a subquery to filter the results and then filter again. you can use the table and use al filters in the same WHERE clause.
It's considered a best practice to use column names or aliases in the ORDER BY clause. As you'll see on my example, I used just 2 columns and the references were lost. That could happen anytime and you want to prevent it.
Finally, here's the solution. I hope that you take the comments as a way to grow and not as bad criticism.
--Define the table structure so we can know the table types. Add indexes if available.
CREATE TABLE #Nodes(
Sys_name varchar(50),
DNS varchar(50))

--Sample data ready to be used by executing a simple query
INSERT INTO #Nodes
VALUES
('qta-subbld4-swt1-ups1','qta-sub-bldg4-swt1-ups1.domain'), --should show
('qta-lr-china-hssp-ups1','qta-china-hssp-ups1.domain'), --should show
('sha-cab67-ups1.domain','sha-cab67-ups1.domain'), --should NOT how, but does
('sha-cab78-ups1.domain','sha-cab78-ups1.domain'), --should NOT how, but does
('sha-cab4009-pdu1.doamin','sha-cab4009-ups1.domain ') --should show

--This is the solution
SELECT N.Sys_name,
N.DNS
FROM #Nodes N
WHERE LEFT( Sys_name, CHARINDEX('.', Sys_name + '.'))
<> LEFT( DNS, CHARINDEX('.', DNS + '.'))

--This is to clean up
DROP TABLE #Nodes




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539382
Posted Friday, February 7, 2014 1:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 13,002, Visits: 12,419
Taking Lowell's attempt at converting this ddl (excellent job, I would love to see your macro for this) you could do something like this.

;WITH MyCTE([col1],[col2],[note])
AS
(
SELECT 'qta-subbld4-swt1-ups1','qta-sub-bldg4-swt1-ups1.domain','<-should show' UNION ALL
SELECT 'qta-lr-china-hssp-ups1','qta-china-hssp-ups1.domain','<-should show' UNION ALL
SELECT 'sha-cab67-ups1.domain','sha-cab67-ups1.domain','<-should NOT how, but does' UNION ALL
SELECT 'sha-cab78-ups1.domain','sha-cab78-ups1.domain','<-should NOT how, but does' UNION ALL
SELECT 'sha-cab4009-pdu1.doamin','sha-cab4009-ups1.domain','<-should show'
)
, NumberedCTE as
(
SELECT *, ROW_NUMBER() over(order by (select null)) as RowNum FROM MyCTE
)

select *
from NumberedCTE c1
join NumberedCTE c2 on c1.RowNum = c2.RowNum
where c1.col1 <> c2.col2



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1539385
Posted Friday, February 7, 2014 8:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 6:42 AM
Points: 3, Visits: 4
I posted my whole sql query sorry if I didn't post the correct thing

the data I posted is the output I get when I run the query

I will try the where left statement

thank you
Post #1539460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse