This is what I have so far, but it doesnt work...
SELECT * FROM tblUsers
WHERE year(CreatedDate)=CASE
WHEN @.yr<>'0' THEN @.yr
ELSE NOT NULL
END
SELECT * FROM tblUsers
WHERE year(CreatedDate)=CASE WHEN (@.yr= 0) THEN year(CreatedDate) ELSE @.yr END
Showing posts with label selects. Show all posts
Showing posts with label selects. Show all posts
Thursday, March 29, 2012
case statement in where clause
I need a SQL statement that selects a specific year (@.yr type int) in the "createddate" column...if this @.yr is equal to 0 then I want to select ALL columns regardless of the year...
Tuesday, March 27, 2012
Case sensitivity of SQL selects
Hi all
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface )
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> > Hi all,
> >
> > Can anybody tell me how to give a case sensitive select on a non case
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
> >
> > Thanks
> > Roshan
>sql
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface )
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> > Hi all,
> >
> > Can anybody tell me how to give a case sensitive select on a non case
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
> >
> > Thanks
> > Roshan
>sql
Case sensitivity of SQL selects
Hi all,
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface ) ?
Thanks
Roshan
Assuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan
|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>
Can anybody tell me how to give a case sensitive select on a non case sensitive instance of sql server .Is there any setting to be set up ( Which needs to be done programtically not through the interface ) ?
Thanks
Roshan
Assuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan
|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>
Case sensitivity of SQL selects
Hi all,
Can anybody tell me how to give a case sensitive select on a non case sensit
ive instance of sql server .Is there any setting to be set up ( Which needs
to be done programtically not through the interface ) ?
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>
Can anybody tell me how to give a case sensitive select on a non case sensit
ive instance of sql server .Is there any setting to be set up ( Which needs
to be done programtically not through the interface ) ?
Thanks
RoshanAssuming SQL 2000, you can specify a case-sensitive collation for a
case-sensitive query. For example:
USE Northwind
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
You can also add the case-insensitive condition so that indexes can be used
efficiently.
SELECT *
FROM Customers
WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'alfki'
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
AND CustomerID = 'ALFKI'
Hope this helps.
Dan Guzman
SQL Server MVP
"Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> Hi all,
> Can anybody tell me how to give a case sensitive select on a non case
sensitive instance of sql server .Is there any setting to be set up ( Which
needs to be done programtically not through the interface ) ?
> Thanks
> Roshan|||In addition Dan's post
This should work on SQL 7 too
create table ABCD
(
courceid smallint not null,
description varchar(20) null
)
insert into ABCD(courceid,description)values (1,'DFh2AcZ')
insert into ABCD(courceid,description)values (2,'dHZ3')
)
SELECT description FROM ABCD where charindex(cast('H' as
varbinary(20)),cast(description as varbinary(20)))> 0
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:u2gDRbBKEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming SQL 2000, you can specify a case-sensitive collation for a
> case-sensitive query. For example:
> USE Northwind
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> You can also add the case-insensitive condition so that indexes can be
used
> efficiently.
> SELECT *
> FROM Customers
> WHERE CustomerID = 'alfki' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'alfki'
> SELECT *
> FROM Customers
> WHERE CustomerID = 'ALFKI' COLLATE SQL_Latin1_General_CP850_BIN
> AND CustomerID = 'ALFKI'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Roshan Jayalath" <anonymous@.discussions.microsoft.com> wrote in message
> news:648CCD5F-2615-4730-B735-3AED0EEEB4CB@.microsoft.com...
> sensitive instance of sql server .Is there any setting to be set up (
Which
> needs to be done programtically not through the interface ) ?
>
Sunday, March 25, 2012
Case sensitive problem
Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BBYou simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause can
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
--
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
--
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||On 14 Jun 2006 02:08:55 -0700, bad_boyu wrote:
>Thanks a lot for these replies!
>But I have another problem, I need that the method LIKE or something
>similar to be case-sensitive! The Function column has fields like this:
>"Dr Bad Boy", "DR Angelina",
>"Prof dr Italian", "Prof Dr Adrian",...
>Is there any solution for this kind of problem?
Hi BB,
SELECT *
FROM Function
WHERE Function LIKE '%Dr%' COLLATE LATIN1_General_CS_AS
--
Hugo Kornelis, SQL Server MVP|||See if this helps:
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||Thanks for these quick replies!!! I believe it works ;)
You are the best!
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BBYou simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause can
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
--
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
--
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
--
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||On 14 Jun 2006 02:08:55 -0700, bad_boyu wrote:
>Thanks a lot for these replies!
>But I have another problem, I need that the method LIKE or something
>similar to be case-sensitive! The Function column has fields like this:
>"Dr Bad Boy", "DR Angelina",
>"Prof dr Italian", "Prof Dr Adrian",...
>Is there any solution for this kind of problem?
Hi BB,
SELECT *
FROM Function
WHERE Function LIKE '%Dr%' COLLATE LATIN1_General_CS_AS
--
Hugo Kornelis, SQL Server MVP|||See if this helps:
http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||Thanks for these quick replies!!! I believe it works ;)
You are the best!
Sunday, March 11, 2012
cascading parameters
Hi,
I have a listbox which selects distinct brands from the products table.
Then another list box which lists all the orders with order description. Each order has a unique system generated ORDERID and the user provides the orderdescription which could be duplicate.
Depending on the Brand selected by the user in the earlier list box, only those orders containing the the brands in order details files should be available for selection in the list box.
e.g. BRands Lisbox shows: Cream A, Cream B and Cream C
If the user selects 'Cream A', then the next list box shows orderdescription as 'Cream A order for regular sale','Cream A order for exhinition sale', 'Cream A order for exhibition sale'
The problem here is that if the user selects a description which is duplicate (could be the case), then the system brings back the wrong order details.
How is it possible to allow a user to select a order description but search on the OrderID?
Thanks for the help
regards
josh
Hi,
this should work by simply defining two parameter queries whereas the orders query uses the parameter value of the brand parameter.
Regards, Alex
Saturday, February 25, 2012
capturing reportviewer parameters
Hi Guys
I have an application which consists of a navigation bar and a frame.
When a user selects a hyperlink on the navigation bar that particular
report is displayed in the frame. Now these reports contains links
through which the user can jump to some other report which is not
present in the navigation bar. Now when the user clicks any other link
on the navigation bar the new report is starting up using the default
parameters set for that report. But i dont want this to happen. The
report viewer should take the latest parameters set in the report
viewer and pass them to the new report that will be generated when the
user clicks on the navigation bar. Can any body help with this aspect.
it is blowing up my brains. dont even know that whether this is
possible.
Also can we somehow get the url of the report being displayed in the
report viewer." i know that report viewer is an iframe which uses url
access beneath it to access the reports." Only the first report server
url is encoded in the application. But when the user navigated to some
other report through the links in reports how do i get access to that
particular report url .
any help will really be great. I am wondering is it really possible to
do the stuff i just mentioned above. Any tips on this will really save
me a lot of time.
Thanks in advance.../......
Passxunlimitedyou have to add some code to save the parameters and reuse them when you
open a new report.
you have to intercept some events (from the reportviewer control) to
retrieve the parameters when the first report is refreshed with new
parameters, save the values into a the session, in the page load event if
you open a new report change the values of the parameters of these reports
regarding what you have saved before.
its not complicated, the API is easy to use to do this.
"Passx" <passxunlimited@.gmail.com> wrote in message
news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
> Hi Guys
> I have an application which consists of a navigation bar and a frame.
> When a user selects a hyperlink on the navigation bar that particular
> report is displayed in the frame. Now these reports contains links
> through which the user can jump to some other report which is not
> present in the navigation bar. Now when the user clicks any other link
> on the navigation bar the new report is starting up using the default
> parameters set for that report. But i dont want this to happen. The
> report viewer should take the latest parameters set in the report
> viewer and pass them to the new report that will be generated when the
> user clicks on the navigation bar. Can any body help with this aspect.
> it is blowing up my brains. dont even know that whether this is
> possible.
> Also can we somehow get the url of the report being displayed in the
> report viewer." i know that report viewer is an iframe which uses url
> access beneath it to access the reports." Only the first report server
> url is encoded in the application. But when the user navigated to some
> other report through the links in reports how do i get access to that
> particular report url .
> any help will really be great. I am wondering is it really possible to
> do the stuff i just mentioned above. Any tips on this will really save
> me a lot of time.
> Thanks in advance.../......
> Passxunlimited
>|||i was trying to do exactly the same thing . But could not find any
events associated with report viewer wherein i can catch the parameter
values or session state. Any idea or resources regarding this.
thanks
passx
Jeje wrote:
> you have to add some code to save the parameters and reuse them when you
> open a new report.
> you have to intercept some events (from the reportviewer control) to
> retrieve the parameters when the first report is refreshed with new
> parameters, save the values into a the session, in the page load event if
> you open a new report change the values of the parameters of these reports
> regarding what you have saved before.
> its not complicated, the API is easy to use to do this.
>
> "Passx" <passxunlimited@.gmail.com> wrote in message
> news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
> > Hi Guys
> >
> > I have an application which consists of a navigation bar and a frame.
> > When a user selects a hyperlink on the navigation bar that particular
> > report is displayed in the frame. Now these reports contains links
> > through which the user can jump to some other report which is not
> > present in the navigation bar. Now when the user clicks any other link
> > on the navigation bar the new report is starting up using the default
> > parameters set for that report. But i dont want this to happen. The
> > report viewer should take the latest parameters set in the report
> > viewer and pass them to the new report that will be generated when the
> > user clicks on the navigation bar. Can any body help with this aspect.
> > it is blowing up my brains. dont even know that whether this is
> > possible.
> >
> > Also can we somehow get the url of the report being displayed in the
> > report viewer." i know that report viewer is an iframe which uses url
> > access beneath it to access the reports." Only the first report server
> > url is encoded in the application. But when the user navigated to some
> > other report through the links in reports how do i get access to that
> > particular report url .
> >
> > any help will really be great. I am wondering is it really possible to
> > do the stuff i just mentioned above. Any tips on this will really save
> > me a lot of time.
> >
> > Thanks in advance.../......
> > Passxunlimited
> >|||try the onunload event or any event after the rendering step.
"Passx" <passxunlimited@.gmail.com> wrote in message
news:1167942475.840285.10930@.51g2000cwl.googlegroups.com...
>i was trying to do exactly the same thing . But could not find any
> events associated with report viewer wherein i can catch the parameter
> values or session state. Any idea or resources regarding this.
>
> thanks
> passx
> Jeje wrote:
>> you have to add some code to save the parameters and reuse them when you
>> open a new report.
>> you have to intercept some events (from the reportviewer control) to
>> retrieve the parameters when the first report is refreshed with new
>> parameters, save the values into a the session, in the page load event if
>> you open a new report change the values of the parameters of these
>> reports
>> regarding what you have saved before.
>> its not complicated, the API is easy to use to do this.
>>
>> "Passx" <passxunlimited@.gmail.com> wrote in message
>> news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
>> > Hi Guys
>> >
>> > I have an application which consists of a navigation bar and a frame.
>> > When a user selects a hyperlink on the navigation bar that particular
>> > report is displayed in the frame. Now these reports contains links
>> > through which the user can jump to some other report which is not
>> > present in the navigation bar. Now when the user clicks any other link
>> > on the navigation bar the new report is starting up using the default
>> > parameters set for that report. But i dont want this to happen. The
>> > report viewer should take the latest parameters set in the report
>> > viewer and pass them to the new report that will be generated when the
>> > user clicks on the navigation bar. Can any body help with this aspect.
>> > it is blowing up my brains. dont even know that whether this is
>> > possible.
>> >
>> > Also can we somehow get the url of the report being displayed in the
>> > report viewer." i know that report viewer is an iframe which uses url
>> > access beneath it to access the reports." Only the first report server
>> > url is encoded in the application. But when the user navigated to some
>> > other report through the links in reports how do i get access to that
>> > particular report url .
>> >
>> > any help will really be great. I am wondering is it really possible to
>> > do the stuff i just mentioned above. Any tips on this will really save
>> > me a lot of time.
>> >
>> > Thanks in advance.../......
>> > Passxunlimited
>> >
>
I have an application which consists of a navigation bar and a frame.
When a user selects a hyperlink on the navigation bar that particular
report is displayed in the frame. Now these reports contains links
through which the user can jump to some other report which is not
present in the navigation bar. Now when the user clicks any other link
on the navigation bar the new report is starting up using the default
parameters set for that report. But i dont want this to happen. The
report viewer should take the latest parameters set in the report
viewer and pass them to the new report that will be generated when the
user clicks on the navigation bar. Can any body help with this aspect.
it is blowing up my brains. dont even know that whether this is
possible.
Also can we somehow get the url of the report being displayed in the
report viewer." i know that report viewer is an iframe which uses url
access beneath it to access the reports." Only the first report server
url is encoded in the application. But when the user navigated to some
other report through the links in reports how do i get access to that
particular report url .
any help will really be great. I am wondering is it really possible to
do the stuff i just mentioned above. Any tips on this will really save
me a lot of time.
Thanks in advance.../......
Passxunlimitedyou have to add some code to save the parameters and reuse them when you
open a new report.
you have to intercept some events (from the reportviewer control) to
retrieve the parameters when the first report is refreshed with new
parameters, save the values into a the session, in the page load event if
you open a new report change the values of the parameters of these reports
regarding what you have saved before.
its not complicated, the API is easy to use to do this.
"Passx" <passxunlimited@.gmail.com> wrote in message
news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
> Hi Guys
> I have an application which consists of a navigation bar and a frame.
> When a user selects a hyperlink on the navigation bar that particular
> report is displayed in the frame. Now these reports contains links
> through which the user can jump to some other report which is not
> present in the navigation bar. Now when the user clicks any other link
> on the navigation bar the new report is starting up using the default
> parameters set for that report. But i dont want this to happen. The
> report viewer should take the latest parameters set in the report
> viewer and pass them to the new report that will be generated when the
> user clicks on the navigation bar. Can any body help with this aspect.
> it is blowing up my brains. dont even know that whether this is
> possible.
> Also can we somehow get the url of the report being displayed in the
> report viewer." i know that report viewer is an iframe which uses url
> access beneath it to access the reports." Only the first report server
> url is encoded in the application. But when the user navigated to some
> other report through the links in reports how do i get access to that
> particular report url .
> any help will really be great. I am wondering is it really possible to
> do the stuff i just mentioned above. Any tips on this will really save
> me a lot of time.
> Thanks in advance.../......
> Passxunlimited
>|||i was trying to do exactly the same thing . But could not find any
events associated with report viewer wherein i can catch the parameter
values or session state. Any idea or resources regarding this.
thanks
passx
Jeje wrote:
> you have to add some code to save the parameters and reuse them when you
> open a new report.
> you have to intercept some events (from the reportviewer control) to
> retrieve the parameters when the first report is refreshed with new
> parameters, save the values into a the session, in the page load event if
> you open a new report change the values of the parameters of these reports
> regarding what you have saved before.
> its not complicated, the API is easy to use to do this.
>
> "Passx" <passxunlimited@.gmail.com> wrote in message
> news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
> > Hi Guys
> >
> > I have an application which consists of a navigation bar and a frame.
> > When a user selects a hyperlink on the navigation bar that particular
> > report is displayed in the frame. Now these reports contains links
> > through which the user can jump to some other report which is not
> > present in the navigation bar. Now when the user clicks any other link
> > on the navigation bar the new report is starting up using the default
> > parameters set for that report. But i dont want this to happen. The
> > report viewer should take the latest parameters set in the report
> > viewer and pass them to the new report that will be generated when the
> > user clicks on the navigation bar. Can any body help with this aspect.
> > it is blowing up my brains. dont even know that whether this is
> > possible.
> >
> > Also can we somehow get the url of the report being displayed in the
> > report viewer." i know that report viewer is an iframe which uses url
> > access beneath it to access the reports." Only the first report server
> > url is encoded in the application. But when the user navigated to some
> > other report through the links in reports how do i get access to that
> > particular report url .
> >
> > any help will really be great. I am wondering is it really possible to
> > do the stuff i just mentioned above. Any tips on this will really save
> > me a lot of time.
> >
> > Thanks in advance.../......
> > Passxunlimited
> >|||try the onunload event or any event after the rendering step.
"Passx" <passxunlimited@.gmail.com> wrote in message
news:1167942475.840285.10930@.51g2000cwl.googlegroups.com...
>i was trying to do exactly the same thing . But could not find any
> events associated with report viewer wherein i can catch the parameter
> values or session state. Any idea or resources regarding this.
>
> thanks
> passx
> Jeje wrote:
>> you have to add some code to save the parameters and reuse them when you
>> open a new report.
>> you have to intercept some events (from the reportviewer control) to
>> retrieve the parameters when the first report is refreshed with new
>> parameters, save the values into a the session, in the page load event if
>> you open a new report change the values of the parameters of these
>> reports
>> regarding what you have saved before.
>> its not complicated, the API is easy to use to do this.
>>
>> "Passx" <passxunlimited@.gmail.com> wrote in message
>> news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
>> > Hi Guys
>> >
>> > I have an application which consists of a navigation bar and a frame.
>> > When a user selects a hyperlink on the navigation bar that particular
>> > report is displayed in the frame. Now these reports contains links
>> > through which the user can jump to some other report which is not
>> > present in the navigation bar. Now when the user clicks any other link
>> > on the navigation bar the new report is starting up using the default
>> > parameters set for that report. But i dont want this to happen. The
>> > report viewer should take the latest parameters set in the report
>> > viewer and pass them to the new report that will be generated when the
>> > user clicks on the navigation bar. Can any body help with this aspect.
>> > it is blowing up my brains. dont even know that whether this is
>> > possible.
>> >
>> > Also can we somehow get the url of the report being displayed in the
>> > report viewer." i know that report viewer is an iframe which uses url
>> > access beneath it to access the reports." Only the first report server
>> > url is encoded in the application. But when the user navigated to some
>> > other report through the links in reports how do i get access to that
>> > particular report url .
>> >
>> > any help will really be great. I am wondering is it really possible to
>> > do the stuff i just mentioned above. Any tips on this will really save
>> > me a lot of time.
>> >
>> > Thanks in advance.../......
>> > Passxunlimited
>> >
>
Subscribe to:
Posts (Atom)