Comparing 2 Columns until the 1st "."

Question

Post reply

Comparing 2 Columns until the 1st "."

spyfly

Mr or Mrs. 500

Points: 593
More actions
February 7, 2014 at 11:24 am

#289332

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

spyfly Mr or Mrs. 500 Points: 593 More actions · Answer 2

qta-subbld4-swt1-ups1qta-sub-bldg4-swt1-ups1.domain<-should show

qta-lr-china-hssp-ups1qta-china-hssp-ups1.domain<-should show

sha-cab67-ups1.domainsha-cab67-ups1.domain<-should NOT how, but does

sha-cab78-ups1.domainsha-cab78-ups1.domain<-should NOT how, but does

sha-cab4009-pdu1.doaminsha-cab4009-ups1.domain<-should show

There you go

Thanks

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

rtrice (2/7/2014)
qta-subbld4-swt1-ups1qta-sub-bldg4-swt1-ups1.domain<-should show
qta-lr-china-hssp-ups1qta-china-hssp-ups1.domain<-should show
sha-cab67-ups1.domainsha-cab67-ups1.domain<-should NOT how, but does
sha-cab78-ups1.domainsha-cab78-ups1.domain<-should NOT how, but does
sha-cab4009-pdu1.doaminsha-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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323504 More actions · Answer 4

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],)

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

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_namevarchar(50),

DNSvarchar(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.
General Disclaimer:
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?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

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],)

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

spyfly Mr or Mrs. 500 Points: 593 More actions · Answer 7

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