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 Containers and Variables from Script task?

Hi,
* Here's my use case:
I have 20 odd event handlers in various containers that all do the exact same thing. Even though they're simple (a Script and an Execute SQL Task), its a right pain to copy them & keep them synced. I could probably create a custom task for the content of each handler, but I would still need to copy & configure it across 20 event handlers.
My (maybe impossible) idea to handle this was to:
Create a single event handler at the global scope. When invoked, use System::SourceID or System::SourceName to either
- GetContainer(SourceID).Variables("myVar") (had there been such a function call)
- Traverse the package object model to find the container, i.e:
Package.Executables(SourceName).Variables("myVar")
Is it true that there is no practical way of doing this?

* "Variables on the container ... are visible to the event handler that handles the event on that container"
As I understand it, this is only true when attaching an event handler to the specific container generating the event. If a container has no event handler, the event will propagate up through the hierarchy all the way to the package scope if needed. As soon as the event has propagated even once, the invoked event handler no longer has access to the local variables of the source container. Is this a reasonably correct description?
Event handlers and propagation would be _strikingly_ more useful if the handler had access to the source container environment that created the event, or am I missing something obvious?

* As a second possibility, can I create a _custom task_ (as opposed to a script) that _can_ traverse the object model of a package created in the designer (i.e. I'm not creating the whole package in my own code)?

If you lasted this long, thanks!-)
KI think I answered this on the NG, but the variables on the parent of the eventhandler that raised the event are visible to the event handler.
You can also create a package that handle the events and uses parent package configurations to pass in the values for the events. Then use an execute package task in all your event handlers that references that shared package.
Tasks cannot traverse the object model. They are prohibited and variables will refuse to hold a reference to IS object model objects with an error.|||Although I did implement using those 20 event handlers, I've now ditched that in favour of parsing the needed info out of sysdtslog90, which does away with the need for all those event handlers. Always a good feeling when stripping out half the 'code' but none of the functionality
Very useful to understand how & when to use the event handlers though, thanks!
K

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:

> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:
> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>

find any character in a string that is a lower case

in SQL Server 7, How can I find out if a database field has any lower case
character in the string. Thanks!
you can create a temp table w/ the letters of the alphabet in it and
search that field IN #temp table.
or you could find a length of the string and then substring letter by
letter and search the ASCII values for each lowercase letter against
the substring letter.
|||Thanks! What I did is to find the sum of the ASCII values for the whole
string and the sum of ASCIIs for upper(string) at the same time. If they are
not equal, there is a lower case character.
"GlennThomas5" wrote:

> you can create a temp table w/ the letters of the alphabet in it and
> search that field IN #temp table.
> or you could find a length of the string and then substring letter by
> letter and search the ASCII values for each lowercase letter against
> the substring letter.
>