Case Statement(Help Please)

I am trying to use a case statement to pull bad phone numbers out of my
company table into a readable format. I am using SQL Server 2000 SP4.
The phone numbers, in my inherited database, are in any number of formats,
not complete, etc, and I need to pull a report showing only the good phone
numbers.
My Case statement returns the error below the statement.
-- SELECT SUBSTRING((RTRIM(phone) + ' '+
RTRIM(phone) + ' '), 1, 25) AS Name, phone,
Phone =
CASE
WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
END
FROM company
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '+(' to a column of data type int.
RayRay wrote on Fri, 11 Nov 2005 07:05:07 -0800:

> I am trying to use a case statement to pull bad phone numbers out of my
> company table into a readable format. I am using SQL Server 2000 SP4.
> The phone numbers, in my inherited database, are in any number of formats,
> not complete, etc, and I need to pull a report showing only the good phone
> numbers.
> My Case statement returns the error below the statement.
> -- SELECT SUBSTRING((RTRIM(phone) + ' '+
> RTRIM(phone) + ' '), 1, 25) AS Name, phone,
> Phone =
> CASE
> WHEN SUBSTRING(phone, 1, 2) > len(10) THEN 'Phone'
> WHEN SUBSTRING(phone, 1, 2) < len(10) THEN 'Phone'
> END
> FROM company
> Server: Msg 245, Level 16, State 1, Line 1
> Syntax error converting the varchar value '+(' to a column of data type
> int.
SUBSTRING(phone, 1, 2) > len(10) is the problem
This takes the first 2 characters of the phone column, and compares it to
the length of the string '10' (implicit conversion of the LEN expression to
a string). The first 2 characters are '(+', and due to implicit conversion
will try to convert that to an integer to compare to the value 2.. What
exactly are you trying to achieve here? It makes no sense.
Dan|||I am trying to pull good phone numbers from a list of bad numbers.
For Example:+ () -
+ () -3681
+ () -8544
+ () ?
+ () ?
+ () 0-+00-00
+ () 0-00
+ () 0-00
+ () 0-00--00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-00
+ () 0-00-000
+ () 00-000
+ () 011-1-4756-6900
+ () 011-234-1266-89
+ () 011-2711-320-50
+ () 011-2711-377-38
+ () 011-322-545-252
+ () 011-331-4323-20
+ () 011-358-013360
+ () 011-418-643-307
+ () 011-44-002-7551
+ () 011-45-4468-446
+ () 011-468-719-500
+ () 011-495-254-991
+ () 011-603-707-449
+ () 011-612-4655-24
I inhereted this database from someone who just put stuff in anyway they
wanted. Now I need to pull out the ones formatted corrected so they can be
inputted into a new system.
Ray
"Daniel Crichton" wrote:

> Ray wrote on Fri, 11 Nov 2005 07:05:07 -0800:
>
> SUBSTRING(phone, 1, 2) > len(10) is the problem
> This takes the first 2 characters of the phone column, and compares it to
> the length of the string '10' (implicit conversion of the LEN expression t
o
> a string). The first 2 characters are '(+', and due to implicit conversion
> will try to convert that to an integer to compare to the value 2.. What
> exactly are you trying to achieve here? It makes no sense.
> Dan
>
>|||Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:

> I am trying to pull good phone numbers from a list of bad numbers.
Sorry, I guess I wasn't being clear enough. From the SELECT statement you
provided I can't figure out what you're trying to do. I can only guess that
you're looking for strings of at least 10 characters after the +( at the
start, but as to what you want returned it makes no sense - why would you
display the first 25 characters of the phone number concatentated to itself
as the name column, and then show the phone number again as phone, and then
appear to just put the string 'Phone' into another column called Phone? A
long list of numbers doesn't explain anything. What is a well formatted
phone number? None of those I would consider well formatted, but then again
I'm a UK resident and we have a different number format here.
Dan|||Daniel Crichton wrote:
> Ray wrote on Fri, 11 Nov 2005 07:39:16 -0800:
>
> Sorry, I guess I wasn't being clear enough. From the SELECT statement
> you provided I can't figure out what you're trying to do. I can only
> guess that you're looking for strings of at least 10 characters after
> the +( at the start, but as to what you want returned it makes no
> sense - why would you display the first 25 characters of the phone
> number concatentated to itself as the name column, and then show the
> phone number again as phone, and then appear to just put the string
> 'Phone' into another column called Phone? A long list of numbers
> doesn't explain anything. What is a well formatted phone number? None
> of those I would consider well formatted, but then again I'm a UK
> resident and we have a different number format here.
Another idea thrown into the mix: write a user defined function that
either maps a given phone number to a valid one or maps phone numbers to
something like "ok", "maybe", "crap". Then you can easily select
your_function(phone) = 'ok'.
Kind regards
robert

Case Statement Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,case
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
JamieWell let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
--
Regards,
Jamie
"Mike C#" wrote:
> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expression?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
> >I have the case statement below and when the processing runs (set
> > transname=(case stmt...) or insert into table (col1,col2...) select
> > col1,case
> > stmt from anothertable, the processing from within the part of the case
> > statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> > not
> > process the statements inside the case within the case. I suspect I have
> > committed an error but I can't pin it down. Help appreciated.
> >
> > CASE ISNULL(trans_id,0)
> > WHEN 5 THEN
> > CASE Upper(tloc_id)
> > WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> > WHEN 'FIN-INITG' THEN 'To Burlington'
> > WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> > Location' ELSE 'Move To Location' END -- move it in
> > WHEN 'REWORK' THEN 'Rework'
> > ELSE 'Move To Location'
> > END
> > WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> > 'Undefined' END
> > WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> > 'Undefined' END
> > WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> > WHEN 101 THEN 'Shipped'
> > WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> > 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> > Upper(tloc_id)='OF-OUT'
> > THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> > ELSE 'Undefined'
> > END
> >
> > Regards,
> > Jamie
>
>

Case Statement Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,case
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id)IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
Jamie
Well let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie
|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
Regards,
Jamie
"Mike C#" wrote:

> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expression?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>
>

Case Statement Woes

I have the case statement below and when the processing runs (set
transname=(case stmt...) or insert into table (col1,col2...) select col1,cas
e
stmt from anothertable, the processing from within the part of the case
statement that is below the "when isnull(trans_id,0)=1105" runs, it does not
process the statements inside the case within the case. I suspect I have
committed an error but I can't pin it down. Help appreciated.
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
Location' ELSE 'Move To Location' END -- move it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END
WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
'Undefined' END
WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
'Undefined' END
WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND Upper(tloc_id)='OF-OUT'
THEN 'Finished (END of Roll)' ELSE 'Undefined' END
ELSE 'Undefined'
END
Regards,
JamieWell let's indent your CASE "expression" to make it a little easier to
troubleshoot:
CASE ISNULL(trans_id,0)
WHEN 5 THEN
CASE Upper(tloc_id)
WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
WHEN 'FIN-INITG' THEN 'To Burlington'
WHEN 'FIN-IN' THEN
CASE
WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Location'
ELSE 'Move To Location'
END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- move
it in
WHEN 'REWORK' THEN 'Rework'
ELSE 'Move To Location'
END /* CASE Upper(tloc_id) */
WHEN 20 THEN
CASE
WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) like 'RC%' */
WHEN 25 THEN
CASE
WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
ELSE 'Undefined'
END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
WHEN 26 THEN
CASE STATUS
WHEN 0 THEN 'Available'
ELSE 'Hold'
END /* CASE STATUS */
WHEN 101 THEN 'Shipped'
WHEN 1105 THEN
CASE
WHEN Upper(floc_id)='OF-OUT'
AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
WHEN floc_id IS NULL /* Upper() not necessary */
AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
ELSE 'Undefined-2' /* Used to be Undefined */
END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
ELSE 'Undefined'
END /* CASE ISNULL(trans_id,0) */
Are you sure it's not processing the expressions inside the case expression?
I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Look
at the expressions in that CASE expression and see if they can be True at
any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
drop through to the ELSE. If those criteria aren't exactly matched, then
you can expect it to drop through to the ELSE.
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>I have the case statement below and when the processing runs (set
> transname=(case stmt...) or insert into table (col1,col2...) select
> col1,case
> stmt from anothertable, the processing from within the part of the case
> statement that is below the "when isnull(trans_id,0)=1105" runs, it does
> not
> process the statements inside the case within the case. I suspect I have
> committed an error but I can't pin it down. Help appreciated.
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN CASE WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To
> Location' ELSE 'Move To Location' END -- move it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END
> WHEN 20 THEN CASE WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving' ELSE
> 'Undefined' END
> WHEN 25 THEN CASE WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process' ELSE
> 'Undefined' END
> WHEN 26 THEN CASE STATUS WHEN 0 THEN 'Available' ELSE 'Hold' END
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN CASE WHEN Upper(floc_id)='OF-OUT' AND tLoc_ID is null THEN
> 'Finished (Unbatch)' WHEN Upper(floc_id) IS NULL AND
> Upper(tloc_id)='OF-OUT'
> THEN 'Finished (END of Roll)' ELSE 'Undefined' END
> ELSE 'Undefined'
> END
> Regards,
> Jamie|||You're right about it getting through the case statement with no problem.
Looks like I am looking in the wrong place. I appreciate the help. I think
I can forget about it from this end of the problem. The other records with
nulls show up just fine as 'Undefined-2'
Thanks Mike!
--
Regards,
Jamie
"Mike C#" wrote:

> Well let's indent your CASE "expression" to make it a little easier to
> troubleshoot:
> CASE ISNULL(trans_id,0)
> WHEN 5 THEN
> CASE Upper(tloc_id)
> WHEN 'OF-OUT' THEN 'Finished (Unbatch)'
> WHEN 'FIN-INITG' THEN 'To Burlington'
> WHEN 'FIN-IN' THEN
> CASE
> WHEN Upper(floc_id)= 'FIN-INITG' THEN 'Move To Locatio
n'
> ELSE 'Move To Location'
> END /* CASE WHEN Upper(floc_id)='FIN-INITG' */ -- mov
e
> it in
> WHEN 'REWORK' THEN 'Rework'
> ELSE 'Move To Location'
> END /* CASE Upper(tloc_id) */
> WHEN 20 THEN
> CASE
> WHEN Upper(tloc_id) like 'RC%' THEN 'Receiving'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) like 'RC%' */
> WHEN 25 THEN
> CASE
> WHEN Upper(tloc_id) = 'BTCH-IN' THEN 'In Process'
> ELSE 'Undefined'
> END /* CASE WHEN Upper(tloc_id) = 'BTCH-IN' */
> WHEN 26 THEN
> CASE STATUS
> WHEN 0 THEN 'Available'
> ELSE 'Hold'
> END /* CASE STATUS */
> WHEN 101 THEN 'Shipped'
> WHEN 1105 THEN
> CASE
> WHEN Upper(floc_id)='OF-OUT'
> AND tLoc_ID IS NULL THEN 'Finished (Unbatch)'
> WHEN floc_id IS NULL /* Upper() not necessary */
> AND Upper(tloc_id)='OF-OUT' THEN 'Finished (END of Roll)'
> ELSE 'Undefined-2' /* Used to be Undefined */
> END /* CASE WHEN Upper(floc_id) = 'OF-OUT' */
> ELSE 'Undefined'
> END /* CASE ISNULL(trans_id,0) */
> Are you sure it's not processing the expressions inside the case expressio
n?
> I changed the 'Undefined' there to 'Undefined-2' to find out for sure. Lo
ok
> at the expressions in that CASE expression and see if they can be True at
> any point; for instance, if tLoc_ID is NULL and floc_id is NULL, it will
> drop through to the ELSE. If those criteria aren't exactly matched, then
> you can expect it to drop through to the ELSE.
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:0D68A7EB-5944-4EFC-8B93-44E624FEC6DD@.microsoft.com...
>
>

Case Statement Using Table Alias, Not Possible ...?

Hello, how come I can't qualify a column with its table alias in a
case clause
this works:
select tasks.taskid,tasks.status,machinename=
case
when machinename is null ( select machine from queue where
taskid = tasks.taskid)
else machinename
end
from tasks
but when I change all machinename references to tasks.machinename
i get syntax errors:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'else'
For this simple query, ambiguous columns are not an issue, but when
they become one, how is one to use a case statement?"MaggotChild" <hsomob1999@.yahoo.com> wrote in message
news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...
> Hello, how come I can't qualify a column with its table alias in a
> case clause
> this works:
> select tasks.taskid,tasks.status,machinename=
> case
> when machinename is null ( select machine from queue where
> taskid = tasks.taskid)
> else machinename
> end
> from tasks
> but when I change all machinename references to tasks.machinename
> i get syntax errors:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'else'
>
> For this simple query, ambiguous columns are not an issue, but when
> they become one, how is one to use a case statement?
>
You missed out the keyword "THEN" in your CASE expression.
Couldn't you do this with a JOIN? Try the following:
SELECT T.taskid, T.status,
COALESCE(T.machinename, Q.machine) AS machinename
FROM tasks AS T
LEFT JOIN queue AS Q
ON T.taskid = Q.taskid ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Oh, yeah the then... at any rate, then 'then' wont fix the error.
I need a case because the query in question is part of a somewhat big
view that is now required to provide some additional "functionality".
Getting the case to work would be easier than rewriting it... well, if
it can work...
Any ideas as to why the qualifier causes it to fail?
Thanks,
Skye|||As David Portas pointed out, you need a THEN. But to avoid your error,
don't add the tasks. prefix to the machinename in the first line just before
the = sign. That is the name of the column in your result set, not the name
of the column in the tasks table. So you want:
select tasks.taskid,tasks.status,machinename=
case
when tasks.machinename is null Then ( select machine from queue where
taskid = tasks.taskid)
else tasks.machinename
end
from tasks
If for some reason, you wanted the name of the colum in the result set to be
tasks.machinename (and you almost certainly don't want that), you would have
to enclose it in single quotes, e.g.,
select tasks.taskid,tasks.status,'tasks.machinename'=
case
when tasks.machinename is null Then ( select machine from queue where
taskid = tasks.taskid)
else tasks.machinename
end
from tasks
Tom
"MaggotChild" <hsomob1999@.yahoo.com> wrote in message
news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...
> Hello, how come I can't qualify a column with its table alias in a
> case clause
> this works:
> select tasks.taskid,tasks.status,machinename=
> case
> when machinename is null ( select machine from queue where
> taskid = tasks.taskid)
> else machinename
> end
> from tasks
> but when I change all machinename references to tasks.machinename
> i get syntax errors:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '='
> Server: Msg 156, Level 15, State 1, Line 4
> Incorrect syntax near the keyword 'else'
>
> For this simple query, ambiguous columns are not an issue, but when
> they become one, how is one to use a case statement?
>|||Tom Cooper wrote:
> As David Portas pointed out, you need a THEN. But to avoid your error,
> don't add the tasks. prefix to the machinename in the first line just befo
re
> the = sign. That is the name of the column in your result set, not the na
me
> of the column in the tasks table. So you want:
> select tasks.taskid,tasks.status,machinename=
> case
> when tasks.machinename is null Then ( select machine from queue whe
re
> taskid = tasks.taskid)
> else tasks.machinename
> end
> from tasks
ahh, ok....

> If for some reason, you wanted the name of the colum in the result set to
be
> tasks.machinename (and you almost certainly don't want that), you would ha
ve
> to enclose it in single quotes, e.g.,
> select tasks.taskid,tasks.status,'tasks.machinename'=
> case
> when tasks.machinename is null Then ( select machine from queue whe
re
> taskid = tasks.taskid)
> else tasks.machinename
> end
> from tasks
Thanks allot for your clarification, I actually just ended up aliasing
the case statement.
> Tom
> "MaggotChild" <hsomob1999@.yahoo.com> wrote in message
> news:1144100666.210621.60400@.u72g2000cwu.googlegroups.com...

case statement problem

Hi all

I am having a small problem with the case statement,
I have two table, a status table and users table ( i have scripted them below)

create table users
(id int
, user_name char (10) )

insert into users (id, user_name)
values ( 1, 'bob')
insert into users (id, user_name)
values ( 2, 'sue')
insert into users (id, user_name)
values ( 3, 'richard')
insert into users (id, user_name)
values ( 4, 'john')
insert into users (id, user_name)
values ( 5, 'wendy')

create table status
(name char (10)
, status int, sales_manager int, account_manager int)

insert into status (name, status, sales_manager)
values ('test1', 1, 1 )
insert into status (name, status, sales_manager)
values ('test2', 1, 2 )
insert into status (name, status, account_manager)
values ('test3', 2, 3 )
insert into status (name, status, account_manager)
values ('test4', 2, 4 )
insert into status (name, status)
values ('test5', 2 )

What i need to do when i run the below statement it gives me a list of the names
and the managers, if there is a null value returned i want it to display
'No manager assigned' or something like that

select s.name
, 'manager' = case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

thanks

Like this, use COALESCE or ISNULL

select status,s.name
, 'manager' = case
when status = 1 then coalesce(u1.user_name,'No manager assigned')
when status = 2 then coalesce(u2.user_name,'No manager assigned')
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can do below:

select s.name
, coalesce(case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end, 'No manager assigned') as manager
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Also, please don't use the 'column_alias' = expr syntax. This has been deprecated in SQL Server 2005 and will be removed in a future version of SQL Server. See link below for more details:

http://msdn2.microsoft.com/en-us/ms143729(SQL.90).aspx

|||Thanks guys for the answers, sorted!

Case statement

Hi

I am having a few problem with a case statement.

I have a table with customer data in and I need to bring back all the rows
which have data missing from 2 different columns.

customer table
CustomerID Name SoftwareVersion
1001 TEST1 V5
1002 TEST2 V5
3 TEST3 V5
004 TEST4 V6
ect..

CustomerID Table
ID
1001
1002
1003
1004
ect...

I have a need customerid table and I need to list all the customers who are
not in this table or whos id does not match the new listings,
alos there is a softwareVersion Table list all the newest software so the same
will need to be done for this.

Thanks

Rich

You need to create the join batween the two table with CustomerID & ID, with not equal operator, yuor query may look like this

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

Gurpreet S. Gill

|||

Hi

I would like to use the case statement because where there is an error, I would like the cell to display

ERROR: 'then customerid'

Thanks

Rich

|||

I dont get you, what you want to do ? tell me in more detail.

|||

SELECT A.CustomerID FROM customer A , customerID B

WHERE A.CustomerID != B.ID

This won't work, you're just going to get every Customer joined to every customer except themselves.

You need to use an outer join something like this

SELECT A.CustomerID
FROM Customer A
LEFT JOIN CustomerID B ON A.CustomerID = B.[ID]
WHERE B.ID IS NULL

|||I assume you mean that the CustomerID table in your example above is really the result of a query you want to write. The problem is you haven't specified what "all the customers who are not in this table or whos id does not match the new listings" means. How do you know if a customer is not in that table or if they do not match the new listings, is that info in another table?|||

yes SnMSDN

he didnt make much clear what he wants, that why i did post that sample code. But he not responding, might he had solved the problem

sql

Case statemant- separate forenames

Hi All

I need to separate the forenames within the staff table, at present it looks something like this:

John-Test
John - Test
John - Test
Richard
Bill Jones
John - Test MiddleName
John-Test Mid

I need to split the middlenames out and remove the spaces within the double barrelled names

John-Test
John-Test
John-Test
Richard
Bill
John-Test
John-Test

Thanks in advanced

Rich

hi...

there is a recent post solving a similar issue..have a look...

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

|||

It will do..

Create Table Names
(
FullName varchar(100)
)
Go

Insert Into Names values ('John-Test ')
Insert Into Names values ('John - Test')
Insert Into Names values ('John - Test')
Insert Into Names values ('Richard')
Insert Into Names values ('Bill Jones')
Insert Into Names values ('John - Test MiddleName')
Insert Into Names values ('John-Test Mid')

Go

--Simple / Direct Logic

Select
A Actual
,B + Substring(C,1,CharIndex(' ',C)) Result
From
(
Select
FullName A
,Replace(Substring(FullName,1,CharIndex('-',FullName)),' ','') B
,Ltrim(Substring(FullName,CharIndex('-',FullName)+1,len(FullName)-CharIndex('-',FullName))) + ' ' C
from
Names
) as Data

Go

--Creating Function
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProperName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetProperName]
Go

Create Function Dbo.GetProperName(@.Name as varchar(100)) Returns Varchar(100)
as
Begin
Declare @.B as Varchar(100);
Declare @.C as Varchar(100);
Declare @.Result as Varchar(100);

Select

@.B = Replace(Substring(@.Name,1,CharIndex('-',@.Name)),' ','')
,@.C = Ltrim(Substring(@.Name,CharIndex('-',@.Name)+1,len(@.Name)-CharIndex('-',@.Name))) + ' '

Select @.Result = @.B + Substring(@.C,1,CharIndex(' ',@.C))
return @.Result;
End

go
Select FullName, Dbo.GetProperName(fullname) from names
go
Drop Table Names

Case Sensitive?

Dear everyone,

I am doing Login webform (C# .NET web application) with SQL Server 2000.

The staff table is to store authenticated user info.

But when I test it, I found that the password can be case insensitive, i.e. 'A0001' should be correct password, but 'a0001' can allow login.

Could anyone tell me how to solve this problem??

Thanks you very much!!

private void btnLogin_Click(object sender, System.EventArgs e)
{
//instantiate SQL connection
SqlConnection sqlConnect = new SqlConnection(connectStg);
SqlCommand selectLogin = sqlConnect.CreateCommand();
selectLogin.CommandText = "SELECT sid, type from STAFF Where sid= '" + txtId.Text + "' and pwd= '" + txtPwd.Text + "' ";
//open connectin for execution
sqlConnect.Open();
//instantiate the SqlDataReader reader
SqlDataReader loginReader = selectLogin.ExecuteReader();
//try and catch SqlException error
try
{
if(loginReader.Read())
{
// check whether the user is the role of administrator or operator
// I use GetValue(1) i.e. type field from the above select statement // if "O' then go operator page, else go to administrator page.
if (loginReader.GetValue(1).ToString().ToUpper().Equals("O"))
{
Server.Transfer("//SMS/LoginUser/SuccessLoginOper.aspx");
}
else if (loginReader.GetValue(1).ToString().ToUpper().Equals("A"))
{
Server.Transfer("//SMS/LoginUser/SuccessLoginAdmin.aspx");
}
}
else
{
//clear content of textbox and display error message
txtId.Text="";
txtPwd.Text="";
lblLoginFail.Visible = true;
lblLoginFail.Text="Login Failed!<br>" + "Ensure that ID and Password are correct!";
}
}
catch (SqlException se)
{
if (se.Number == 17)
{
lblLoginFail.Visible = true;
lblLoginFail.Text = "Could not connect to the database";
}
else
{
lblLoginFail.Visible = true;
lblLoginFail.Text = se.Message;
}
}
//close SqlDataReader and SqlConnection
loginReader.Close();
sqlConnect.Close();

You can alter the database to be case sensitive, and I think you can also do that on a per connection basis - but you'd have to check that. The other way could be to return the passwords that have matched and then double check them in c#. I sure someone has a better method.|||Case-sesitivity is determined when installing SQL Server,
try running sp_help to see the current settings.
Passwords shouldn't be stored in plaintext in the database
anyway. I suggest you have a look at the hashing functions
in .Net and use them to calculate a hash and then save that
in the database.
Then you wouldn't have to worry about case-sensitivity either.|||Thanks you for reply!!

As you said running sp_help to see the current settings, how to change the current settings of case-sensitive problems.

I recognise that the passwords should be better stored in encrypted forms. But how to encrypt it in SQL Server. I am new in web development. Could you briefly tell me how to do? Or any web reference provided?

Waiting for reply! Thanks

Roy|||::As you said running sp_help to see the current settings, how to change the current settings
::of case-sensitive problems

He DID tell you it is determined on install time. So you can not change it.

::I recognise that the passwords should be better stored in encrypted forms.

Good. You are wrong, though. Storing encrypted passwords in SQL Server is as bad as storing them plain text. Hashing is not encryption.

::But how to encrypt it in SQL Server.

Why should you?

Hash (not encrypt) the passwords on the website, then store he hashed passwords in the server.

In the SQL only ask for the user's data by user name, retrieve the password hash from the server, hash the user input and compare. Do not forget to salt your hashes, as otherwise you are totally open to a dictionary attack.

::I am new in web development.

Not to development in general? Sounds more like this. I would suggest you invest heavily into some books.|||First of all you should be aware that the case-sensitivity settings are GLOBAL to the entire SQL Server and all databases on it.
If you really want to to the change you have to rebuild the master database using
Rebuildm.exe.
Do look it up in the books online first, and don't forget to backup your database before!

For hashing password have a look at the classes:
System.Security.Cryptography.MD5
or preferrably
System.Security.Cryptography.SHA1

Case sensitive/insensitive

Hi, I configure the SQL Server as case-insensitive. Can I configure a particular table's column to be case-sensitive
Thank you.During the instalation you can choose the default collation for all
databases, for a particular database you can define this durin the database
creation process.
Look at bol for COLLATE clause.
HTH
Wandenkolk T. Neto
MCSE, MCDBA, MCP
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:10435BC6-7691-4655-8D52-CAD92F45FBD0@.microsoft.com...
> Hi, I configure the SQL Server as case-insensitive. Can I configure a
particular table's column to be case-sensitive?
> Thank you.|||To add to Wandenkolk's response, in SQL Server 2000 you can specify
COLLATE at the column level to control case sensitivity.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:10435BC6-7691-4655-8D52-CAD92F45FBD0@.microsoft.com...
> Hi, I configure the SQL Server as case-insensitive. Can I configure a
particular table's column to be case-sensitive?
> Thank you.|||I was also wondering about case senitivity on columns only.
Under EM on each table I found where I can change the collate... but
there is about 50 different ones listed. Where can I find out about
the differences. Mine is currently set to
"SQL_Latin1_General_CP1_CI_AI"
Whatever that is...
Al.
On Sun, 16 Nov 2003 21:47:25 -0600, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:
>To add to Wandenkolk's response, in SQL Server 2000 you can specify
>COLLATE at the column level to control case sensitivity.|||You can get a list of available collations with fn_helpcollations:
SELECT * FROM ::fn_helpcollations()
This is described in the COLLATE topic in the Books Online
<tsqlref.chm::/ts_ca-co_5z55.htm>.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Harag" <harag@.REMOVETHESECAPITALSsofthome.net> wrote in message
news:7m7hrv4toku4433mpmfqf343ultn44ntuu@.4ax.com...
> I was also wondering about case senitivity on columns only.
> Under EM on each table I found where I can change the collate... but
> there is about 50 different ones listed. Where can I find out about
> the differences. Mine is currently set to
> "SQL_Latin1_General_CP1_CI_AI"
> Whatever that is...
> Al.
> On Sun, 16 Nov 2003 21:47:25 -0600, "Dan Guzman"
> <danguzman@.nospam-earthlink.net> wrote:
> >To add to Wandenkolk's response, in SQL Server 2000 you can specify
> >COLLATE at the column level to control case sensitivity.
>sql

case sensitive sql

Hi
how can i use the sensive case in a select field from table where fild='GhhY' ?



I think you'd have to do character by character comparison using SUBSTRINGs and UPPER/LOWERs unless someone else here has a better solution.|||select * from TABLE where cast(fild as varbinary) = cast('GhhY' as varbinary)

Nicksql

Case sensitive query? Problems with Integration Services Query

Hi,
i am writing a IntegrationServices2005 job which copies the result of a
view to a table.
I found a very strange problem:
select * from [dbo].[warehouse_aufwandurlaub] -> runs in < 1 second.
select * from [dbo].[warehouse_AufwandUrlaub] -> runs in > 30 Second
s
(timeout).
the name of the view is "warehouse_AufwandUrlaub".
If i run the query in query analyzer, both querys exectute very fast.
What is causing this failure? how can i avoid this?
Thx
Best regards,
Patrick DingerSorry i forgot to mention, that i am using SQL Server 2005 with SP1.
Thx in advance,
Patrick Dinger|||Any activites on the server at this time?
Don't you really need WHERE condition?
"Patrick Dinger" <paxos2k@.gmail.com> wrote in message
news:1147690976.451955.271160@.j55g2000cwa.googlegroups.com...
> Sorry i forgot to mention, that i am using SQL Server 2005 with SP1.
> Thx in advance,
> Patrick Dinger
>|||Hi,
its not an ressource problem, there isn't really activity on the server
currently.
the error is reproducible. No, i do not need a where condition...
Best regards,
Patrick Dinger

Case sensitive query? Problems with Integration Services Query

Hi,
i am writing a IntegrationServices2005 job which copies the result of a
view to a table.
I found a very strange problem:
select * from [dbo].[warehouse_aufwandurlaub] -> runs in < 1 second.
select * from [dbo].[warehouse_AufwandUrlaub] -> runs in > 30 Seconds
(timeout).
the name of the view is "warehouse_AufwandUrlaub".
If i run the query in query analyzer, both querys exectute very fast.
What is causing this failure? how can i avoid this?
Thx
Best regards,
Patrick DingerSorry i forgot to mention, that i am using SQL Server 2005 with SP1.
Thx in advance,
Patrick Dinger|||Any activites on the server at this time?
Don't you really need WHERE condition?
"Patrick Dinger" <paxos2k@.gmail.com> wrote in message
news:1147690976.451955.271160@.j55g2000cwa.googlegroups.com...
> Sorry i forgot to mention, that i am using SQL Server 2005 with SP1.
> Thx in advance,
> Patrick Dinger
>|||Hi,
its not an ressource problem, there isn't really activity on the server
currently.
the error is reproducible. No, i do not need a where condition...
Best regards,
Patrick Dinger

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!

Case sensitive of table name

i installed Sql server 2005 on my computer.
when i try to do some simple query from the sample database
(AdventureWorks), i realize that the table name is case sensitive..
select * from Production.product => Invalid object name
'Production.product'.
select * from Production.Product => rows returned...
is this true that the table names are case sensitive ?
In a case sensitive database they are.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zas" <yeezett@.gmail.com> wrote in message
news:1137553660.480416.239260@.z14g2000cwz.googlegr oups.com...
>i installed Sql server 2005 on my computer.
> when i try to do some simple query from the sample database
> (AdventureWorks), i realize that the table name is case sensitive..
> select * from Production.product => Invalid object name
> 'Production.product'.
> select * from Production.Product => rows returned...
> is this true that the table names are case sensitive ?
>
|||u mean.. it's depend of the database ?
and.. i can set the database is case sensitive or not ?
|||yes, CREATE DATABASE databaseName CollationName where the collation name is
a collcation with a sufix of CI, you can get a list of collations using
this query:
SELECT *
FROM ::fn_helpcollations()
For most American databases you should use SQL_Latin1_General_CP1_CI_AS
which is case insensitive.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zas" <yeezett@.gmail.com> wrote in message
news:1137554119.669213.165690@.g43g2000cwa.googlegr oups.com...
>u mean.. it's depend of the database ?
> and.. i can set the database is case sensitive or not ?
>
|||declare @.lng varchar(500)
select @.lng =
convert(sysname,Databasepropertyex(db_name(),'coll ation'))
print @.lng
select 'Server default collation' = description
from ::fn_helpcollations() C
where C.name = @.lng
Run this query for selected database in query anlyzer.
Regards
Amish
|||thx you..
i got it now..
Regards
zas

Case sensitive of table name

i installed Sql server 2005 on my computer.
when i try to do some simple query from the sample database
(AdventureWorks), i realize that the table name is case sensitive..
select * from Production.product => Invalid object name
'Production.product'.
select * from Production.Product => rows returned...
is this true that the table names are case sensitive 'In a case sensitive database they are.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zas" <yeezett@.gmail.com> wrote in message
news:1137553660.480416.239260@.z14g2000cwz.googlegroups.com...
>i installed Sql server 2005 on my computer.
> when i try to do some simple query from the sample database
> (AdventureWorks), i realize that the table name is case sensitive..
> select * from Production.product => Invalid object name
> 'Production.product'.
> select * from Production.Product => rows returned...
> is this true that the table names are case sensitive '
>|||u mean.. it's depend of the database ?
and.. i can set the database is case sensitive or not ?|||yes, CREATE DATABASE databaseName CollationName where the collation name is
a collcation with a sufix of CI, you can get a list of collations using
this query:
SELECT *
FROM ::fn_helpcollations()
For most American databases you should use SQL_Latin1_General_CP1_CI_AS
which is case insensitive.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zas" <yeezett@.gmail.com> wrote in message
news:1137554119.669213.165690@.g43g2000cwa.googlegroups.com...
>u mean.. it's depend of the database ?
> and.. i can set the database is case sensitive or not ?
>|||declare @.lng varchar(500)
select @.lng =
convert(sysname,Databasepropertyex(db_na
me(),'collation'))
print @.lng
select 'Server default collation' = description
from ::fn_helpcollations() C
where C.name = @.lng
Run this query for selected database in query anlyzer.
Regards
Amish|||thx you..
i got it now..
Regards
zas

Case sensitive of table name

i installed Sql server 2005 on my computer.
when i try to do some simple query from the sample database
(AdventureWorks), i realize that the table name is case sensitive..
select * from Production.product => Invalid object name
'Production.product'.
select * from Production.Product => rows returned...
is this true that the table names are case sensitive 'In a case sensitive database they are.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zas" <yeezett@.gmail.com> wrote in message
news:1137553660.480416.239260@.z14g2000cwz.googlegroups.com...
>i installed Sql server 2005 on my computer.
> when i try to do some simple query from the sample database
> (AdventureWorks), i realize that the table name is case sensitive..
> select * from Production.product => Invalid object name
> 'Production.product'.
> select * from Production.Product => rows returned...
> is this true that the table names are case sensitive '
>|||u mean.. it's depend of the database ?
and.. i can set the database is case sensitive or not ?|||yes, CREATE DATABASE databaseName CollationName where the collation name is
a collcation with a sufix of CI, you can get a list of collations using
this query:
SELECT *
FROM ::fn_helpcollations()
For most American databases you should use SQL_Latin1_General_CP1_CI_AS
which is case insensitive.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zas" <yeezett@.gmail.com> wrote in message
news:1137554119.669213.165690@.g43g2000cwa.googlegroups.com...
>u mean.. it's depend of the database ?
> and.. i can set the database is case sensitive or not ?
>|||declare @.lng varchar(500)
select @.lng =convert(sysname,Databasepropertyex(db_name(),'collation'))
print @.lng
select 'Server default collation' = description
from ::fn_helpcollations() C
where C.name = @.lng
Run this query for selected database in query anlyzer.
Regards
Amish|||thx you..
i got it now.. :)
Regards
zas

Case Sensitive Object Names?

My new DB seems to be "case-sensitive". I have a table named Bld_List.

Select * From Bld_List

returns all records in QA. However, if I don't follow the correct case

Select * From bld_list

I get this error: Invalid object name 'bld_list'.

Also, when I try to get rid of some unnecessary records by using this

DELETE
FROM Bld_List
WHERE (PRODUCT IN
(SELECT DISTINCT Product
FROM BldOff_Inv_Daily))

I get this error: Cannot resolve collation conflict for equal to operation.

I imagine I set something up wrong when I first built the DB, but I don't know what to look for.

ThanksI'd suspect you are not using the default character set collations. Character sets can be either case-sensitive or case-insensitive, and I think this applies not just to the data but to the object names as well.|||So is that something I can change?

I used the Collation Name that my other databases are set to:
SQL_Latin1_General_Cp1_CI_AS
or is it some other setting?|||Hi,

CI is pointing that the collation is Case Insensitive.

If you take a create script of your tables, you can see if a non-default collation is used.

Eralper
http://www.kodyaz.com|||What does sp_helpsort tell you?|||Hi,

CI is pointing that the collation is Case Insensitive.

If you take a create script of your tables, you can see if a non-default collation is used.

Eralper
http://www.kodyaz.com

hmmm...
[PRODUCT] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL

Do I need to rebuild and repopulate the table? I only have a few so far, so it wouldn't be a huge deal.|||What does sp_helpsort tell you?

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data|||Ok, I think I've got it. I did change the collation at some point over the weekend. Any tables I created after that are okay.
If I just use the Alter Table Collation clause on the first tables I created, that should take care of the problem, right?

Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
LudekHi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can not
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:
> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
> > Hello,
> > SQL server 2000, database Test1, Collation name Czech_CS_AS
> > I have this table:
> > aa F1 aa
> > bb f1 bb
> > cc F1 cc
> > ***
> > SELECT TOP 8 Table1.*
> > FROM dbo.Table1 Table1
> > WHERE Table1.rozmer >= 'F1'
> > ORDER BY Table1.rozmer, Table1.nazev
> >
> > Result:
> > aa F1 aa
> > cc F1 cc
> > OK
> > ***
> > but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> > SELECT TOP 8 Table1.*
> > FROM dbo.Table1 Table1
> > WHERE Table1.rozmer+Table1.nazev >= 'F1'
> > ORDER BY Table1.rozmer, Table1.nazev
> >
> > Return all rows - also row with rozmer = 'f1' :
> > bb f1 bb
> > aa F1 aa
> > cc F1 cc
> >
> > Thank You for help
> > Ludek
> >
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>|||Hi Ludek
"Ludek" wrote:
> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with this
> 5 fields from all database + detailed view for one record. But database have
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first example?
Johnsql

Case sensitive

Hi all, I'm using Thai_BIN collation for my database. When i build my SELECT
statement, i have to be case sensitive on the table name and field name. Is
there anywhere to ignore this so that i don't have to care about the case s
ensitivity? Is there any se
tting on the server that i can turn this off? Or is it a design by feature?On Tue, 16 Mar 2004 19:11:05 -0800, Loon wrote:

>Hi all, I'm using Thai_BIN collation for my database. When i build my SELECT statem
ent, i have to be case sensitive on the table name and field name. Is there anywhere
to ignore this so that i don't have to care about the case sensitivity? Is there an
y s
etting on the server that i can turn this off? Or is it a design by feature?
A binary sort order makes sorting and comparisons faster than any
other collation sequence can be, since SQL Server only has to compare
the byte values of the characters.
The drawback is that characters with different byte values (like 'A'
and 'a') compare unequal.
The only way to change collation sequence after installation is to
rebuild the master database and then recreate all your databases with
the new collation sequence. Refer to BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)