finding a word, submitting part of it

Dear All

Does anyone know any syntax that will allow me to find entries in a atable when only part of a word is entered. I have a table that has been full txt indexed at the moment I am using the CONTAINS keyword to search fields. This works fine 'select * from tblX where contains(colX, 'gill')', gives me back what i want, but what if i type this 'select * from tblX where contains(colX, 'ill')' it doesn't find anything. What i want is a command that will search all the words in a table column. Does anyone know of a command that will do this?

ThanksI got into the CONTAINS (T-SQL) syntax, and I found only prefix_terms like 'ill*', but I didn't found a solution for a '*ill' condition. Using the LIKE operator isn't an option either. I hope that other guys can help you. :(|||Well, LIKE is an option, just not a very efficient one. But, it's there for just this type of search.

SELECT * FROM tblX WHERE colX LIKE '%ill'

You can also replace the wildcard % with:

_ looks for any single char

[] looks for any chars or range of chars you specify ([a-q], [abcd], [1-9], etc)

[^] same as above, but exclusion chars ([^a-f] means anything but a through f)|||Another option is to use CHARINDEX:

where charindex('ill', colx) > 0

I don't know how this compares to LIKE for speed, but I suspect it would be faster because it has less functionality (no wildcards) and thus may have less processing overhead.

blindman|||I think you are right. Though, any time you ask SQL to do string processing, you are going to take a hit. They seem to have build in several levels of functionality here, getting progressively deeper as you need them. Can't think of why they would do that vs a sigle flexible function, unless the performance would dive.

Find Missing data from table

Here is an issue that has me stumped for the past few days. I have a
table called MerchTran. Among various columns, the relevant columns for
this issue are:

FileDate datetime
, SourceTable varchar(25)

SQL statement:
SELECT DISTINCT
FileDate
, SourceTable
FROM
MerchTran
ORDER BY
FileDate
, SourceTable

Data looks like this:
FileDate DataSource
-----------
2005-02-13 00:00:00.000S1
2005-02-13 00:00:00.000S2
2005-02-13 00:00:00.000S3
2005-02-14 00:00:00.000S1
2005-02-14 00:00:00.000S2
2005-02-14 00:00:00.000S3
2005-02-15 00:00:00.000S2
2005-02-15 00:00:00.000S3
2005-02-16 00:00:00.000S1
2005-02-16 00:00:00.000S2
2005-02-16 00:00:00.000S3
2005-02-17 00:00:00.000S1
2005-02-17 00:00:00.000S2
2005-02-18 00:00:00.000S1
2005-02-18 00:00:00.000S2
2005-02-18 00:00:00.000S3
2005-02-19 00:00:00.000S1
2005-02-19 00:00:00.000S3

We run a daily process that inserts data in to this table everyday for
all 3 sources S1, S2, S3

Notice how some data is missing indicating the import process for that
particular source failed.
Example: Missing record
2005-02-15 00:00:00.000S1
2005-02-17 00:00:00.000S3
2005-02-19 00:00:00.000S2

Can someone please help me with a SQL Statement that will return me the
3 missing records as above.

Thanks in advance for all your help!

DBA in distress!
Vishal[posted and mailed, please reply in news]

SQLJunkie (vsinha73@.gmail.com) writes:
> Here is an issue that has me stumped for the past few days. I have a
> table called MerchTran. Among various columns, the relevant columns for
> this issue are:
>...
> We run a daily process that inserts data in to this table everyday for
> all 3 sources S1, S2, S3
> Notice how some data is missing indicating the import process for that
> particular source failed.
> Example: Missing record
> 2005-02-15 00:00:00.000 S1
> 2005-02-17 00:00:00.000 S3
> 2005-02-19 00:00:00.000 S2
> Can someone please help me with a SQL Statement that will return me the
> 3 missing records as above.

The below assumes that daily means daily and not only Monday to Friday.
It will catch if a date is missing entirely, although if the first or
last day is missing.

CREATE TABLE demo (date datetime NOT NULL,
src char(2) NOT NULL,
CONSTRAINT pk_demo PRIMARY KEY (date, src))
go
-- Creates a numbers table in a somewhat casual way that
-- is not guaranteed to work. The MAXDOP turns of parallelism
-- to improve our chances.
SELECT TOP 8000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
OPTION (MAXDOP 1)
go
INSERT demo (date, src)
SELECT '2005-02-13 00:00:00.000', 'S1' UNION
SELECT '2005-02-13 00:00:00.000', 'S2' UNION
SELECT '2005-02-13 00:00:00.000', 'S3' UNION
SELECT '2005-02-14 00:00:00.000', 'S1' UNION
SELECT '2005-02-14 00:00:00.000', 'S2' UNION
SELECT '2005-02-14 00:00:00.000', 'S3' UNION
SELECT '2005-02-15 00:00:00.000', 'S2' UNION
SELECT '2005-02-15 00:00:00.000', 'S3' UNION
SELECT '2005-02-16 00:00:00.000', 'S1' UNION
SELECT '2005-02-16 00:00:00.000', 'S2' UNION
SELECT '2005-02-16 00:00:00.000', 'S3' UNION
SELECT '2005-02-17 00:00:00.000', 'S1' UNION
SELECT '2005-02-17 00:00:00.000', 'S2' UNION
SELECT '2005-02-18 00:00:00.000', 'S1' UNION
SELECT '2005-02-18 00:00:00.000', 'S2' UNION
SELECT '2005-02-18 00:00:00.000', 'S3' UNION
SELECT '2005-02-19 00:00:00.000', 'S1' UNION
SELECT '2005-02-19 00:00:00.000', 'S3'
go
SELECT *
FROM (SELECT date = dateadd(DAY, n - 1, mindate)
FROM numbers n
CROSS JOIN (SELECT mindate = MIN(date),
maxdate = MAX(date)
FROM demo) d
WHERE n BETWEEN 1 AND datediff(DAY, mindate, maxdate) + 1) AS a
CROSS JOIN (SELECT src = 'S1' UNION SELECT 'S2' UNION SELECT 'S3') AS s
WHERE NOT EXISTS (SELECT *
FROM demo
WHERE demo.date = a.date
AND demo.src = s.src)
go
DROP TABLE numbers
DROP TABLE demo

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the quick response Erland. I see what you are trying to do,
but I have data in the table starting 2003. Would be a lot of work if I
have to insert 3 records in table numbers for everyday (including
saturday and sunday) starting 2003. But I will try this nonetheless.

Thanks!

Vishal|||SQLJunkie (vsinha73@.gmail.com) writes:
> Thanks for the quick response Erland. I see what you are trying to do,
> but I have data in the table starting 2003. Would be a lot of work if I
> have to insert 3 records in table numbers for everyday (including
> saturday and sunday) starting 2003. But I will try this nonetheless.

Numbers need to have as many rows as there days in the timespan.

In fact, it does have to be a numbers table. I used a numbers, because
1) it was easier to compose one on the fly.
2) a numbers table is applicable other problems as well.
However, in our system we don't have a numbers - but we have a dates table,
and here is how we fill it in a safe way:

CREATE TABLE dates (
thedate aba_date NOT NULL,
CONSTRAINT pk_dates PRIMARY KEY (thedate)
)

-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)

-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'

SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE TABLE Readings
(collection_date DATETIME NOT NULL,
source CHAR(2) NOT NULL
CHECK(source IN ('S1', 'S2', 'S3')),
PRIMARY KEY (collection_date, source));

This is a quick way to see what you do have on the days with missing
data:

CREATE VIEW Shortdates(collection_date, source)
AS
SELECT collection_date, source
FROM Readings
GROUP BY collection_date
HAVING COUNT(*) < 3;

We can now use this view or make it into a derived table. We will also
another table, which probably exists already.

CREATE TABLE Sources (sources CHAR(2) NOT NULL PRIMARY KEY, ..);

SELECT R1.collection_date, S1.source
FROM Readings AS R1,
Sources AS S1
WHERE S1.source
NOT IN (SELECT R2.source
FROM Shortdates AS R2 -- or use a derived table
WHERE R1.collection_date
= R2.collection_date);

-- Untested

find duplicate entries in lastName field.

hey Everyone.
Could anyone give show me a query that would return just the records from a
table that have more than just one of a last name.
Thanks
'If it looks like your going to bite it, try not to ruin the shoot' -
stuntman credo
For such problems always post your table structures, sample data & expected
results. For details refer to : www.aspfaq.com/5006
Here is a solution based on guesswork:
SELECT * -- use column names
FROM tbl t1
WHERE t1.lastname IN (
SELECT t2.lastname
FROM tbl t2
GROUP BY t2.lastname
HAVING COUNT( * ) > 1 ) ;
Anith
|||Example:
use northwind
go
select employeeid, firstname, lastname
into t
from dbo.employees
go
insert into t (firstname, lastname)
select firstname, lastname
from dbo.employees
where employeeid = 7
go
select
e1.employeeid,
e1.firstname,
e1.lastname
from
dbo.t as e1
inner join
(
select
lastname
from
dbo.t
group by
lastname
having
count(*) > 1
) as e2
on e1.lastname = e2.lastname
order by
e1.employeeid
go
drop table t
go
AMB
"kahunaVA" wrote:

> hey Everyone.
> Could anyone give show me a query that would return just the records from a
> table that have more than just one of a last name.
> Thanks
> --
> 'If it looks like your going to bite it, try not to ruin the shoot' -
> stuntman credo
|||Thanks Anith, Gave me the basis of my solution.
"Anith Sen" wrote:

> For such problems always post your table structures, sample data & expected
> results. For details refer to : www.aspfaq.com/5006
> Here is a solution based on guesswork:
> SELECT * -- use column names
> FROM tbl t1
> WHERE t1.lastname IN (
> SELECT t2.lastname
> FROM tbl t2
> GROUP BY t2.lastname
> HAVING COUNT( * ) > 1 ) ;
> --
> Anith
>
>

find duplicate entries in lastName field.

hey Everyone.
Could anyone give show me a query that would return just the records from a
table that have more than just one of a last name.
Thanks
--
'If it looks like your going to bite it, try not to ruin the shoot' -
stuntman credoFor such problems always post your table structures, sample data & expected
results. For details refer to : www.aspfaq.com/5006
Here is a solution based on guesswork:
SELECT * -- use column names
FROM tbl t1
WHERE t1.lastname IN (
SELECT t2.lastname
FROM tbl t2
GROUP BY t2.lastname
HAVING COUNT( * ) > 1 ) ;
Anith|||Example:
use northwind
go
select employeeid, firstname, lastname
into t
from dbo.employees
go
insert into t (firstname, lastname)
select firstname, lastname
from dbo.employees
where employeeid = 7
go
select
e1.employeeid,
e1.firstname,
e1.lastname
from
dbo.t as e1
inner join
(
select
lastname
from
dbo.t
group by
lastname
having
count(*) > 1
) as e2
on e1.lastname = e2.lastname
order by
e1.employeeid
go
drop table t
go
AMB
"kahunaVA" wrote:

> hey Everyone.
> Could anyone give show me a query that would return just the records from
a
> table that have more than just one of a last name.
> Thanks
> --
> 'If it looks like your going to bite it, try not to ruin the shoot' -
> stuntman credo|||Thanks Anith, Gave me the basis of my solution.
"Anith Sen" wrote:

> For such problems always post your table structures, sample data & expecte
d
> results. For details refer to : www.aspfaq.com/5006
> Here is a solution based on guesswork:
> SELECT * -- use column names
> FROM tbl t1
> WHERE t1.lastname IN (
> SELECT t2.lastname
> FROM tbl t2
> GROUP BY t2.lastname
> HAVING COUNT( * ) > 1 ) ;
> --
> Anith
>
>