CHARINDEX and Comparing Data from 2 different DB's

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

SQL Server 2005 Forums

Transact-SQL (2005)

CHARINDEX and Comparing Data from 2 different DB's

Author

Topic

winbmparchitect
Starting Member

2 Posts

Posted - 2009-05-20 : 11:42:52

Hello SQL Server Forums,

This is my first post so be gentle

I'm working on writing a SQL Report that compares data in two different databases and tells me the differences between them. The problem I'm running in to is with some of the data. Here's the basic code behind the report:

SELECT inventory.Computer
FROM servergroup INNER JOIN
inventory ON servergroup.Servergroup_Idx = inventory.Servergroup_Idx
WHERE (inventory.Good = 1) AND (servergroup.Servergroup > '%')
ORDER BY inventory.Computer

Now the data this spits out contains something like this:

123456-SERVER1
123457-SERVER2
123458-SERVER3
WEBSERVER1
WEBSERVER2
WEBSERVER3
SERVER-APP-WEB2
V123456

From the output you can see that some contain the dash, while some contain text and numbers.

When I add the CHARINDEX to the code, it spits out the values for the dash as either 7, 6, 2, or 0 depending on what it finds.

Now my question to all of you is, how to I write the query so that it spits out only numbers (123456, 123457, 123458), and still account for the other items in the row?

I already know that I need to do a left(7-1) to account for some of the CHARINDEX values, but can I also include the left(6-1) in the same statement? Some of my computer numbers are 6 digit, while others are 7 digit. Then the ones with the letter in front are 5 digit.

Of the 6k+ records it returns, I would say that 300 or so of them contain those V123456 or WEBSERVER1 names. I still need to account for them, but I simply can't figure out the syntax to make this happen.

I'm sure this was confusing to read so feel free to pick away at it and let me know anything I need to be more clear on.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-20 : 12:20:29

Can you post for the above data exactly what you are looking to return.

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

winbmparchitect
Starting Member

2 Posts

Posted - 2009-05-20 : 13:42:17

quote:
Originally posted by Vinnie881

Can you post for the above data exactly what you are looking to return.

Yes,

The data from my first database contains nothing but numbers and looks like this:

123456
123457
123458
123459

The other database contains data which looks like this:

123456-SERVER1
123457-SERVER2
123458-SERVER3
WEBSERVER1
WEBSERVER2
WEBSERVER3
R12345

What I'm trying to do is figure out how to take the data in the second database, remove anything to the right of the dash, and then also account for those computers that are odd like the WEBSERVER1 or the R12345.

Then I'm going to take DB1 and DB2 and compare the numbers for matches and discard them. Anything that doesn't match I'm then going to use for the report to show what's different from each DB.

I simply can't figure out how to do that in one single query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:53:32

seems like this


SELECT t2.*
FROM DB2..table t2
LEFT JOIN DB1..table t1
ON t2.field LIKE t1.field + '-%'
WHERE t1.field IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources