Comparing Tables and Columns Between Two Databases

Hi,

I have a two databases that may be similar but are not identical.I would like to compare the table and column names of the two databases to find out where they differ.Are there any tools that would help me in this task?In Oracle I would compare the meta data of the two databases in a select clause.Can we do the same thing in MS SQL?

Thanks in advance,

Dave

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054 should get you what you are looking.

Comparing Smalldatetime column

I have two Smalldatetime columns. One of the columns have
NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/e..._qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...

quote:

---

> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.

---

|||> b) you could say SET ANSI_NULLS OFF
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.

quote:

---

>--Original Message--
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -

---

it's still UNKNOWN.

quote:

---

>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

---

|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.

quote:

---

>--Original Message--
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -

---

it's still UNKNOWN.

quote:

---

>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

---

|||SORRY FOR THE DOUBLE POST...

quote:

---

>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%

---

1900%').

quote:

---

>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>
>it's still UNKNOWN.
>.
>

---

|||It helps if you can post some code to reproduce your problem and show your
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.

quote:

---

>--Original Message--
>It helps if you can post some code to reproduce your

---

problem and show your

quote:

---

>required results. It's not clear to me exactly what

---

result you're trying to

quote:

---

>get in this case.
>When combining AND and OR you have to watch out for the

---

order of precedence.

quote:

---

>Use parentheses to make the order of evaluation clear

---

otherwise x OR y AND z

quote:

---

>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string

---

comparison and if

quote:

---

>you use it on dates then every date has to be cast as a

---

string. Best way to

quote:

---

>eliminate the year 1900 is to test for a date >= 1901-01-

---

01.

quote:

---

>Here's some code to reproduce a result that *may* be what

---

you want:

quote:

---

>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1

---

SMALLDATETIME,

quote:

---

>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE

---

(column2,'19000101')

quote:

---

d">
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have

---

to specify which

quote:

---

>rows you want to be included in your result. I should

---

have covered all the

quote:

---

>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

---

Comparing Smalldatetime column

I have two Smalldatetime columns. One of the columns have
NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
--
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...
> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.|||> b) you could say SET ANSI_NULLS OFF
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
--
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||SORRY FOR THE DOUBLE POST...
>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%
1900%').
>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
>it's still UNKNOWN.
>>--
>>David Portas
>>--
>>Please reply only to the newsgroup
>>--
>>
>>.
>.
>|||It helps if you can post some code to reproduce your problem and show your
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.
>--Original Message--
>It helps if you can post some code to reproduce your
problem and show your
>required results. It's not clear to me exactly what
result you're trying to
>get in this case.
>When combining AND and OR you have to watch out for the
order of precedence.
>Use parentheses to make the order of evaluation clear
otherwise x OR y AND z
>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string
comparison and if
>you use it on dates then every date has to be cast as a
string. Best way to
>eliminate the year 1900 is to test for a date >= 1901-01-
01.
>Here's some code to reproduce a result that *may* be what
you want:
>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1
SMALLDATETIME,
>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE
(column2,'19000101')
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have
to specify which
>rows you want to be included in your result. I should
have covered all the
>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>

Comparing Rows in a table

Hi guys,

I just wanted to know if it is possible to compare each row in the table with the previous one.

Im looking to compare each row the time column with the previous to see if there is a gap of more then 15 minutes.

I was thinking of using the datediff function to compare but not sure how i go about accessing row by row and compare with the previous one.

Any help much appreciated.

Thanks.

I would suggest using a self-join based on your datetime field. For this kind of join it is critical that you have an index built on the target datetime field. I will put together a mock-up of this.|||

I dont understand target datetime field?

Do you mean the table that ill be out putting the results to?

Thanks for your quick response and putting together the mock-up of this.

|||

Here is a mockup. I sprayed 32767 records out over 43 days going backwards from 3/20/7 using an iterator table and a scalar RAND udf. These two items can be found on this page:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1330536&SiteID=1

Here is the mockup. I have not yet evaluated whether or not this is efficient. Stand by; another post will follow..

declare @.mockup table
( rowid integer,
aDateTime datetime,
unique (aDateTime, rowId)
)

insert into @.mockup
select iter,
cast ('3/20/7' as datetime) - 43 * dbo.rand() as aDateTime
from small_iterator
--select * from @.mockup

select rowId,
aDateTime,
nextDateTime,
convert (varchar(12), nextDateTime - aDateTime, 108)
as [Time Difference]
from ( select rowId,
aDatetime,
( select top 1 b.aDateTime
from @.mockup b
where b.aDateTime > a.aDateTime
order by aDateTime, rowId
) as nextDatetime
from @.mockup a
) x
where nextDateTime - aDateTime >= cast(cast ('0:15:00' as datetime) as float)

-- rowId aDateTime nextDateTime
-- - -- --
-- 23623 2007-02-11 09:51:57.530 2007-02-11 10:07:25.077 00:15:27
-- 12068 2007-02-12 11:46:03.190 2007-02-12 12:01:25.987 00:15:22
-- 14377 2007-02-14 03:17:02.890 2007-02-14 03:32:39.907 00:15:37
-- ...

-- 30564 2007-03-17 04:32:18.287 2007-03-17 04:48:51.520 00:16:33
-- 9053 2007-03-18 21:07:15.663 2007-03-18 21:22:21.220 00:15:05

Oh yeah: I didn't perform a self join; this looked better. Sorry, I am in the middle of a restore.

OK! This puppy doesn't look too bad: With this index I am getting a plan of index scan / index seek. To process the entire 32767 records, the IO is:

-- Table '#401BB779'. Scan count 32768, logical reads 65746, physical reads 0, read-ahead reads 0.

That's pretty good. You might be able to find a better way to do this, but this is at least pretty good.

REMEMBER!

It is CRITICAL that the date/time column has an index! Otherwise you are NOT going to be able to search by index seek.

|||

Just to clarify - how do you define your previous row? Is your definition based on the primary key, or the datetime column?

i.e.

ID Date

1 05-Jun-2007

2 06-Mar-2007

3 02-Dec-2006

In the above example - if the current row is row 2, are you looking to compare row 2's date with that of row 1 or row 3?

Chris

|||

In our GPS Table we have an ID column for each entry.

What we need to do is compare current row time by next row time.

For example

in you example above we would compare Row 1 with Row 2 if there is a gap of 15 mins in time we record that.

Else we move to row 2 and compare it with row 3 and see if there is a gap of 15 minutes.

We need to do this for our entire database table and record each time there is gap of 15 mins or greater between 2 entries in the table.

Our goal is to split the table into a new trips table.

|||

Does your table have an IDENTITY column? If so, are there any gaps between the IDENTITY values?

Thanks
Chris

|||

Thanks for your reply.

The date time column is only referenced by the Auto ID that is created when the data is inputted into our GPS Table.

We need to compare row 1 and with row 2 and check if there is a 15 minute gap or greater in time.

If there is we want to record this in our Trip table.

We would like to do this for the entire table and record each time there is a gap of 15 minutes between the current row and the next row.

|||

Yes our table has an IDENTITY column. This ID is created everytime data is inputted into our GPS table.

This column will never be blank as the number is generated when an entry is made to the table.

|||Hey all!
Thanks for your Class I help so far.
I'm involved in this project too.. The data comes in something like this:

ID VehicleID Date/Time
333 212 2006-1-12 10:45:33
334 212 2006-1-12 10:45:48
335 212 2006-1-12 10:46:32
336 212 2006-1-12 10:47:43
337 212 2006-1-12 10:47:52
<< We want to identify and generate a new trip ID here in trip table (>15mins interval) >>
338 212 2006-1-12 17:23:33
339 212 2006-1-12 17:23:58
340 212 2006-1-12 17:24:33|||

Previous post removed as I've found a solution that can cope with gaps between values in the IDENTITY column. See below.

Chris

--Setup test data
DECLARE @.MyTable TABLE (ID INT NOT NULL, VehicleID INT, [Date/Time] DATETIME NOT NULL)

INSERT INTO @.MyTable(ID, VehicleID, [Date/Time])
SELECT 333, 212, '2006-1-12 10:45:33' UNION
SELECT 334, 212, '2006-1-12 10:45:48' UNION
SELECT 335, 212, '2006-1-12 10:46:32' UNION
SELECT 336, 212, '2006-1-12 10:47:43' UNION
SELECT 337, 212, '2006-1-12 10:47:52' UNION
--<< We want to identify and generate a new trip ID here in trip table (>15mins interval) >>
SELECT 338, 212, '2006-1-12 17:23:33' UNION
SELECT 339, 212, '2006-1-12 17:23:58' UNION
SELECT 340, 212, '2006-1-12 17:24:33' UNION
--Repeat data for a different VehicleID
SELECT 341, 100, '2006-1-12 10:45:33' UNION
SELECT 342, 100, '2006-1-12 10:45:48' UNION
SELECT 343, 100, '2006-1-12 10:46:32' UNION
SELECT 344, 100, '2006-1-12 10:47:43' UNION
SELECT 345, 100, '2006-1-12 10:47:52' UNION
--<< We want to identify and generate a new trip ID here in trip table (>15mins interval) >>
SELECT 346, 100, '2006-1-12 17:23:33' UNION
SELECT 347, 100, '2006-1-12 17:23:58' UNION
SELECT 348, 100, '2006-1-12 17:24:33'

SELECT *
FROM @.MyTable

--Return the rows between which there is a datetime gap > 15 mins
--All columns are returned purely for this example - take out any columns you don't need and add in any that you do
SELECT t1.ID, t1.[Date/Time],
t2.ID,
t2.[Date/Time],
DATEDIFF(mi, t1.[Date/Time], t2.[Date/Time]) AS [Difference (mins)],
t1.VehicleID
FROM @.MyTable t1, @.MyTable t2
WHERE t2.[Date/Time] > DATEADD(mi, 15, t1.[Date/Time])
AND t2.ID = (SELECT TOP 1 t3.ID FROM @.MyTable t3 WHERE t3.ID > t1.ID AND t3.VehicleID = t1.VehicleID ORDER BY t3.ID ASC)
AND t2.VehicleID = t1.VehicleID

--Delete a couple of rows to show we can cope with range gaps
DELETE FROM @.MyTable
WHERE id IN (338, 345)

--Repeat the query just to show we can cope with range gaps, expect different results as rows have been deleted from the source table
SELECT t1.ID, t1.[Date/Time],
t2.ID,
t2.[Date/Time],
DATEDIFF(mi, t1.[Date/Time], t2.[Date/Time]) AS [Difference (mins)],
t1.VehicleID
FROM @.MyTable t1, @.MyTable t2
WHERE t2.[Date/Time] > DATEADD(mi, 15, t1.[Date/Time])
AND t2.ID = (SELECT TOP 1 t3.ID FROM @.MyTable t3 WHERE t3.ID > t1.ID AND t3.VehicleID = t1.VehicleID ORDER BY t3.ID ASC)
AND t2.VehicleID = t1.VehicleID

|||

Hi Chris,

Thanks for your response.

I cant test it out until tomorrow as i dont have access to the database table from home.

I will try this out tomorrow and keep you informed.

Thanks a mil.

|||

Emerson, Carrics3,

How about (sorry for the double spacing, which I don't know how to control):

create table new_trips

(start_ID int not null,

vehicle_ID int,

start_time datetime

)

;

insert into new_trips

select

t2.ID,

t2.Vehicle_ID,

t2.[Date/Time]

from data_table t1

inner join data_table t2 -- you are joining a table to itself

on t2.ID = (t1.ID + 1) and

t2.vehicle_ID = t1.vehicle_ID

where DATEDIFF(mi, t1.[Date/Time], t2.[Date/Time])

order by 1, 2

;

Dan

P.S. This does not properly deal with having multiple Vehicle_ID in the same table. I will have to think a bit about how best to deal with that. It may involve placing ID and Vehicle_ID in a sorted fashion into a temporary table, and having an additional column (an IDENTITY column) in the temporary table that would be used the same way ID is being used in the above query. When Vehicle_ID changed it should also indicate a new trip -- I am guessing, anyway.

|||

In response to DanR1's post, just be aware that joining onto an identity value + 1 (and incorporating the VehicleID) will fail where either the VehicleID changes between adjacent rows or where there are gaps between identity values (where rows have been deleted or transactions containing inserts not committed).

If your data looks like either of the two examples below then this method will fail.

Chris

ID VehicleID Date/Time
333 212 2006-1-12 10:45:33
334 212 2006-1-12 10:45:48
335 212 2006-1-12 10:46:32
336 212 2006-1-12 10:47:43
337 212 2006-1-12 10:47:52
<< We want to identify and generate a new trip ID here in trip table (>15mins interval) >>
338 213 2006-1-12 17:23:33
339 212 2006-1-12 17:23:35
340 212 2006-1-12 17:23:58
341 212 2006-1-12 17:24:33

or

ID VehicleID Date/Time
333 212 2006-1-12 10:45:33
334 212 2006-1-12 10:45:48
335 212 2006-1-12 10:46:32
336 212 2006-1-12 10:47:43
337 212 2006-1-12 10:47:52
<< We want to identify and generate a new trip ID here in trip table (>15mins interval) >>
339 212 2006-1-12 17:23:33
340 212 2006-1-12 17:23:35
341 212 2006-1-12 17:23:58
342 212 2006-1-12 17:24:33

|||

Hi Chris,

I have tested out your sql statement.

It is working but its not exactly what we want to do.

We need to compare row 1 with row 2 and check if there is a gap of 15 mins

If there is we want to record that ( Instead of Checking row 1 against every record in the table)

However if there is a gap of less then 15 mins then we want to move on and query row 2 with row 3 and check if there is gap of 15 mins. And so on til we get to the end where there is no row coming after the current row.

Each time there is gap of 15 mins or greater between the current row and the next row we want to record it in our Trip table

ID VehicleID Date/Time
333 353864504523 2006-1-12 10:35:33
334 353864504523 2006-1-12 10:35:48
335 353864504523 2006-1-12 10:36:32
336 353864504523 2006-1-12 10:37:43
337 353864504523 2006-1-12 10:38:52
338 353864504523 2006-1-12 10:39:33
339 353864504523 2006-1-12 10:40:48 There is a gap of 15 mins between these 2 entries.
340 353864504523 2006-1-12 10:55:32
341 353864504523 2006-1-12 10:55:43
342 353864504523 2006-1-12 10:56:52

comparing nvarchar(max) column using like to non-ASCII range

Our database defines the long_value column as nvarchar(max). I want to find out which rows actually contain non-ASCII characters in that column, but this clause also returns rows with only ASCII characters:

where long_value like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%')

What am I doing wrong?

It seems to be the fact that nchar(65535) has no length. I couldn't find any really good reference to cover this (perhaps this thread: http://groups.google.com/group/comp.lang.javascript/tree/browse_frm/month/2004-09/7d3603a75c1550f3?rnum=91&_done=%2Fgroup%2Fcomp.lang.javascript%2Fbrowse_frm%2Fmonth%2F2004-09%3F), but if you run these statements:

select N'%[' + nchar(128) + N'-' + nchar(65535) + N']%'

select N'%[' + nchar(128) + N'-' + nchar(65534) + N']%'

You will see that there is no second character after the - in the second string. This should do what you want:

declare @.long_value nvarchar(max)
set @.long_value = N'abcdefghijklmnopqrstuvwxyz' + nchar(129)
select case when @.long_value like (N'%[' + nchar(128) + N'-' + nchar(65534) + N']%') then 'yes' else 'no' end

and you could add a check for the nchar(65535) also...

|||

Louis Davidson wrote:

It seems to be the fact that nchar(65535) has no length. I couldn't find any really good reference to cover this (perhaps this thread: http://groups.google.com/group/comp.lang.javascript/tree/browse_frm/month/2004-09/7d3603a75c1550f3?rnum=91&_done=%2Fgroup%2Fcomp.lang.javascript%2Fbrowse_frm%2Fmonth%2F2004-09%3F), but if you run these statements:

select N'%[' + nchar(128) + N'-' + nchar(65535) + N']%'

select N'%[' + nchar(128) + N'-' + nchar(65534) + N']%'

You will see that there is no second character after the - in the second string.

I can't corroborate your results. I'm runing SQL Server 2005, both an unpatched Enterprise Edition and the SP1 + build 2153 hotfix Developer Edition. Connected to either server's master database, the result of both queries is displayed in Management Studio with an empty box on either side of the dash. Furthermore, the results of the following queries are 1, 2, 3, and 7 as expected:

select len(nchar(65535));

select len(nchar(128) + nchar(65535));

select len(nchar(128) + N'-' + nchar(65535));

select len(N'%[' + nchar(128) + N'-' + nchar(65535) + N']%');

Louis Davidson wrote:

This should do what you want:

declare @.long_value nvarchar(max)
set @.long_value = N'abcdefghijklmnopqrstuvwxyz' + nchar(129)
select case when @.long_value like (N'%[' + nchar(128) + N'-' + nchar(65534) + N']%') then 'yes' else 'no' end

and you could add a check for the nchar(65535) also...

Well, the obvious way to add a check for nchar(65535) is to include it in the range:

N'%[' + nchar(128) + N'-' + nchar(65534) + nchar(65535) + N']%'

Which, if it worked, would indicate a bug in character range matching. But it fails in the same way as the original.

Here is a diagnostic query that suggests to me that the problem has something to do with collations (a topic I had been blissfully ignorant of). If the server's default collation or the database's collation is Latin1_General_BIN2, it returns the expected result; but if the collation is SQL_Latin1_General_CP1_CI_AS, it returns 21,230 Unicode characters between 0 and 65535 inclusive which are equal to nchar(65535). Most disturbing is the fact that 0 (NULL) and 32 (SPACE) are among them, which might explain my original problem with the character range. Try this out on different servers and databases:

declare @.code as integer

set @.code = 0

while (@.code < 65536)

begin

if (nchar(@.code) = nchar(65535))

print cast(@.code as nchar) + ' equal'

set @.code = @.code + 1

end

|||

Kevin Rodgers wrote:

Here is a diagnostic query that suggests to me that the problem has something to do with collations (a topic I had been blissfully ignorant of). If the server's default collation or the database's collation is Latin1_General_BIN2, it returns the expected result; but if the collation is SQL_Latin1_General_CP1_CI_AS, it returns 21,230 Unicode characters between 0 and 65535 inclusive which are equal to nchar(65535). Most disturbing is the fact that 0 (NULL) and 32 (SPACE) are among them, which might explain my original problem with the character range. Try this out on different servers and databases:

declare @.code as integer

set @.code = 0

while (@.code < 65536)

begin

if (nchar(@.code) = nchar(65535))

print cast(@.code as nchar) + ' equal'

set @.code = @.code + 1

end

Collations are definitely the issue, since the orginal query works as intended (returning all long_value columns with non-ASCII characters) when reformulated with an explicit collation: select * from Item_Detail

where (long_value collate Latin1_General_BIN2) like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%')

|||

Kevin Rodgers wrote:

Kevin Rodgers wrote:

Here is a diagnostic query that suggests to me that the problem has something to do with collations (a topic I had been blissfully ignorant of). If the server's default collation or the database's collation is Latin1_General_BIN2, it returns the expected result; but if the collation is SQL_Latin1_General_CP1_CI_AS, it returns 21,230 Unicode characters between 0 and 65535 inclusive which are equal to nchar(65535). Most disturbing is the fact that 0 (NULL) and 32 (SPACE) are among them, which might explain my original problem with the character range. Try this out on different servers and databases:

declare @.code as integer

set @.code = 0

while (@.code < 65536)

begin

if (nchar(@.code) = nchar(65535))

print cast(@.code as nchar) + ' equal'

set @.code = @.code + 1

end

Collations are definitely the issue, since the orginal query works as intended (returning all long_value columns with non-ASCII characters) when reformulated with an explicit collation: select * from Item_Detail

where (long_value collate Latin1_General_BIN2) like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%')

Unfortunately, the Latin1_General_CI_AS collation does not work like the Latin1_General_BIN2 collation. Can someone explain that?

comparing nvarchar(max) column using like to non-ASCII range

Our database defines the long_value column as nvarchar(max). I want to find out which rows actually contain non-ASCII characters in that column, but this clause also returns rows with only ASCII characters:

where long_value like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%')

What am I doing wrong?

It seems to be the fact that nchar(65535) has no length. I couldn't find any really good reference to cover this (perhaps this thread: http://groups.google.com/group/comp.lang.javascript/tree/browse_frm/month/2004-09/7d3603a75c1550f3?rnum=91&_done=%2Fgroup%2Fcomp.lang.javascript%2Fbrowse_frm%2Fmonth%2F2004-09%3F), but if you run these statements:

select N'%[' + nchar(128) + N'-' + nchar(65535) + N']%'

select N'%[' + nchar(128) + N'-' + nchar(65534) + N']%'

You will see that there is no second character after the - in the second string. This should do what you want:

declare @.long_value nvarchar(max)
set @.long_value = N'abcdefghijklmnopqrstuvwxyz' + nchar(129)
select case when @.long_value like (N'%[' + nchar(128) + N'-' + nchar(65534) + N']%') then 'yes' else 'no' end

and you could add a check for the nchar(65535) also...

|||

Louis Davidson wrote:

It seems to be the fact that nchar(65535) has no length. I couldn't find any really good reference to cover this (perhaps this thread: http://groups.google.com/group/comp.lang.javascript/tree/browse_frm/month/2004-09/7d3603a75c1550f3?rnum=91&_done=%2Fgroup%2Fcomp.lang.javascript%2Fbrowse_frm%2Fmonth%2F2004-09%3F), but if you run these statements:

select N'%[' + nchar(128) + N'-' + nchar(65535) + N']%'

select N'%[' + nchar(128) + N'-' + nchar(65534) + N']%'

You will see that there is no second character after the - in the second string.

I can't corroborate your results. I'm runing SQL Server 2005, both an unpatched Enterprise Edition and the SP1 + build 2153 hotfix Developer Edition. Connected to either server's master database, the result of both queries is displayed in Management Studio with an empty box on either side of the dash. Furthermore, the results of the following queries are 1, 2, 3, and 7 as expected:

selectlen(nchar(65535));

selectlen(nchar(128)+nchar(65535));

selectlen(nchar(128)+ N'-'+nchar(65535));

selectlen(N'%['+nchar(128)+ N'-'+nchar(65535)+ N']%');

Louis Davidson wrote:

This should do what you want:

declare @.long_value nvarchar(max)
set @.long_value = N'abcdefghijklmnopqrstuvwxyz' + nchar(129)
select case when @.long_value like (N'%[' + nchar(128) + N'-' + nchar(65534) + N']%') then 'yes' else 'no' end

and you could add a check for the nchar(65535) also...

Well, the obvious way to add a check for nchar(65535) is to include it in the range:

N'%['+nchar(128)+ N'-'+nchar(65534)+nchar(65535)+ N']%'

Which, if it worked, would indicate a bug in character range matching. But it fails in the same way as the original.

Here is a diagnostic query that suggests to me that the problem has something to do with collations (a topic I had been blissfully ignorant of). If the server's default collation or the database's collation is Latin1_General_BIN2, it returns the expected result; but if the collation is SQL_Latin1_General_CP1_CI_AS, it returns 21,230 Unicode characters between 0 and 65535 inclusive which are equal to nchar(65535). Most disturbing is the fact that 0 (NULL) and 32 (SPACE) are among them, which might explain my original problem with the character range. Try this out on different servers and databases:

declare @.code as integer

set @.code = 0

while(@.code < 65536)

begin

if(nchar(@.code)=nchar(65535))

printcast(@.code asnchar)+' equal'

set @.code = @.code + 1

end

|||

Kevin Rodgers wrote:

Here is a diagnostic query that suggests to me that the problem has something to do with collations (a topic I had been blissfully ignorant of). If the server's default collation or the database's collation is Latin1_General_BIN2, it returns the expected result; but if the collation is SQL_Latin1_General_CP1_CI_AS, it returns 21,230 Unicode characters between 0 and 65535 inclusive which are equal to nchar(65535). Most disturbing is the fact that 0 (NULL) and 32 (SPACE) are among them, which might explain my original problem with the character range. Try this out on different servers and databases:

declare @.code as integer set @.code = 0 while (@.code < 65536) begin if (nchar(@.code) = nchar(65535)) print cast(@.code as nchar) + ' equal' set @.code = @.code + 1 end

Collations are definitely the issue, since the orginal query works as intended (returning all long_value columns with non-ASCII characters) when reformulated with an explicit collation: select * from Item_Detail where (long_value collate Latin1_General_BIN2) like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%')|||

Kevin Rodgers wrote:

Kevin Rodgers wrote:

Here is a diagnostic query that suggests to me that the problem has something to do with collations (a topic I had been blissfully ignorant of). If the server's default collation or the database's collation is Latin1_General_BIN2, it returns the expected result; but if the collation is SQL_Latin1_General_CP1_CI_AS, it returns 21,230 Unicode characters between 0 and 65535 inclusive which are equal to nchar(65535). Most disturbing is the fact that 0 (NULL) and 32 (SPACE) are among them, which might explain my original problem with the character range. Try this out on different servers and databases:

declare @.code as integer set @.code = 0 while (@.code < 65536) begin if (nchar(@.code) = nchar(65535)) print cast(@.code as nchar) + ' equal' set @.code = @.code + 1 end

Collations are definitely the issue, since the orginal query works as intended (returning all long_value columns with non-ASCII characters) when reformulated with an explicit collation: select * from Item_Detail where (long_value collate Latin1_General_BIN2) like (N'%[' + nchar(128) + N'-' + nchar(65535) + N']%')

Unfortunately, the Latin1_General_CI_AS collation does not work like the Latin1_General_BIN2 collation. Can someone explain that?

comparing encrypted strings

How do you compare an encripted value to a string?

I have a table called test table which has a column called password. The fields in that column were encrypted using the pwdencrypt() function. I need to be able to compare those encrypted fields to regular nvarchar strings. Right now I am using the pwdcompare() function to compare the values but I'm not getting the desired results.

This is what I am doing

select *
from testtable
where pwdcompare(pwdencrypt('pass_tempx'),testtable.pass word) = 1Check this one:

create table users(
id int identity,
username nvarchar(128) not null unique,
userpassword nvarchar(128) not null
)

insert users(username,userpassword)
select 'tom',pwdencrypt('tom2')

insert users(username,userpassword)
select 'brett',pwdencrypt('brett2')

select Id from users
where pwdcompare('tom2',userpassword)=1
and username='tom'

Id
----
1

(1 row(s) affected)

select Id from users
where pwdcompare('brett3',userpassword)=1
and username='brett'
Id
----|||Thanks for the feedback but it still isn't working for me. I've got my table set up just like yours but my username and userpassword fields are set up as nvarchar(15). Does this make a difference? Is there any other way to compare the 2 strings?

Originally posted by snail
Check this one:

create table users(
id int identity,
username nvarchar(128) not null unique,
userpassword nvarchar(128) not null
)

insert users(username,userpassword)
select 'tom',pwdencrypt('tom2')

insert users(username,userpassword)
select 'brett',pwdencrypt('brett2')

select Id from users
where pwdcompare('tom2',userpassword)=1
and username='tom'

Id
----
1

(1 row(s) affected)

select Id from users
where pwdcompare('brett3',userpassword)=1
and username='brett'
Id
----|||Originally posted by grualo1
Thanks for the feedback but it still isn't working for me. I've got my table set up just like yours but my username and userpassword fields are set up as nvarchar(15). Does this make a difference? Is there any other way to compare the 2 strings?

MS is using nvarchar(128) for keeping a passwors and I guess functions pwdcompare and pwdencrypt are working only for this length of string.|||Changing the length to 128 did the trick thank you so much!

Gary

Originally posted by snail
MS is using nvarchar(128) for keeping a passwors and I guess functions pwdcompare and pwdencrypt are working only for this length of string.

Comparing differences in database structure between databases

I am trying to find a way to easily compare the difference in table/column structure between two SQL server databases.

I am doing this since I need to document the foreign key relationships between a database schema that is currently under development. The foreign keys are not defined as constraints in the database, but are controlled through the application.

The current naming conventions make it easy to see what the relationships are (primary keys are "tablename_seq" and foreign keys use the same names, only tables that have foreign keys that reference themselves break this rule, with a suffix added to the primary key name like "tablename_seq_parent").

In order to document what these relationships are, I have created a copy of the database and set up the foreign key restraints so that I can use the database digram tools in SQL Server Management Studio, or Visio. It took quite a bit of manual work to create all these relationships.

Now the developers have added new tables, or made changes to tables and I need to keep the document up to date. Manually keeping track of all the changes will probably be an issue so I am looking for either:

a way of automatically generating an update script for my database when comparing to the development database, so that I can update to the latest version (then manually create the new constraints OR a way of automatically reading in information on the tables from a database and creating foreign key relationships for any primary key that is a column of another table (ie. has the same name)

The closest thing I have found that might help solve the first option is the tablediff utility. I thought perhaps I could write a script (it has been a while!!) that:

Lists all tables in the developer database For each table check if it exists in my database If it does exist then use tablediff to check for new or changed columns and generate a script to change the table using -f. If it doesn't exist then create table using script If tables exist in the destination database but not in the main one then flag them for followup manually.

Does anyone know of a simpler way that I have missed?

Regards

Jo

Hi all

Does anyone have any ideas on this? Or have I posted to the wrong forum?

Regards

Jo

|||

Hi,

Easiest thing to do is to get a third-party tool for this. I personally use the SQL Tools from Red-Gate.

For what you want , its SQL Compare. Its 295 USD, and a total bargain. There are others, from ApexSQL, and DB Ghost...

However, I use all Red-Gate tools regularly and wouldn't be without them and for me, I plumped for the SQL Bundle Pro (990 USD).

Cheers.

Paul

|||

I use a product called AdeptSQL Diff, it's pretty quick to scan my databases (6500 procs and 1100 tables in about a minute) and easy to use. www.adeptsql.com

The comparison tool is 240 USD and if you want to compare your data as well, it's 320 USD.

Jarret

|||If nothing else, version your DB objects as scripts within VSS, and do a compare between script versions.

Comparing DB's columns and create script

Hi,
I am looking for a solution that can generate a script on column difference
between two databases and its tables.
Basically, I have a one core db and one development db. The development db
has been revised several time and now I need to generate a script that just
adds all new table columns with their defaults to the old core db.
Any knowledge about this or some methods to use.
Thanks in advance
ChristianCheck out SQLCompare, from Red-GAte Software... It will compare the two
databases, and generate a script to do exactly what you need...
http://www.red-gate.com
"Christian Perthen" wrote:

> Hi,
> I am looking for a solution that can generate a script on column differenc
e
> between two databases and its tables.
> Basically, I have a one core db and one development db. The development db
> has been revised several time and now I need to generate a script that jus
t
> adds all new table columns with their defaults to the old core db.
> Any knowledge about this or some methods to use.
> Thanks in advance
> Christian
>
>

Comparing dates

Hello!
I want to check if a date (not time) is found in a column with datetime.
If I have the following rows in a table:
2005-02-02 02:58:00
2005-02-02 07:22:30
2005-01-28 11:33:56
I want to find rows using a stored procedure with the @.date parameter set to
2005-02-02, but within all time during that day.
So with one criteria I want to find the to rows with 2005-02-02...
I don't want to use text, because I want the application and database to run
on different servers in different countries with different configuration for
date format.
This must be a very common task, but I can't figure out how to solve it, so
please help...
Regards MagnusDECLARE @.MyDateParameter char(8)
SET @.MyDateParameter = '20050209'
SELECT *
FROM MyTable
WHERE CONVERT(CHAR(8),MyDateColumn,112) = @.MyDateParameter
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:%23W7sRAvDFHA.1392@.tk2msftngp13.phx.gbl...
> Hello!
> I want to check if a date (not time) is found in a column with datetime.
> If I have the following rows in a table:
> 2005-02-02 02:58:00
> 2005-02-02 07:22:30
> 2005-01-28 11:33:56
> I want to find rows using a stored procedure with the @.date parameter set
> to
> 2005-02-02, but within all time during that day.
> So with one criteria I want to find the to rows with 2005-02-02...
> I don't want to use text, because I want the application and database to
> run
> on different servers in different countries with different configuration
> for
> date format.
> This must be a very common task, but I can't figure out how to solve it,
> so
> please help...
> Regards Magnus
>|||Hi,
try using datediff function eg :
--
Use Northwind
Go
DECLARE @.date as datetime
Set @.date = '04-Jul-1996 19:45:23'
SELECT @.date
SELECT * from Orders
where Datediff(day,Orders.OrderDate,@.date) =0
Let us know if it helps
siaj
"Magnus Blomberg" wrote:

> Hello!
> I want to check if a date (not time) is found in a column with datetime.
> If I have the following rows in a table:
> 2005-02-02 02:58:00
> 2005-02-02 07:22:30
> 2005-01-28 11:33:56
> I want to find rows using a stored procedure with the @.date parameter set
to
> 2005-02-02, but within all time during that day.
> So with one criteria I want to find the to rows with 2005-02-02...
> I don't want to use text, because I want the application and database to r
un
> on different servers in different countries with different configuration f
or
> date format.
> This must be a very common task, but I can't figure out how to solve it, s
o
> please help...
> Regards Magnus
>
>|||On Wed, 9 Feb 2005 22:39:16 +0100, Magnus Blomberg wrote:

>I want to find rows using a stored procedure with the @.date parameter set t
o
>2005-02-02, but within all time during that day.
Hi Magnus,
You've already gotten some suggestions that will work, but if there's an
index on the datetime column, you should use this one instead:
SELECT ...
FROM ...
WHERE MyDateCol >= @.date
AND MyDateCol < DATEADD(day, 1, @.date)
An index can only be used if the indexed column is on it's own on one side
of a comparison operator - if it's in a function or other expression, the
index can't be used to quickly locate the rows you need.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello!
Thanks all of you.
I think all of your suggestions will work, but I'll go for the DateDiff
variant.
Regards Magnus
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Comparing DATE with TIMESTAMP fields possible ?

In my database table there is a datetime column which can obviously contain
a timestamp
(in the format YYYY-MM-DD HH:MM:SS.NNN).
Now I want to get all those records which have a date of lets say 2005-02-03
regardsless of
their time value.
Ok, I could do this by statement like
SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <=
'2005-02-03 23:59.59.999'
but this is rather inconvenient. I feel that there must be a shorter version
like
SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
or a built-in fuction like:
SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
How does this work?
GeorgeHi
What you are looking for:
SELECT * FROM MYTAB WHERE CONVERT(CHAR(10), MYDATE, 120) = '2005-02-03'
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:cue1up$k5b$01$1@.news.t-online.com...
> In my database table there is a datetime column which can obviously
contain a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say
2005-02-03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE
<= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter
version like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>|||> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000'
> AND MYDATE <= '2005-02-03 23:59.59.999'
In fact this won't give the answer you want. '2005-02-03 23:59.59.999'
will be rounded up to 2005-02-04!
Instead try:
SELECT *
FROM MYTAB
WHERE mydate >= '20050203'
AND mydate < '20050204'
Note also that the date format you used is not safe under all regional
connection settings - it may cause a syntax error. The safe formats
are:
'2005-02-03T00:00:00.000'
'2005-02-03T00:00:00'
'20050203'
David Portas
SQL Server MVP
--|||On Wed, 9 Feb 2005 23:13:46 +0100, George Dainis wrote:

>In my database table there is a datetime column which can obviously contain
a timestamp
>(in the format YYYY-MM-DD HH:MM:SS.NNN).
>Now I want to get all those records which have a date of lets say 2005-02-0
3 regardsless of
>their time value.
(snip)
You've already gotten some suggestions that will work, but if there's an
index on the datetime column, you should use this one instead:
SELECT ...
FROM ...
WHERE MyDateCol >= '20050203' -- Standard date format has no dashes!
AND MyDateCol < '20050204'
An index can only be used if the indexed column is on it's own on one side
of a comparison operator - if it's in a function or other expression, the
index can't be used to quickly locate the rows you need.
Your suggested query:

>SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <= '2005-0
2-03 23:59.59.999'
will also return the rows with mydate equal to 2005-02-04 00:00:00.000, as
datetime has a precision of 3/1000th of a second - 23:59:59.999 gets
rounded up to 0:00:00.000, not down to 23:59:59.997.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||George Dainis wrote:

> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
What you are suggesting above is implicit conversion which Oracle
advises against and which is likely to lead to many problems. A
date is not a string and a timestamp is not a string. So surrounding
them with single-quotes is a bad idea in many respects.
What you need to look at is the following functions:
TO_CHAR, TO_DATE, and TO_TIMESTAMP.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||On Wed, 09 Feb 2005 15:52:39 -0800, DA Morgan wrote:
(snip)
>What you are suggesting above is implicit conversion which Oracle
>advises against and which is likely to lead to many problems. A
>date is not a string and a timestamp is not a string. So surrounding
>them with single-quotes is a bad idea in many respects.
>What you need to look at is the following functions:
>TO_CHAR, TO_DATE, and TO_TIMESTAMP.
Hi DA,
This is a SQL Server newsgroup. TO_CHAR, TO_DATE, and TO_TIMESTAMP are not
valid functions in SQL Server.
In SQL Server, implicit conversion from string to datetime is perfectly
valid, as long as unambiguous date and datetime formats are used:
yyyymmdd
yyyy-mm-ddThh:mm:ss
yyyy-mm-ddThh:mm:ss.ttt
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||George Dainis wrote:
> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
Morning George,
select * from mytab
where trunc(mydate) = to_date('2005-02-03', 'yyyy-mm-dd');
However, if mydate is indexed then the trunc() call will prevent the
index being used, so you need to use a statement similar to the one you
have mentioned above except I would use BETWEEN instead of >= and <= but
that's just personal preference.
Cheers,
Norm.
PS. Oracle stores DATE and TIMESTAMP columns in it's own internal
format, not the format you 'think' it is stored in. Always, when doing
date stuff, specify the format mask to TO_DATE because one day your code
may be running on a database which has a different default date format
and it will barf. Been there, got bitten, fixed it.|||On Thu, 10 Feb 2005 07:48:22 +0100, Norman Dunbar wrote:
(snip)
>select * from mytab
>where trunc(mydate) = to_date('2005-02-03', 'yyyy-mm-dd');
Hi Norman,
The result of this query will be:
Server: Msg 195, Level 15, State 10, Line 2
'trunc' is not a recognized function name.
Why are you posting Oracle syntax as replies to questions in a MS SQL
Server newsgroup?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 10 Feb 2005 06:13:46 +0800, George Dainis wrote
(in article <cue1up$k5b$01$1@.news.t-online.com> ):

> In my database table there is a datetime column which can obviously contain a[/col
or]
> timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03
> regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <=[/color
]
> '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on
> like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
use the between function.|||George Dainis wrote:
> In my database table there is a datetime column which can obviously contai
n a timestamp
> (in the format YYYY-MM-DD HH:MM:SS.NNN).
> Now I want to get all those records which have a date of lets say 2005-02-
03 regardsless of
> their time value.
> Ok, I could do this by statement like
> SELECT * FROM MYTAB WHERE MYDATE >= '2005-02-03 00:00:00.000' AND MYDATE <
= '2005-02-03 23:59.59.999'
> but this is rather inconvenient. I feel that there must be a shorter versi
on like
> SELECT * FROM MYTAB WHERE MYDATE = '2005-02-03 **:**:**.***'
> or a built-in fuction like:
> SELECT * FROM MYTAB WHERE datepartonly(MYDATE) = '2005-02-03'
> How does this work?
> George
>
TRUNC is your friend, by default it will truncate the date part to the
DD-MON-YY part, dropping the time area, so:
CREATE TABLE mytab (mydate timestamp);
INSERT INTO mytab VALUES (SYSDATE); <-- few entries over a few seconds.
SELECT * FROM mytab WHERE TRUNC(mydate) = '12-FEB-05';
Hope that helps,
Enoch.

Comparing date only?

This is probably a really simple one:
Given a table with a DATETIME column, how can I do an = or <> comparison
with just the date part?
ie.
SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
SOMEFUNC(@._In_theDataTime)
, where SOMEFUNC will just return the date part (ignoring the time part) of
theDateTime.
Thanks.....You don't want to apply expressions to both sides of the WHERE clause,
otherwise you will negate any indexes. How about (assuming your oddly named
variable is a DATETIME or, better yet, SMALLDATETIME):
WHERE MyTable.TheDateTIme >= (@._In_theDataTime)
AND MyTable.TheDateTIme < (@._In_theDataTime + 1)
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
> This is probably a really simple one:
> Given a table with a DATETIME column, how can I do an = or <> comparison
> with just the date part?
> ie.
> SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
> SOMEFUNC(@._In_theDataTime)
> , where SOMEFUNC will just return the date part (ignoring the time part)
> of theDateTime.
>
> Thanks.....
>|||Okay, thanks (and yes, my stored proc names are odd, as I use:
@._In_somevar, @._Out_somevar, @._InOut_somevar). I find it easier to
distinguish between local vars and parameters just by prefixing parameters
with `_'. It's a personal thing (no guidelines here) - also, quite often I
write Data when I mean to write Date and visa versa - apologies as I'm
slightly dyslexic. Anyway:
WHERE MyTable.TheDateTime >= (@._In_theDateTime)
AND MyTable.TheDateTime < (@._In_theDateTime + 1)
This won't work though, because @._In_theDateTime might be less than
MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the
latter 1pm). I was thinking I would be able to say "DATEPART(x) =
DATEPART(y)" or something. I don't have millions of rows (average around
20 - 40k rows in this table), so I don't mind too much about not using the
indexes (at least for now).
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyawDVfjFHA.3336@.tk2msftngp13.phx.gbl...
> You don't want to apply expressions to both sides of the WHERE clause,
> otherwise you will negate any indexes. How about (assuming your oddly
> named variable is a DATETIME or, better yet, SMALLDATETIME):
> WHERE MyTable.TheDateTIme >= (@._In_theDataTime)
> AND MyTable.TheDateTIme < (@._In_theDataTime + 1)
>
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
>|||> This won't work though, because @._In_theDateTime might be less than
> MyTable.TheDateTime but still on the same date (ie. the former is 3pm, the
> latter 1pm).
So why not pass in JUST THE DATE?
Or, in the stored procedure, say:
SET @._in_theDateTime = 0 + DATEDIFF(DAY, 0, @._in_TheDateTime)
-- this will set the time portion to midnight without any messy
casts/conversions.|||See CONVERT function in SQL Server Books Online. It offers you various
styles for date formatting. One of them (112) gets rid of time portion.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:dbo636$hgi$1$8302bc10@.news.demon.co.uk...
This is probably a really simple one:
Given a table with a DATETIME column, how can I do an = or <> comparison
with just the date part?
ie.
SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <>
SOMEFUNC(@._In_theDataTime)
, where SOMEFUNC will just return the date part (ignoring the time part) of
theDateTime.
Thanks.....|||Okay I get it now - yes this will work. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23vvTFhfjFHA.3608@.TK2MSFTNGP12.phx.gbl...
> So why not pass in JUST THE DATE?
> Or, in the stored procedure, say:
> SET @._in_theDateTime = 0 + DATEDIFF(DAY, 0, @._in_TheDateTime)
> -- this will set the time portion to midnight without any messy
> casts/conversions.
>sqlsql

Comparing columns in two tables

Hi DBA's,

I need to figure out how to compare the column data in two distinct tables. I have two files that populate these two tables. Basically I am doing a file comparison here. Let me explain the process:

Table 1

Col 1 Col 2

ID Name

1 A
2 C,D
3 F

Table 2

Col

Name

E
F
D

Now if there is any data that is present in Table 2 that matches with the data in table 1 then I need to write the entire record of table 2 into a separate table OR file.

Here is what I think I need to do.

1. Take first record from Table 1 and scan Table 2 to see if the Name 'A' exists. If yes put/insert the record from Table 2 in a seprate table say table 3 and then go to the second record. If no match then go directly to the second record in table 1. Repeat the process till every record in table 1 is compared to the records in table 2.

2. Now the trick here is some Names have only last name. Others have last name and first name. So for Table 1, Name C,D should be a match to D in Table 2. I have to send this record to Table 3. How do I accomplish that? Should I spilt the Col2 into columns. How do I do that?

Please note that table 2 would have close to 5000 records.

Please advise.

Thanks in anticipation.Think you'll be having a lot of false matches on Smtih and Jones...

Maybe not Kaiser though...

Can you post the DDL of the Tables, and sample data..like

CREATE TABLE myTable99 (Col1 int, Col2, varchar(50), ect

For Sample Data, something like..

INSERT INTO myTable99 (Col1, Col2, ect)
SELECT 1, 'Brett Kaiser', ect UNION ALL
SELECT 2, 'Indiana Jones', ect UNION ALL
SELECT 3, 'Jones', ect UNION ALL
SELECT 4, 'Jeff Smith', ect UNION ALL

get the picture?

It's easier to help when we have the actual stuff...

Still think the matching will be a fudge though..

maybe you can match on exact, remove that population, then do the fudge on a smaller subset...sqlsql

Comparing columns contents between 2 table...

any idea on the syntax for such a querie? all my attempts to compare an item # column in 2 seperate tables keep coming up an incorrect syntax...(I want to compare one column in one table with a column in another table).
Thanks in advance:rolleyes:I'm not sure on what you are asking?

SELECT a.*
FROM TBL a, TBL b
WHERE a.col = b.col

or

SELECT a.*
FROM TBL a
WHERE NOT EXISTS
(
SELECT *
FROM TBL b
WHERE a.col = b.col
)

??|||sorry I did not respond to your reply:

Thanks for the info - also the first one works for me...

SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname

simple really :)

Again thanks... Now if I can only get it to ignore NULL and empty cells...|||SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname
AND tableB.b_fieldname is not null
and LEN (tableB.b_fieldname) <> 0

--you can also put in tableA.a_fieldname as well if you so desire
-- tell me if that sorts out your problem|||thanks works fine :)

got to get it onto my production server and run now... Thanks,

Comparing Column names and indexes

Is there a way to compare tables in 2 different databases to find out if they have the same indexes and column names and keys. Or maybe a tool i dont know about.Again, not sure if this violates the non-commercial nature of this forum, but I use a product called SQL Compare from Red gate Software, Ltd. (www.red-gate.com).

It's definitely worth the price. It will do exactly what you are looking for, plus much more.

Regards,

hmscott

Comparing column data in two tables

DBA's of SQL Server:

I have two tables, the columns of which I need to compare.

Table A
Col1............Col2
Name.......ID

ABC........ 1
DEF ........ 2
WXY.........3

Table B

Col1......Col2....Col3
Name1...Name2..Name3
A......... B.......C
D........ G........Z

I need to output every record of Table A where the even a single alphabet in name column matches with Table B.

So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?

Appreciate your help.Try something like this

select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name2) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name3) +'%'

Originally posted by vivek_vdc
DBA's of SQL Server:

I have two tables, the columns of which I need to compare.

Table A
Col1............Col2
Name.......ID

ABC........ 1
DEF ........ 2
WXY.........3

Table B

Col1......Col2....Col3
Name1...Name2..Name3
A......... B.......C
D........ G........Z

I need to output every record of Table A where the even a single alphabet in name column matches with Table B.

So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?

Appreciate your help.|||This doesn't work. Here is what happens. Let me make the tables simple.

Table A
Name......ID
B C A........1
D E F.........2
A............3

Table B

Name1
A

I need all records that contain alphabet A. With the query -

select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'

Only record 3 (A...3) is returned. I also need record 1. Note that the individual alphabets are separated by spaces.

Let me know. Thanks.

Originally posted by fhunth
Try something like this

select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name2) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name3) +'%'|||Here you go. I created two tables exactly as you specified:
create table table1
(Name varchar(3)
,ID int identity (1,1))
It has values:
ABC, 1
DEF, 2
WXY, 3

create table table2
(Name1 char(1)
,Name2 char(1)
,Name3 char(1))

It has values:
A, B, C
D, G, Z

Now run this:

select distinct table1.Name, table1.ID from table1, table2
where charindex (substring(NAME,1,1), Name1+Name2+Name3) <> 0
or charindex (substring(NAME,1,2), Name1+Name2+Name3) <> 0
or charindex (substring(NAME,1,3), Name1+Name2+Name3) <> 0

Comparing a column list split to a table.

Let me see if I can explain my situation clearly.

I have a table with the columns:

answer_id, question_id, member_id, answer

- answer_id is the primary key for the table.
- question_id relates to another table with questions for a user. The
table holds the question and the possible choices in a varchar field
separated by a delimiter.
- member_id is self-explanatory
- answer is a varchar field of all the choices the user selected,
separated by a delimiter.

Here is my problem.

I am trying to search all members that have answered, say, question_id
= 2 where they selected 'brown' as one of their choices.

i can do this if they selected ONLY that item, but not multiple items.

The problem is this portion

answer in
(select valu from dbo.iter_intlist....

I need this to be something like...

function_to_return_all_separated_answers(answer) in
(select valu from dbo.iter_intlist

The current way, it is only returning members that have an answer
'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,
what I need to do is separate the list of answers and say :

select member_id from profile_answers where

ANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (select
valu from dbo.iter_intlist...

It seems I might have to join or something, I am just a little lost
right now.

Here is my proc.

ALTER procedure search_detailed_get_ids

@.question_id as integer,
@.answers as varchar(8000),
@.member_ids ntext

as

declare @.v as varchar(8000)

--get the delimited string of all possible answers
set @.v = (select bind_data from profiles_questions where question_id =
@.question_id)

--prepare it for the function only accepting 1 char
set @.v = replace(@.v, '||', '|')

--gimme all members that match
select member_id from profiles_answers where question_id = @.question_id
and answer in
(select valu from dbo.iter_intlist_to_table(@.v, '|') where listpos in

(select valu from dbo.iter_intlist_to_table(@.answers, ',')))
and member_id in (select valu from dbo.iter_intlist_to_table
(@.member_ids, ','))

return
gotwdo (johnj@.tampawebdevelopment.com) writes:
> Let me see if I can explain my situation clearly.
> I have a table with the columns:
> answer_id, question_id, member_id, answer
> - answer_id is the primary key for the table.
> - question_id relates to another table with questions for a user. The
> table holds the question and the possible choices in a varchar field
> separated by a delimiter.
> - member_id is self-explanatory
> - answer is a varchar field of all the choices the user selected,
> separated by a delimiter.

Redo the table design, and move answer to a subtable:

CREATE TABLE answers (answer_id int NOT NULL,
answer varchar(10) NOT NULL,
CONSTRAINT pk_answers PRIMARY KEY (answer_id, answer)

Do the same for the answers to the questions.

If you are really stuck with the design, use temp tables in the
procedure. But note that with the design above it is not possible to
answer Brown more than once to a question - which presumably is a
good thing.

Working with comma-separated lists is really painful in SQL, because
relation algebra - in which SQL does take its foundation - assumes
that values are atomic, and yours aren't.

I can not really write a query for you with these tables, since I
couldn't make out whether Brown had to be a correct answer, or if
you just wanted any one who had answered Brown.

And, oh, the usual advice apply:

o CREATE TABLE statements for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.

That increases your does for a tested solution in reposnse.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> o CREATE TABLE statements for the tables involved.
> o INSERT statements with sample data.
> o The desired result given the sample.
> That increases your does for a tested solution in reposnse.

I am stuck with the design of the tables. I can probably do some
looping to achieve what I need but I wanted to avoid loops at all costs
because of the performance.

Below is some table and data code. Basically, if you run the procedure
first provided, it will work if someone answers only ONE of the
possible choices (question_id = 1) BUT, if someone answers multiple
items, it will fail. I need it to split the list of answers and split
the list of possible choices and see if *ANY* match at all. Does that
make sense? I appreciate all help.

CREATE TABLE [dbo].[profiles_answers] (
[answer_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_id] [int] NOT NULL ,
[member_id] [int] NOT NULL ,
[answer] [varchar] (7000) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[profiles_questions] (
[question_id] [int] IDENTITY (1, 1) NOT NULL ,
[group_id] [int] NOT NULL ,
[display_label] [varchar] (50) NOT NULL ,
[field_type_id] [int] NOT NULL ,
[bind_data] [varchar] (7000) NOT NULL ,
[display_order] [int] NOT NULL ,
[status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[profiles_questions]
(group_id, display_label, field_type_id, bind_data, display_order,
status)
VALUES
(1, 'Interests', 1, 'Computers||Outdoors', 1, 1)
INSERT INTO [dbo].[profiles_questions]
(group_id, display_label, field_type_id, bind_data, display_order,
status)
VALUES
(1, 'Hair Color', 1, 'Brown||Black||Blonde', 1, 1)

INSERT INTO [dbo].[profiles_answers]
(question_id, member_id, answer)
VALUES
(2, 1, 'Brown')

INSERT INTO [dbo].[profiles_answers]
(question_id, member_id, answer)
VALUES
(1, 1, 'Computers, Outdoors')|||twdo (johnj@.tampawebdevelopment.com) writes:
> I am stuck with the design of the tables. I can probably do some
> looping to achieve what I need but I wanted to avoid loops at all costs
> because of the performance.

If you feel that you cannot change that design, don't even consider
performance. Perfomance is not achievable with that design.

> Below is some table and data code. Basically, if you run the procedure
> first provided, it will work if someone answers only ONE of the
> possible choices (question_id = 1) BUT, if someone answers multiple
> items, it will fail. I need it to split the list of answers and split
> the list of possible choices and see if *ANY* match at all. Does that
> make sense? I appreciate all help.

Thanks for the tables and insert data. But it would help a lot if you
gave different examples of input parameters, and what result you expect.
You refer to your procedure, but it cannot even run on the test data,
since it uses iter_intlist_to_table, and the answers are character...

Anyway, I don't really like guessing, since I may be wasting my time
on the wrong guess.

One hint is that instead of IN, use EXISTS instead. That's a more
powerful operator, with fewer gotchas, and often better performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Comparing 2 dbs for differences in column definitions

Hi All
I have 2 dbs, one for prodcution and the other one for development
environment. They are meant to be the same.
Is there a script/way to compare the dbs for any difference in column
datatypes, length.
This query has what i want to compare:
select
table_name, column_name, data_type, character_maximum_length [Length],
is_nullable [Null], numeric_precision NuPrec, numeric_scale NuScal,
datetime_precision DTPrec
from
information_schema.columns
order by table_name ASC, column_name ASC
Thank you on advance.Maybe you should take a look at Red Gate's SQL Compare.
http://www.red-gate.com/
ML
http://milambda.blogspot.com/|||See replies to the post "Comparing two databases".
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:844E5968-C506-4A25-A410-2128BA6F2741@.microsoft.com...
> Hi All
> I have 2 dbs, one for prodcution and the other one for development
> environment. They are meant to be the same.
> Is there a script/way to compare the dbs for any difference in column
> datatypes, length.
> This query has what i want to compare:
> select
> table_name, column_name, data_type, character_maximum_length [Length],
> is_nullable [Null], numeric_precision NuPrec, numeric_scale NuScal,
> datetime_precision DTPrec
> from
> information_schema.columns
> order by table_name ASC, column_name ASC
> Thank you on advance.
>

compare varchar column values case-sensitively?

Is it possible to case-sensitively compare 2 varchar column values
without changing the collation on the column to case-sensitive? For
example, I have column called Note on a table called Operation, and I'd
like to select distinct note, case-sensitively, without changing the
collation.
Possible?
-KJ<n_o_s_p_a__m@.mail.com> wrote in message
news:1135287389.111343.321820@.g47g2000cwa.googlegroups.com...
> Is it possible to case-sensitively compare 2 varchar column values
> without changing the collation on the column to case-sensitive? For
> example, I have column called Note on a table called Operation, and I'd
> like to select distinct note, case-sensitively, without changing the
> collation.
> Possible?
> -KJ
>
SELECT columnlist
FROM tablename
JOIN tablename
WHERE col1 COLLATE <collation name> = col2 COLLATE <collation name>
Rick Sawtell|||you can specify a collation on the select
e.g. [using pubs.authors out of the box]
use pubs
update authors
set au_lname = 'ringer' -- only duplicated last name
where au_id='899-46-2035'
select distinct au_lname collate SQL_Latin1_General_CP1_CS_AS
from authors
-- 23 rows return
select distinct au_lname
from authors
-- 22 rows return
n_o_s_p_a__m@.mail.com wrote:
> Is it possible to case-sensitively compare 2 varchar column values
> without changing the collation on the column to case-sensitive? For
> example, I have column called Note on a table called Operation, and I'd
> like to select distinct note, case-sensitively, without changing the
> collation.
> Possible?
> -KJ
>|||Thanks, Rick and Trey, for the quick and accurate replies!
-KJ

compare two different date format column?

Hi guys,

I want to compare two date fields, which are located on different tables. One field contains a date value in this format: ###/###-#### and the other field on the other table contains (###-###-#### and some ##########)

So I want to compare this two different format. May be changing both the formats to a common format and make the comparison, something like that.

Any idea is appreciate.

Hi Amde

Just for clarity, would you be able to provide real examples (from your tables) of a date in each format?

Thanks
Chris

|||

oh yeah,

Table 1.

Phone no.

847/678-2828

Table 2.

Phone no.

333-777-5555

252 321 3333

2223334444

(123)456-789

...

|||

For phone numbers, I would consider creating a Function that strips out all but the numbers -and then saving and comparing only numbers. (Leave the formating for the client application.)

Comparing DATE fields will be somewhat different -however formating is not an issue if the datatype is (small)datetime. If Date data is stored as (var)char -that is a whole different can of worms.

|||

Well that changes things slightly - you originally said that these were date fields...

Another couple of questions - how many rows of data are you looking to compare? Are there any other formats that we should know about?

Thanks
Chris

|||

I've just put together the function below that will strip any non-numerical characters from an input string.

Don't expect fantastic performance from the function when comparing thousands of rows. It would be far better, as Arnie highlighted, to store an unformatted string and leave the formatting to the client.

Chris

CREATE FUNCTION dbo.CleanString(@.InputString VARCHAR(8000))
RETURNS VARCHAR(8000)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @.NewString VARCHAR(8000)
SET @.NewString = ''
DECLARE @.ValidChars CHAR(10)
SET @.ValidChars = '0123456789'
DECLARE @.Loop INT
SET @.Loop = 1
WHILE @.Loop <= LEN(@.InputString)
BEGIN
IF CHARINDEX(SUBSTRING(@.InputString, @.Loop, 1), @.ValidChars) > 0
SET @.NewString = @.NewString + SUBSTRING(@.InputString, @.Loop, 1)

SET @.Loop = @.Loop + 1
END

RETURN @.NewString
END
GO

SELECT dbo.CleanString('1329842DCXLXMC2QPWXMQWX09SWDM09324')
--Returns: 132984220909324

SELECT CASE WHEN dbo.CleanString('1M&&2-FL3FCM4+=5M#6khn7wmx8KE9AAS0')
= dbo.CleanString('cxmn1dUI2QWWJHB3NCU4PWCK5IVN67DBW8XW9K 0')
THEN 'These strings are the same when cleansed.'
END,
dbo.CleanString('1M&&2-FL3FCM4+=5M#6khn7wmx8KE9AAS0'),
dbo.CleanString('cxmn1dUI2QWWJHB3NCU4PWCK5IVN67DBW8XW9K 0')
--Returns: 'These strings are the same when cleansed.', 1234567890, 1234567890

|||

Oh!my bad, I am really sorry.

- I want to say a phone number (which is varchar data type)

- The first table contain about 40,000 records and the second table contains about 60, 000 records. So I want to get the difference.

- Those are the common formats. I hope if I find a solution for one of the format, I can apply similar logic.

Sorry for the confusion.

|||

For a large amount of data, avoiding the loop may increase performance.

By using a 'Numbers' table, this method avoids looping.

-- Prepare Numbers Table

SET NOCOUNT ON
CREATE TABLE Numbers ( n int )
GO

DECLARE @.n int
SET @.n = 1
WHILE @.n < 100
BEGIN
INSERT INTO Numbers VALUES ( @.n )
SET @.n = ( @.n + 1 )
END
-- End Numbers Table

CREATE FUNCTION dbo.NumbersOnly
( @.Text varchar(100) )
RETURNS varchar(100)
AS
BEGIN
DECLARE @.Output varchar(100)
SELECT @.Output = ''
SELECT @.Output = @.Output + CASE
WHEN RealNum LIKE '[0-9]'
THEN RealNum
ELSE ''
END
FROM (SELECT substring( @.Text, n, 1 ) RealNum
FROM Numbers
) d
RETURN @.Output
END
GO

SELECT dbo.NumbersOnly( '(555) 987-4321' )

--
5559874321

|||

You should clean the data and store it in a normalized manner. This is the best way to handle. Solutions using UDFs are fine but they will be slow and you will have to keep changing them if you find newer formats or rules. For example, how do you handle phone numbers like 1-800-96MSFAX? What about phone numbers from other countries etc? There are so many cases where hard-coded solutions break and you will end up with more problems than necessary. I have built data warehouses in the past (in my previous company for data mining/analytics purpose) and we used public geography data, address book scrubbing software etc. So it is better to invest in a solution or package that is specialized for the type of data you are dealing with and focus on your business problem. Once you normalize the phone numbers then it is very easy to compare them - you can easily restrict phone numbers based on country code for example or compare different formats easily.

|||

I agree with you. Having a normalized database is the best thing. However, I want to do this task only for the time being. Here is the thing, I received an excel sheet which contains thousands of records. On the other hand we have a table in our database. So my goal is to get the difference and insert the new data to my table. So what I did is I created an SSIS package to migrate the excel sheet data into SQL Server table, so that it will be easy to write a query to get the differential data. Inserting the data is not an issue. All I need is to compare these two tables and get the different. The only unique field I have to do the comparison is the PHONE column. And the PHONE column has a different format, as I tried to explain before.

Thx.

|||

Thanks guys, I really appreciate your help. I'm good now.