SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Comparing 2 Columns until the 1st "."


Comparing 2 Columns until the 1st "."

Author
Message
spyfly
spyfly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11
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
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16284 Visits: 19059
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
spyfly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25869 Visits: 17509
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 Modens 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)
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27986 Visits: 39922
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

--
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
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16284 Visits: 19059
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.
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
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25869 Visits: 17509
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 Modens 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)
spyfly
spyfly
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 11
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search