|
Home > Archive > ASE Database forum > October 2005 > Case sensitivity revisited
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Case sensitivity revisited
|
|
| Frank Rizzo 2005-10-27, 8:21 am |
| Hello,
Attempted to figure out whether the server is case sensitive by
executing: sp_serverinfo "server_soname"
It returned 'bin_cp850'. What does this mean? Is it case sensitive or
not? I also ran sp_helpsort, but it didn't return anything useful.
So my question is how to determine whether the server is setup to be
case sensitive or not. And also, how I change this setting?
Thanks
| |
| Jayadeva 2005-10-27, 8:21 am |
| Hi,
bin_cp250 sort order is a case sensitive.
To Change:
You can change your character set and sortorder by
sp_configure 'default character set id', <id>
sp_configure 'default sortorder id', <id>
both parameters are static.
This Id should exist in master..syscharset table, and if not
you can add by using "charset" utility (for Windows and UNIX
env) and if it's windows you can use "server config"
utility.
To Test:
For testing try to create a two tables with the same name.
For more info:
http://sybooks.sybase.com/onlineboo...s/asg1251e/sag1
Regards,
-Jayadeva.
> Hello,
>
> Attempted to figure out whether the server is case
> sensitive by executing: sp_serverinfo "server_soname"
>
> It returned 'bin_cp850'. What does this mean? Is it case
> sensitive or not? I also ran sp_helpsort, but it didn't
> return anything useful.
>
> So my question is how to determine whether the server is
> setup to be case sensitive or not. And also, how I
> change this setting?
>
> Thanks
| |
| Frank Rizzo 2005-10-27, 8:21 am |
| Jayadeva wrote:
> Hi,
>
> bin_cp250 sort order is a case sensitive.
Ok, you knew that, but how would I know whether a certain sort order is
case sensitive or not. Is there a list or a query?
[color=darkred]
>
> To Change:
>
> You can change your character set and sortorder by
>
> sp_configure 'default character set id', <id>
> sp_configure 'default sortorder id', <id>
>
> both parameters are static.
>
> This Id should exist in master..syscharset table, and if not
> you can add by using "charset" utility (for Windows and UNIX
> env) and if it's windows you can use "server config"
> utility.
>
> To Test:
>
> For testing try to create a two tables with the same name.
>
> For more info:
>
> http://sybooks.sybase.com/onlineboo...s/asg1251e/sag1
>
> Regards,
> -Jayadeva.
>
>
| |
|
|
|
| You can also use sp_helpsort
> Jayadeva wrote:
>
> Ok, you knew that, but how would I know whether a certain
> sort order is case sensitive or not. Is there a list or
> a query?
>
>
>
http://sybooks.sybase.com/onlineboo...s/asg1251e/sag1[color=darkred]
> case >>sensitive or not? I also ran sp_helpsort, but it
> didn't >>return anything useful.
| |
|
|
| Sherlock, Kevin 2005-10-27, 8:22 am |
| Well, you could do several tests like:
select charindex('a','A')
Where the function would return "1" if case insensitive or "0" if case
sensitive.
Or, you could get a bit more descriptive:
select case 'a'
when 'A' then 'Case_Insensitive'
else 'Case_Sensitive'
end
If you want info about EVERY sort order available in the server, you could do
something like:
set nocount on
declare @i binary(1)
select @i = sortkey(null,"all") /* loads all sort orders in syscharsets */
select
"Collation Name" = name,
"Collation ID" = id,
case sortkey('a',id)
when sortkey('A',id) then 'Case_Insensitive'
else 'Case_Sensitive'
end as 'Sensitivity'
from
master.dbo.syscharsets
where
type = 2003
select
" This_Server's_Sort_O
rder",
"Collation ID" = value,
case sortkey('a',value)
when sortkey('A',value) then 'Case_Insensitive'
else 'Case_Sensitive'
end as 'Sensitivity'
from
master.dbo.syscurconfigs where config = 123
"Frank Rizzo" <none@none.net> wrote in message news:435d1d29$1@foru
ms-2-dub...[color=darkred]
> Thank you. I looked at it, but still I don't see a straightforward (or
> not) way to run some type of query that will return true/false,
> depending whether the sort order is case sensitive or not.
>
>
>
> Jayadeva wrote:
| |
| Jayadeva 2005-10-27, 8:22 am |
| Thanks Kevin,
Regards,
-Jayadeva.
> Well, you could do several tests like:
>
> select charindex('a','A')
>
> Where the function would return "1" if case insensitive or
> "0" if case sensitive.
>
> Or, you could get a bit more descriptive:
>
> select case 'a'
> when 'A' then 'Case_Insensitive'
> else 'Case_Sensitive'
> end
>
> If you want info about EVERY sort order available in the
> server, you could do something like:
>
> set nocount on
> declare @i binary(1)
> select @i = sortkey(null,"all") /* loads all sort orders
> in syscharsets */ select
> "Collation Name" = name,
> "Collation ID" = id,
> case sortkey('a',id)
> when sortkey('A',id) then 'Case_Insensitive'
> else 'Case_Sensitive'
> end as 'Sensitivity'
> from
> master.dbo.syscharsets
> where
> type = 2003
> select
> " This_Server's_Sort_O
rder",
> "Collation ID" = value,
> case sortkey('a',value)
> when sortkey('A',value) then 'Case_Insensitive'
> else 'Case_Sensitive'
> end as 'Sensitivity'
> from
> master.dbo.syscurconfigs where config = 123
>
>
> "Frank Rizzo" <none@none.net> wrote in message
> depending whether the sort order is case sensitive or not.
>
http://sybooks.sybase.com/onlineboo...s/asg1251e/sag1
>
http://sybooks.sybase.com/onlineboo...s/asg1251e/sag1
>
>
| |
|
| Yes/no answers are sometimes hard to come by. You can
always examine the stored procedure code (sp_helpsort) to
understand how the information is gathered and write your
own variation.
Try the following:
select charset = c.name,
SortId = c.id,
case when f.value in (20, 22, 24, 25, 50) then
'Sensitive'
when f.value in (21, 45, 46, 47, 51, 53, 65, 69)
then 'Dictionary'
else 'Insensitive'
end as 'Sort'
from master..syscharsets c , master..sysconfigures f
where f.value = c.id
and f.name = 'default sortorder id'
> Thank you. I looked at it, but still I don't see a
> straightforward (or not) way to run some type of query
> that will return true/false, depending whether the sort
> order is case sensitive or not.
>
>
>
> Jayadeva wrote:
>
http://sybooks.sybase.com/onlineboo...s/asg1251e/sag1
> certain >>sort order is case sensitive or not. Is there
> a list or >>a query?
> if >>>not you can add by using "charset" utility (for
> Windows >>>and UNIX env) and if it's windows you can use
> "server >>>config" utility.
>
http://sybooks.sybase.com/onlineboo...s/asg1251e/sag1[color=darkred]
> it >>didn't >>return anything useful.
> is >>>>setup to be case sensitive or not. And also, how
> I >>>>change this setting?
| |
| Frank Rizzo 2005-10-27, 8:22 am |
| Sherlock, Kevin wrote:
> Well, you could do several tests like:
>
> select charindex('a','A')
>
> Where the function would return "1" if case insensitive or "0" if case
> sensitive.
>
> Or, you could get a bit more descriptive:
>
> select case 'a'
> when 'A' then 'Case_Insensitive'
> else 'Case_Sensitive'
> end
This is good. So does the result apply to case sensitivity with the
data and schema? Or just one of the two?
Thanks
>
> If you want info about EVERY sort order available in the server, you could do
> something like:
>
> set nocount on
> declare @i binary(1)
> select @i = sortkey(null,"all") /* loads all sort orders in syscharsets */
> select
> "Collation Name" = name,
> "Collation ID" = id,
> case sortkey('a',id)
> when sortkey('A',id) then 'Case_Insensitive'
> else 'Case_Sensitive'
> end as 'Sensitivity'
> from
> master.dbo.syscharsets
> where
> type = 2003
> select
> " This_Server's_Sort_O
rder",
> "Collation ID" = value,
> case sortkey('a',value)
> when sortkey('A',value) then 'Case_Insensitive'
> else 'Case_Sensitive'
> end as 'Sensitivity'
> from
> master.dbo.syscurconfigs where config = 123
>
>
> "Frank Rizzo" <none@none.net> wrote in message news:435d1d29$1@foru
ms-2-dub...
>
>
>
>
| |
| mpeppler@peppler.org 2005-10-27, 8:22 am |
| > Sherlock, Kevin wrote:
>
>
> This is good. So does the result apply to case
> sensitivity with the data and schema? Or just one of the
> two?
Case (in)sensitivity is an all-or-nothing setting, so it
applies to the data and to the schema (column names, etc.)
Michael
|
|
|
|
|