finding lower case data

I have a table with a VIN number column, I need to find all entries where th
e
VIN contains a lower case character, example '4d0131P2X4E146985'. This recor
d
should be included in the results because it contains 'd'.. What would be th
e
easiest way to write a query to return these, can I use regular expression i
n
T-SQL...?
Thanks,
DanAlien2_51,
Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column
in question. I guess it is using a case insensitive one. Use the COLLATE
clause to tell SQL Server which collation to use during the operation and us
e
a case sensitive one. You can also use function binary_checksum.
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
select *
from
(select '4d0131P2X4E146985' as c1
union all
select '4D0131P2X4E146985' as c1
) as t1
where binary_checksum(c1) != binary_checksum(upper(c1))
AMB
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan|||You could do this:
select VIN
from yourTable
where VIN like '%[abcdefghijklmnopqrstuvwxyz]%'
collate Latin1_General_CS_AS
-- or
select VIN
from yourTable
where VIN like '%[a-z]%'
collate Latin1_General_BIN
Note that it will not work to use the range [a-z] in the first
example, because in the CS_AS collation, [a-z] contains the
uppercase letters A through Y.
Steve Kass
Drew University
http://www.stevekass.com
Alien2_51 wrote:

>I have a table with a VIN number column, I need to find all entries where t
he
>VIN contains a lower case character, example '4d0131P2X4E146985'. This reco
rd
>should be included in the results because it contains 'd'.. What would be t
he
>easiest way to write a query to return these, can I use regular expression
in
>T-SQL...?
>Thanks,
>Dan
>|||Thanks Alejandro... Just what I needed, and so simple...
DB
"Alejandro Mesa" wrote:
> Alien2_51,
> Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the colu
mn
> in question. I guess it is using a case insensitive one. Use the COLLATE
> clause to tell SQL Server which collation to use during the operation and
use
> a case sensitive one. You can also use function binary_checksum.
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS
> select *
> from
> (select '4d0131P2X4E146985' as c1
> union all
> select '4D0131P2X4E146985' as c1
> ) as t1
> where binary_checksum(c1) != binary_checksum(upper(c1))
>
> AMB
>
> "Alien2_51" wrote:
>|||Alien, I guess you might want to check this out
http://vadivel.blogspot.com/2003/12...e-searches.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"Alien2_51" wrote:

> I have a table with a VIN number column, I need to find all entries where
the
> VIN contains a lower case character, example '4d0131P2X4E146985'. This rec
ord
> should be included in the results because it contains 'd'.. What would be
the
> easiest way to write a query to return these, can I use regular expression
in
> T-SQL...?
> Thanks,
> Dan

Finding last entries

Hi, I am searching for the most easy SQL solution:

Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?

Thx for reply.

dajmHow's your resume' looking these days? Is it up to date?|||The last row updated? The last row inserted? Physical location might not really mean anything.

Are there any related tables that could help us out.

You might be screwed if you want to figure this out in your current setup.

Going forward you could use a trigger to populate a new table for this purpose.|||If you use TOP 1 you get a result that is as good as any.
Noone can say that it is false, because if they can, they must say why, and if they say why, then you have a rule you could use in your select statement.

That's what I should've done in your shoes, given that there was noone to ask that is ...

As Thrasymachus says, there is no way (with the given info) that the solution can be found, atleast not that I can see.|||O.K. Here's an example for everybody

Car-ID |sales price

1|5
3|3
2|6
1|7
3|4

As result I need to get
2|6
1|7
3|4

With Navision language C/Side it is very easy to catch the last entries of database by setting a filter on car-id, but how do I get this result by using SQL ?|||Example pasted from earlier post :

Car-ID |sales price

1|5
3|3
2|6
1|7
3|4

As result I need to get
2|6
1|7
3|4

Nothing tells you that these three last entries in your result is in fact the three last ones. All you know is that some means of presentation choose to show you those results in that order. Running the same question again or from another client might return other results. Therefore you still haven't presented any useful info to solve the problem, unless you wanna use my TOP solution earlier. In that case replace the TOP 1 with TOP 3 ...|||Data in a database is not guaranteed to be in any order unless specified in the table design. If this table has a clustered index (as it should), then the data is no longer in the order in which it was entered.

Period.

If you do not store the datetime the record was entered, you cannot expect to sort or filter on the datetime the record was entered.|||There exists no index, but the no. of cars is dynamic.
I cannot use a "select top n" statement as I never know "n".
Can I combine this however with a "select count (distinct car-id)" ?

dajm|||Sorry, all back. There is a clustered index on a integer field with incremental no., used as a uniqueidentifier.
Does this help somehow ?|||well yes. but you said there were no keys earlier no keys...

and then you said the keys were not dynamic

"Lets say i have 500000 rows of cars in one table with 30000 different car ID's"

just use the max() function on your newly discovered field.|||A max() on this field returns just the last row, but not every last row of my 30000 vehicles, means 30000 rows.|||See this thread for a solution to your problem:

http://www.dbforums.com/showthread.php?t=1094858&page=1&pp=15

Finding duplicate entries in a "smart" way - by comparing first two words

What is the best way to compare two entries in a single table where
the two fields are "almost" the same?

For example, I would like to write a query that would compare the
first two words in a "company" field. If they are the same, I would
like to output them.

For example, "20th Century" and "20th Century Fox" in the company
field would be the same.

How do I do this? Do I need to use a cursor? Is it as simple as using
"Like?"Here's an example:

CREATE TABLE Sometable (company VARCHAR(20) PRIMARY KEY)

INSERT INTO Sometable VALUES ('20th Century')
INSERT INTO Sometable VALUES ('20th Century Fox')
INSERT INTO Sometable VALUES ('Something Else')

SELECT company
FROM Sometable AS S
JOIN
(SELECT first_two_words
FROM
(SELECT company,
LEFT(company,
CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))
AS first_two_words
FROM Sometable) AS X
GROUP BY first_two_words
HAVING COUNT(*)>1) AS T
ON T.first_two_words =
LEFT(company,CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))

--
David Portas
----
Please reply only to the newsgroup
--|||>> a query that would compare the first two words in a "company"
field [sic]. If they are the same, I would like to output them. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY,
company CHAR(35) NOT NULL,
...);

SELECT F1.foo_key, F2.foo_key
FROM Foobar AS F1, Foobar AS F2
WHERE CHARINDEX(F1.company, F2.company) = 1
AND F1.foo_key < F2.foo_key;

finding DISTINCT combinations

Hi,

I need to get 8 columns from a table.
1)
I must get only one entry of each unique combination of columsn 1,2,3.
2)
These entries must have the max. possible value in column 4.

Currently I have,

SELECT DISTINCT
one AS one,
two AS two,
three AS three,
four AS four,
five AS five,
six AS six,
seven AS seven,
eight AS eight
INTO #temp
FROM TABLE1 A

INSERT INTO ANSWERTABLE
(one, two, three, four, five, six, seven, eight)

SELECT one, two, three, four, five, six, seven, eight
FROM #temp A
WHERE A.four = (SELECT MAX(B.four)
FROM TABLE B
WHERE B.one = A.two
AND B.two = A.two
AND B.three = A.three);my policy is, i don't answer what is obviously a homework problem unless you have made an attempt yourself, and in this case, it looks like you have

either i don't understand the problem, or else it's a lot simpler than you thinkselect one, two, three, max(four)
from yourtable
group by one, two, threeGROUP BY always yields distinct combinations

a good homework assignment makes you stop and think, doesn't it

"distinct" does not imply you have to use the DISTINCT keyword

rudy
http://r937.com/|||believe it or not, but this isn't a homework question. I just simplified the table and column names so that it would be easier to understand.

Thanks for your solution though.

But i need to be able to get entries from almost all 8 columns (ie. other columns which do not have to be distinct).|||Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()|||Originally posted by DoktorBlue
Specify, what your 4 other columns must contain. You may choose from some accumulation functions like Max(), min(), avg(), sum()

that's the thing, i don't want to use an aggregate function on those other columns.

The database has numerous entries for each unique combination of columns one, two and three. And I want the entire row for the entry that has the max value at column 4.

thanks for the help though.|||I want the entire row for the entry that has the max value at column 4. you shoulda said it like that in the first place ;)
select one, two, three, four, five, six, seven, eight
from yourtable zz
where four =
( select max(four)
from yourtable
where one=zz.one
and two=zz.two
and three=zz.three )rudy|||Originally posted by r937
you shoulda said it like that in the first place ;)
select one, two, three, four, five, six, seven, eight
from yourtable zz
where four =
( select max(four)
from yourtable
where one=zz.one
and two=zz.two
and three=zz.three )rudy

haha..
i think i did.

But i think I got it..check it out:

SELECT
one AS one,
two AS two,
three AS three,
max(four) AS four
INTO #temp
FROM TABLE1 A
GROUP BY one, two, three;

SELECT
one AS one,
two AS two,
three AS three,
four AS four,
five AS five,
six AS six,
seven AS seven,
eight AS eight
INTO ANSWERTABLE
FROM TABLE1 A INNER JOIN #temp B
ON (A.one = B.one
AND A.two = B.two
AND A.three = B.three
AND A.four = B.four);

I'm checking the results...and it looks good so far.

See any bugs or loopholes ?

Thanks again.|||bugs or loopholes? you mean, other than using a temp table where you don't really need one?

did you try my correlated subquery?

rudy|||Originally posted by r937
bugs or loopholes? you mean, other than using a temp table where you don't really need one?

did you try my correlated subquery?

rudy

ya thanks...
got the same results (y)

but for some reason i can only get it to work if the "yourtable zz" is a temp table.
If i directly access the "yourtable" i get the following errors:

Server: Msg 8180, Level 16, State 1, Line 38
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.|||did you remember the correlation variable zz?

i'd have to see your query, but i bet it's a coding error

correlated subqueries do work in sql server

rudy|||Originally posted by r937
did you remember the correlation variable zz?

i'd have to see your query, but i bet it's a coding error

correlated subqueries do work in sql server

rudy

ya i named the table...
it's funny, cause it's still a correlated query if I substitute the temp table for the outer query table.|||i cannot help you if you do not show me your query

rudy|||Originally posted by r937
i cannot help you if you do not show me your query

rudy
ya sorry...
i'm legally bound not to give out any code..
i know it's anal, and doesn't really matter and the code's not important or anything, but i still can't.

But it's exactly like how u posted (i'm pretty sure i can follow that, haha)
'cept that the 'yourtable' is a view.

I ran the code in MS Access, and it was able to run. Except it did not include only one unique combinations of columns one two and three.
So i guess I wouldn't be able to use it even if I got it to go in SQLServer

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 Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999
SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:

>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

Find Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:

>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

Find Records with duplicate values in 1 row keyed to 2 other rows

My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999

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
>
>

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 & 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
>
>