check if field contains numeric character

How can I select all of the rows of my table for which a certain
field's value contains other characters than a-z and A-Z (non alpha
string) ?
Thank youWhere Column like '%[0-9]%'
Look up "Pattern Matching in Search Conditions" in BOL for more information.
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||If it does not help ,please post your actual data + expected result
create table #t (c1 varchar(20))
insert into #t values ('cdjdfj')
insert into #t values ('cd4jdfj')
insert into #t values ('fh')
insert into #t values ('1525')
insert into #t values ('1jj')
insert into #t values ('jkk')
select * from #t where c1 like '%[0-9]%'
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||found out, had to use the PATINDEX function|||Uri Dimant wrote:
> If it does not help ,please post your actual data + expected result
> create table #t (c1 varchar(20))
> insert into #t values ('cdjdfj')
> insert into #t values ('cd4jdfj')
> insert into #t values ('fh')
> insert into #t values ('1525')
> insert into #t values ('1jj')
> insert into #t values ('jkk')
>
> select * from #t where c1 like '%[0-9]%'
>
What about :
insert into test values ('!"=A3$%^&*()_')
Try this instead:
select * from #t where c1 like '%[^A-Z]%'=20
Jamie.

Check for unicode within an ntext

Hey all,
I've got to check an ntext column if it actually does contain unicode characters or not. I would prefer to not have to iterate through every character in each column, is there a simple way to do this in sql server 2000?
Cheers,
-KilkaCAST or CONVERT it to text, and monitor for errors.|||That's a great idea, thanks Blindman.

Cheers,
-Kilka

check constraint or rule for control characters

I'm trying to control the accidental entry of special characters (carriage
return) from getting into my data. I thought I could write a check
constraint like what follows
[name] <> '%' + char(13) + '%'
but this doesn't work. Tried several permutations but the carriage return
is always accepted when I enter the data through an access database table
view (control + enter).
If I can get the expression working I think the best way to implement would
be either a user defined datatype or a rule bound to the column.
Has anyone done this sort of thing, or know how?
Thanks
David LHow about
CharIndex(Char(13), [Name], 1) = 0
Or
[Name] Not Like '%' + Char(13) + '%'
Thomas
"DavinciCoder" <dal@.rlpi.com> wrote in message
news:uqpvUvuaFHA.2664@.TK2MSFTNGP15.phx.gbl...
> I'm trying to control the accidental entry of special characters (carriage
> return) from getting into my data. I thought I could write a check constr
aint
> like what follows
> [name] <> '%' + char(13) + '%'
> but this doesn't work. Tried several permutations but the carriage return
is
> always accepted when I enter the data through an access database table vie
w
> (control + enter).
> If I can get the expression working I think the best way to implement woul
d be
> either a user defined datatype or a rule bound to the column.
> Has anyone done this sort of thing, or know how?
> Thanks
>
> --
> David L
>|||Ok, the second works great thanks
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:eh1KX0uaFHA.1152@.tk2msftngp13.phx.gbl...
> How about
> CharIndex(Char(13), [Name], 1) = 0
> Or
> [Name] Not Like '%' + Char(13) + '%'
>
> Thomas
>
> "DavinciCoder" <dal@.rlpi.com> wrote in message
> news:uqpvUvuaFHA.2664@.TK2MSFTNGP15.phx.gbl...
>|||David
You may want to check CHAR(10) as well.
"DavinciCoder" <dal@.rlpi.com> wrote in message
news:%23X6if6uaFHA.2884@.tk2msftngp13.phx.gbl...
> Ok, the second works great thanks
>
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:eh1KX0uaFHA.1152@.tk2msftngp13.phx.gbl...
>

Check 3 occurrences of double characters.

Hi all,

I need to write some sort of statement to identify which numbers from a list fall into the following pattern:

% TwoIdenticalNumbers % TwoIdenticalNumbers % TwoIdenticalNumbers %

for example:

08812355677

I thought I would be able to use a LIKE statement but I'm not sure how to write it so that double characters are used rather than single. For example if I wanted to check three numbers appear within the string I could do the following:

SELECT *

FROM Table

WHERE Number LIKE '%[0-9]%[0-9]%[0-9]%'

I think the easiest way to check the doubles would be to represent them as a string, so I want to replace each of the [0-9] above with something like the following:

['00' OR '11' OR '22' OR '33' OR '44' OR '55' OR '66' OR '66' OR '77' OR '88' OR '99']

How could I write this using proper SQL code?

Any help would be much appreciated.

Thanks very much,

Will

Thre is no predefined expressions available,

Following approach is one of the way to achive this,

Code Snippet

Create Table #data (

[Numbers] Varchar(100)

);

Insert Into #data Values('1242432');

Insert Into #data Values('242423423');

Insert Into #data Values('2332232');

Insert Into #data Values('828289');

Insert Into #data Values('99887766');

Insert Into #data Values('92829299');

select

numbers

from #data

cross join

(

select '00' n

union all

select '11'

union all

select '22'

union all

select '33'

union all

select '44'

union all

select '55'

union all

select '66'

union all

select '77'

union all

select '88'

union all

select '99' ) as d

group by numbers having sum(case when patindex('%'+ n + '%',numbers) <> 0 Then 1 Else 0 End) >= 3

|||

:-) Looks oofy, but works

SELECT * FROM sysobjects

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'

|||

The idea is good, but the following query might fit the asked requirement,(3 occurrence of …)

Code Snippet

SELECT Id FROM sysobjects

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%X%X%'

|||

Very close. At least three instances are needed.

'11' -- does not qualify

'1122' -- does not qualify

'112233' -- bingo

...

SELECT

*

FROM

(select '11' as ID) as t

WHERE

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),ID),'00','X'),'11','X'),'22','X'),'33','X'),'44','X') ,'55','X'),'66','X'),'77','X'),'88','X'),'99','X') LIKE '%X%'

AMB

|||

Talking about ugly. Try:

-- thanks to Manni for the sample data

select

numbers

from

#Data

where

(len(numbers) - len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(numbers, '00', ''), '11', ''), '22', ''), '33', ''), '44', ''), '55', ''), '66', ''), '77', ''), '88', ''), '99', ''))) / 2 >= 3

AMB

|||Thanks for all the help, this has saved me a lot of time trying to figure this one out.

Characters replaced by squares

Hi!
When running a report in our production environment we get squares instead
of characters. Does anyone have an idea why this is happening?Are there controls characters in the data you are displaying...? In some
instances if you have a carraige return, line feed, tab or something else
along those lines they'll be displayed as a square... In most cases in html
they're escaped the browser, considered white space, try exporting your
report to another format like PDF or Excel and see how the squares show up.
"Anders" wrote:
> Hi!
> When running a report in our production environment we get squares instead
> of characters. Does anyone have an idea why this is happening?|||Hi!
The squares also appears in the drop down lists when selecting report
parameters. What font is Reporting Services using in those drop downs?
/A
"Alien2_51" wrote:
> Are there controls characters in the data you are displaying...? In some
> instances if you have a carraige return, line feed, tab or something else
> along those lines they'll be displayed as a square... In most cases in html
> they're escaped the browser, considered white space, try exporting your
> report to another format like PDF or Excel and see how the squares show up.
> "Anders" wrote:
> > Hi!
> >
> > When running a report in our production environment we get squares instead
> > of characters. Does anyone have an idea why this is happening?

Characters not to use in Table and Field Names: your opinion/experiences

Hi,
I know we can use several non-alpha characters in the names of Tables and
Fields, but I still think it's not a good rpactice. The same thing with the
spaces etc, which can be used, but tiwh the brackets []. Microsoft even uses
underscore_ in their own tables, but still I don't like it because I guess
it could mess up when using the _ for a wildcard.
So I actually prefer not to use Table and field names with special
(non-alpha) characters. Reserved words aren't done either!
And what about figures? I can't really find an exemple wheir it could harm
something, but I don't like it either. Can anybody give me some more info
about this? And any idea about non-alpha characters that can be used safely?
All this not only taking in my Sql Server itself, but also programming
languages that use these databases (especially .NET).
Pieter
My personal preference is to have purely alphabetical table and column
names. For example, ProductID is much more preferable than Product_ID or
[Product-ID] or [Product ID]
Not having these non-alphabetic and special characters in the names makes it
much easier to type things when I am busily troubleshooting something for
example.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23Jsbr6$lFHA.572@.TK2MSFTNGP15.phx.gbl...
Hi,
I know we can use several non-alpha characters in the names of Tables and
Fields, but I still think it's not a good rpactice. The same thing with the
spaces etc, which can be used, but tiwh the brackets []. Microsoft even uses
underscore_ in their own tables, but still I don't like it because I guess
it could mess up when using the _ for a wildcard.
So I actually prefer not to use Table and field names with special
(non-alpha) characters. Reserved words aren't done either!
And what about figures? I can't really find an exemple wheir it could harm
something, but I don't like it either. Can anybody give me some more info
about this? And any idea about non-alpha characters that can be used safely?
All this not only taking in my Sql Server itself, but also programming
languages that use these databases (especially .NET).
Pieter

Characters not to use in Table and Field Names: your opinion/experiences

Hi,
I know we can use several non-alpha characters in the names of Tables and
Fields, but I still think it's not a good rpactice. The same thing with the
spaces etc, which can be used, but tiwh the brackets []. Microsoft even
uses
underscore_ in their own tables, but still I don't like it because I guess
it could mess up when using the _ for a wildcard.
So I actually prefer not to use Table and field names with special
(non-alpha) characters. Reserved words aren't done either!
And what about figures? I can't really find an exemple wheir it could harm
something, but I don't like it either. Can anybody give me some more info
about this? And any idea about non-alpha characters that can be used safely?
All this not only taking in my Sql Server itself, but also programming
languages that use these databases (especially .NET).
PieterMy personal preference is to have purely alphabetical table and column
names. For example, ProductID is much more preferable than Product_ID or
[Product-ID] or [Product ID]
Not having these non-alphabetic and special characters in the names makes it
much easier to type things when I am busily troubleshooting something for
example.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23Jsbr6$lFHA.572@.TK2MSFTNGP15.phx.gbl...
Hi,
I know we can use several non-alpha characters in the names of Tables and
Fields, but I still think it's not a good rpactice. The same thing with the
spaces etc, which can be used, but tiwh the brackets []. Microsoft even
uses
underscore_ in their own tables, but still I don't like it because I guess
it could mess up when using the _ for a wildcard.
So I actually prefer not to use Table and field names with special
(non-alpha) characters. Reserved words aren't done either!
And what about figures? I can't really find an exemple wheir it could harm
something, but I don't like it either. Can anybody give me some more info
about this? And any idea about non-alpha characters that can be used safely?
All this not only taking in my Sql Server itself, but also programming
languages that use these databases (especially .NET).
Pieter

Characters not to use in Table and Field Names: your opinion/experiences

Hi,
I know we can use several non-alpha characters in the names of Tables and
Fields, but I still think it's not a good rpactice. The same thing with the
spaces etc, which can be used, but tiwh the brackets []. Microsoft even uses
underscore_ in their own tables, but still I don't like it because I guess
it could mess up when using the _ for a wildcard.
So I actually prefer not to use Table and field names with special
(non-alpha) characters. Reserved words aren't done either!
And what about figures? I can't really find an exemple wheir it could harm
something, but I don't like it either. Can anybody give me some more info
about this? And any idea about non-alpha characters that can be used safely?
All this not only taking in my Sql Server itself, but also programming
languages that use these databases (especially .NET).
PieterMy personal preference is to have purely alphabetical table and column
names. For example, ProductID is much more preferable than Product_ID or
[Product-ID] or [Product ID]
Not having these non-alphabetic and special characters in the names makes it
much easier to type things when I am busily troubleshooting something for
example.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:%23Jsbr6$lFHA.572@.TK2MSFTNGP15.phx.gbl...
Hi,
I know we can use several non-alpha characters in the names of Tables and
Fields, but I still think it's not a good rpactice. The same thing with the
spaces etc, which can be used, but tiwh the brackets []. Microsoft even uses
underscore_ in their own tables, but still I don't like it because I guess
it could mess up when using the _ for a wildcard.
So I actually prefer not to use Table and field names with special
(non-alpha) characters. Reserved words aren't done either!
And what about figures? I can't really find an exemple wheir it could harm
something, but I don't like it either. Can anybody give me some more info
about this? And any idea about non-alpha characters that can be used safely?
All this not only taking in my Sql Server itself, but also programming
languages that use these databases (especially .NET).
Pieter

Characters not allowed in SQL varchar?

I have been using MS SQL server (8.0.194) and I have been wondering whatacters should I strip from entries before putting them into a varchar() field?

I check for single quote (') and handle that, and malicious attempts. But is it ok to have the newline characters in there(\r\n)? The always show up as the ASCII-square box, so I was wondering if I need to be stripping them out as well?
What other "normally used" text characters do I also need to watch out for, if any?

Thanks.I wouldn't think it would matter what you "put into a varchar" as long as when you "pull" the text back out you DISPLAY it in the same manner from where you saved it. That is, if you used a simple text box for a line of entry then it likely won't matter. But if you use a Rich Text Box for input, then you should use a Rich Text Box for output once the data is retrieved from the database, Newline characters and all. Even a single-quote won't matter as long as your ADO objects are written to allow single-quotes w/out needing to use escape characters or methods (such as double-single-quotes, or \', or whatever).

Otherwise, don't use a Rich Text Box or input or use simpler ASCII codes, if you are building a string, such as {Carriage Return} {Line Feed} rather than {Newline}.

Hope that helps.

Characters might change in text field values?

I am using a SQL Server 2000 database as the base of my application.
One of the tables stores saved user defined query information. I then use the values stored here to recreates user queries for data.
The problem is that when I get back to the table to retrieve data some characters are changed which leads to some application errors.
For example let's say my user wants to serach information based on info location. So he chosses to see all the results from distinct cities (Lisboa, Faro, Santarém and Setúbal). He does and he wants to retrieve the same info again when he returns to the
app, so he saves the query (and my application stores the select statement on a table field (this field is a text field)). When he returns to the app, he retrieves his sabed query and I get an SQL error, stating problems in the SQL statement. When I get
there to check for the statement I get a couple of '&' characters on strange places.
I am shure that is not a programming syntax error since I check the select statement just before it is stored.
Any idea what might be causing this?
I am using SQL Server 2000 SP3 english settings, Windows xp sp1 for the client app english version with portuguese locale settings.
What is the datatype for the column in which you are storing these values?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Hi. It's using the char datatype. The size is growing as needed.
"Vikram Jayaram [MS]" wrote:

> What is the datatype for the column in which you are storing these values?
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
|||Have you tried using nvarchar or nchar? Same results?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Same results.
"Vikram Jayaram [MS]" wrote:

> Have you tried using nvarchar or nchar? Same results?
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>

Characters might change in text field values?

I am using a SQL Server 2000 database as the base of my application.
One of the tables stores saved user defined query information. I then use th
e values stored here to recreates user queries for data.
The problem is that when I get back to the table to retrieve data some chara
cters are changed which leads to some application errors.
For example let's say my user wants to serach information based on info loca
tion. So he chosses to see all the results from distinct cities (Lisboa, Far
o, Santarém and Setúbal). He does and he wants to retrieve the same info a
gain when he returns to the
app, so he saves the query (and my application stores the select statement o
n a table field (this field is a text field)). When he returns to the app, h
e retrieves his sabed query and I get an SQL error, stating problems in the
SQL statement. When I get
there to check for the statement I get a couple of '&' characters on strange
places.
I am shure that is not a programming syntax error since I check the select s
tatement just before it is stored.
Any idea what might be causing this?
I am using SQL Server 2000 SP3 english settings, Windows XP sp1 for the clie
nt app english version with portuguese locale settings.What is the datatype for the column in which you are storing these values?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Hi. It's using the char datatype. The size is growing as needed.
"Vikram Jayaram [MS]" wrote:

> What is the datatype for the column in which you are storing these values?
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>|||Have you tried using nvarchar or nchar? Same results?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Same results.
"Vikram Jayaram [MS]" wrote:

> Have you tried using nvarchar or nchar? Same results?
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>

characters encoding

Hello,

in what code-page are characters stored in MSSQL tables?
is it windows1250?

--
ChrisYou can determine the instance default collation with SERVERPROPERTY:

SELECT SERVERPROPERTY('Collation')

Collation can be specified at the column, database or instance level. If
not specified when the column is created, the database default collation is
used. If no database collation is specified, then the instance collation is
used.

It is best to use the default instance collation unless you have a specific
reason to do otherwise.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Krzysiek" <stefanetNOSPAM@.nospam.o2.pl> wrote in message
news:cu2nrl$331$1@.news.onet.pl...
> Hello,
> in what code-page are characters stored in MSSQL tables?
> is it windows1250?
> --
> Chris|||Krzysiek (stefanetNOSPAM@.nospam.o2.pl) writes:
> in what code-page are characters stored in MSSQL tables?
> is it windows1250?

Text data can be stored in two ways in SQL Server: nchar/nvarchar/ntext
which is Unicode, and char/varchar/text which is some 8-bit encoding.

With Unicode datatypes you don't have to bother all characters are there.

With 8-bit datatypes they will be in some code page. And, yes, in your
case this could well be 1250, since this is the code page normally used
for Polish.

SQL Server have a concept of collations, and as Dan said there is a default
collation on server level, and there is also a default on database level.
But the actual collation can vary from column to column.

As Dan said, to get the default server collation, you can say:

SELECT SERVERPROPERTY('Collation')

To get the default collation for a database, you can do:

select databasepropertyex(db_name(), 'Collation')

And to see the collations for the columns in a table, use sp_help.

Once you have the collation, you can say:

select collationproperty('Collationname', 'CodePage')

to find the code page.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

character sets in SQL server

Hello!
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:

1) I would like to know if in SQL Server, the character set is machine
specific or is it defined at the database instance level?
2) Can multiple installs of SQL Server co-exist on a single server with
different default character sets?

Thanks

VishalVishal (vverma2@.gmail.com) writes:
> My client has a need to be able to store Japanese characters in their
> PeopleSoft database. So we need to change the character set from from
> Latin1_General (1252) to Japanese character set (932) on SQL Server
> 2000 Enterprise. I have 2 questions:
> 1) I would like to know if in SQL Server, the character set is machine
> specific or is it defined at the database instance level?
> 2) Can multiple installs of SQL Server co-exist on a single server with
> different default character sets?

First, you did not say which version of SQL Server you are using. The
answer is not same for SQL 7 and SQL 2000.

On SQL 7, you can only have one single sortorder - and a sortorder implies
a character set - on the server.

On SQL 2000, you can have multiple collations - as the terminolgy is on
SQL 2000 - on the same same server. In fact, every column have its own
collation. Thus, in theory you could create the PeopleSoft database to
use a suitable collation. Problem is if Peoplesoft does make use of
temp tables, in which case you will get collation conflicts when you
join temp tables with regular tables. Use of temp tables is quite common...

So in practice you is likely to have to change the collation for tempdb,
and that means that you have to rebuild the master database. (And this is
about as close as reinstallation of SQL Server you can come.) If you are
setting up a new server for PeopleSoft, this is not much of an issue anyway.

If you have several instances of SQL Server running on the same box,
they can use completely different collations, they are entirely
independent of each other.

Now, whether PeopleSoft supports the Japanese stuff, I don't know, but
I assume you've sorted that out with PeopleSoft already.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

character sets in SQL server

Hello!
My client has a need to be able to store Japanese characters in their
PeopleSoft database. So we need to change the character set from from
Latin1_General (1252) to Japanese character set (932) on SQL Server
2000 Enterprise. I have 2 questions:
1) I would like to know if in SQL Server, the character set is machine
specific or is it defined at the database instance level?
2) Can multiple installs of SQL Server co-exist on a single server with
different default character sets?
Thanks
Vishal
Vishal (vverma2@.gmail.com) writes:
> My client has a need to be able to store Japanese characters in their
> PeopleSoft database. So we need to change the character set from from
> Latin1_General (1252) to Japanese character set (932) on SQL Server
> 2000 Enterprise. I have 2 questions:
> 1) I would like to know if in SQL Server, the character set is machine
> specific or is it defined at the database instance level?
> 2) Can multiple installs of SQL Server co-exist on a single server with
> different default character sets?
First, you did not say which version of SQL Server you are using. The
answer is not same for SQL 7 and SQL 2000.
On SQL 7, you can only have one single sortorder - and a sortorder implies
a character set - on the server.
On SQL 2000, you can have multiple collations - as the terminolgy is on
SQL 2000 - on the same same server. In fact, every column have its own
collation. Thus, in theory you could create the PeopleSoft database to
use a suitable collation. Problem is if Peoplesoft does make use of
temp tables, in which case you will get collation conflicts when you
join temp tables with regular tables. Use of temp tables is quite common...
So in practice you is likely to have to change the collation for tempdb,
and that means that you have to rebuild the master database. (And this is
about as close as reinstallation of SQL Server you can come.) If you are
setting up a new server for PeopleSoft, this is not much of an issue anyway.
If you have several instances of SQL Server running on the same box,
they can use completely different collations, they are entirely
independent of each other.
Now, whether PeopleSoft supports the Japanese stuff, I don't know, but
I assume you've sorted that out with PeopleSoft already.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Character Set (code page)

I have to test an application under a Greek version of Windows.
I heave installed Windows XP - Greek. The Greek characters appears OK in the application contros (edits), but Crystal Reports viewer continues showing U.S.English character set.
Please, does anybody know how to set Greek character set for the Crystal Reports?
Any clue should be very appreciated.
Thanks!I've got the same problem. :confused:|||Hey, crystalreportsgurus around the world, wake up!

Character Problems

The spesific characters of turkish alphabets ->>i?ü???
couse problems while searching in the Fulltext search.
The letter ? is "I" in the uppercase in our alphabet which is equal to the
English letter "I"
The the other lets does not exists in english alphabet.
so the turkish letters always couse problem for us.
Thanks for any sugestions from now.
What happens if you use the neutral word breaker? I take it you are either
using nvarchar, nchar, or nText; or using the Turkish collation.
Also, what is the error you are getting? Are you getting an error message,
or instead are you not getting any results?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Esref DURNA" <EsrefDURNA@.discussions.microsoft.com> wrote in message
news:268F7F6A-41AD-4FFC-AB76-9FCC7510A4F5@.microsoft.com...
> The spesific characters of turkish alphabets ->>igs
> couse problems while searching in the Fulltext search.
> The letter i is "I" in the uppercase in our alphabet which is equal to the
> English letter "I"
> The the other lets does not exists in english alphabet.
> so the turkish letters always couse problem for us.
> Thanks for any sugestions from now.
>
|||The user could write "?zmir" or "izmir"
These quer?es gets different number of results.
We are using nText .
SQLserver Fulltext engine does not support turkish language so we cant
choise it by the wizard.
Thanks for your help
"Hilary Cotter" wrote:

> What happens if you use the neutral word breaker? I take it you are either
> using nvarchar, nchar, or nText; or using the Turkish collation.
> Also, what is the error you are getting? Are you getting an error message,
> or instead are you not getting any results?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Esref DURNA" <EsrefDURNA@.discussions.microsoft.com> wrote in message
> news:268F7F6A-41AD-4FFC-AB76-9FCC7510A4F5@.microsoft.com...
>
>
|||Esref DURNA,
There is no need to use the Full-Text Indexing Wizard as you can use T-SQL
code to accomplish the same thing, for details see:
http://spaces.msn.com/members/jtkane...&_c=blogp art
Specifically, you can use the following SQL code and change the code to
reference your database and table and then change the nText column to use
the Neutral "Language for Word Breaker":
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
-- add 0 as the last parameter for the Neutral wordbreaker.
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add', 0
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add', 0
EXEC sp_fulltext_table 'pub_info', 'activate'
END
go
Furthermore, you should insure that you are using the "N prefix" when
referencing text and data in the Unicode datatypes of nText, nChar and
nVarchar, see KB article 239530 (Q239530) "INF: Unicode String Constants in
SQL Server Require N Prefix"
http://support.microsoft.com/default...b;en-us;239530 for more
details.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Esref DURNA" <EsrefDURNA@.discussions.microsoft.com> wrote in message
news:E3E505F6-756D-4209-8644-7803497690E4@.microsoft.com...[vbcol=seagreen]
> The user could write "Izmir" or "izmir"
> These queries gets different number of results.
> We are using nText .
> SQLserver Fulltext engine does not support turkish language so we cant
> choise it by the wizard.
> Thanks for your help
> "Hilary Cotter" wrote:
either[vbcol=seagreen]
message,[vbcol=seagreen]
the[vbcol=seagreen]
|||Could u tell the steps which we should follow?
and does the fulltext index will incres 1 time when we change the nText to
Language for Word Breaker?
What will be the basic steps?
And Does this query will return true "N'(izmir == ?zmir)" where the
character "?" is the upper case version of the letter i on our language.
And Thanks your help from now
"John Kane" wrote:

> Esref DURNA,
> There is no need to use the Full-Text Indexing Wizard as you can use T-SQL
> code to accomplish the same thing, for details see:
> http://spaces.msn.com/members/jtkane...&_c=blogp art
> Specifically, you can use the following SQL code and change the code to
> reference your database and table and then change the nText column to use
> the Neutral "Language for Word Breaker":
> use pubs
> go
> IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
> BEGIN
> print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
> Catalog...'
> EXEC sp_fulltext_table 'pub_info', 'drop'
> EXEC sp_fulltext_catalog 'PubInfo', 'drop'
> END
> ELSE IF OBJECTPROPERTY (
> object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
> BEGIN
> print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
> Index & Activating...'
> EXEC sp_fulltext_catalog 'PubInfo', 'create'
> EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
> -- add 0 as the last parameter for the Neutral wordbreaker.
> EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add', 0
> EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add', 0
> EXEC sp_fulltext_table 'pub_info', 'activate'
> END
> go
> Furthermore, you should insure that you are using the "N prefix" when
> referencing text and data in the Unicode datatypes of nText, nChar and
> nVarchar, see KB article 239530 (Q239530) "INF: Unicode String Constants in
> SQL Server Require N Prefix"
> http://support.microsoft.com/default...b;en-us;239530 for more
> details.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Esref DURNA" <EsrefDURNA@.discussions.microsoft.com> wrote in message
> news:E3E505F6-756D-4209-8644-7803497690E4@.microsoft.com...
> either
> message,
> the
>
>
|||The problem is that Izmir is considered to be a different word from "izmir"
as the capital letter I has a diacritic on it. For this to work you will
need a Turkish word breaker; I don't believe one currently exists for SQL
2000, however SQL 2005 does ship with one.
Depending on your requirements you may be able to use SQL 2005 which is
currently in beta 2.
Otherwise you might be able to parse at the client to trap for I and replace
it with I and I. So a search on Izmir would be expanded to Izmir OR Izmir
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Esref DURNA" <EsrefDURNA@.discussions.microsoft.com> wrote in message
news:E3E505F6-756D-4209-8644-7803497690E4@.microsoft.com...[vbcol=seagreen]
> The user could write "Izmir" or "izmir"
> These queries gets different number of results.
> We are using nText .
> SQLserver Fulltext engine does not support turkish language so we cant
> choise it by the wizard.
> Thanks for your help
> "Hilary Cotter" wrote:
either[vbcol=seagreen]
message,[vbcol=seagreen]
the[vbcol=seagreen]
|||Esref DURNA,
Sure, I've modified the script from my blog to be more generic and less
specific to the Pubs database, you will need to modify it and replace the
<values> with your real FT Catalog name, table and column names.
I don't understand this question: "does the fulltext index will incres 1
time when we change the nText to Language for Word Breaker?" Could you
explain in more detail what is your concern?
Q. Does this query will return true "N'(izmir == Izmir)" where the character
"I" is the upper case version of the letter i on our language?
A. The answer is both yes and no. Full Text Search queries are
case-insenstive, so normally a lower-case word "izmir" is the equalivant of
"Izmir", but in this case the upper-case letter "I" also has a accented
letter and Full Text Search queries are accent senstive regardless of the
database or column collation. Specificly, for Full Text Search queries
"N'(izmir != Izmir)".
use <your_database_name>
go
--> NOTE: Only run this ONCE per database !!!
sp_fulltext_database 'enable'
go
IF OBJECTPROPERTY (
object_id('<your_table_name>'),'TableHasActiveFull textIndex') = 1
BEGIN
print 'Table <your_table_name> is Full-Text Enabled, dropping Full-Text
Index & Catalog...'
EXEC sp_fulltext_table '<your_table_name>', 'drop'
EXEC sp_fulltext_catalog '<your_FT_Catalog_name>', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('<your_table_name>'),'TableHasActiveFull textIndex') = 0
BEGIN
print 'Table <your_table_name> is NOT Full-Text Enabled, creating FT
Catalog, Index & Activating...'
EXEC sp_fulltext_catalog '<your_FT_Catalog_name>', 'create'
EXEC sp_fulltext_table '<your_table_name>', 'create',
'<your_FT_Catalog_name>', 'UPKCL_pubinfo'
EXEC sp_fulltext_column '<your_nText_Column_name>',
'<your_Table_Primary_key', 'add',0
EXEC sp_fulltext_table '<your_table_name>', 'activate'
END
-- After Enabling & Activating Tables, Columns & Indexes - Start Full
Population
BEGIN
SET NOCOUNT ON
DECLARE @.begin datetime
DECLARE @.end datetime
SET @.begin = CURRENT_TIMESTAMP
EXEC sp_fulltext_catalog '<your_FT_Catalog_name>', 'start_full' -- "Full
Crawl"
-- Wait for crawl to complete
DECLARE @.status int, @.itemCount int, @.keyCount int, @.indexSize int
SELECT @.status = FullTextCatalogProperty('<your_FT_Catalog_name>',
'populatestatus')
WHILE (@.status <> 0)
BEGIN
WAITFOR DELAY '00:00:01' -- wait for 1 second before checking FT
Populatestatus...
SELECT @.status = FullTextCatalogProperty('<your_FT_Catalog_name>',
'populatestatus')
END
SET NOCOUNT OFF
END
go
-- Confirm above results with:
SELECT *
FROM <your_Table_name> WHERE CONTAINS(*, '"<search_word>"')
go
Hope this helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Esref DURNA" <EsrefDURNA@.discussions.microsoft.com> wrote in message
news:93AF07D4-3228-4661-B291-67D38D780CE9@.microsoft.com...[vbcol=seagreen]
> Could u tell the steps which we should follow?
> and does the fulltext index will incres 1 time when we change the nText to
> Language for Word Breaker?
> What will be the basic steps?
> And Does this query will return true "N'(izmir == Izmir)" where the
> character "I" is the upper case version of the letter i on our language.
> And Thanks your help from now
> "John Kane" wrote:
T-SQL[vbcol=seagreen]
http://spaces.msn.com/members/jtkane...&_c=blogp art[vbcol=seagreen]
use[vbcol=seagreen]
= 1[vbcol=seagreen]
&[vbcol=seagreen]
'UPKCL_pubinfo'[vbcol=seagreen]
in[vbcol=seagreen]
message[vbcol=seagreen]
equal to[vbcol=seagreen]
|||hmmmm, the diacritic somehow has disappeared. What I meant to say is trap
for the I with the dot on top of it and then expand your search phrase on
the original word with the dot on top of the I and or it with the plain I,
like this
(Izmir OR I(with a dot on top)zmir) AND (remaining tokens in your search
phrase)
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ehqyyT%238EHA.208@.TK2MSFTNGP12.phx.gbl...
> The problem is that Izmir is considered to be a different word from
> "izmir"
> as the capital letter I has a diacritic on it. For this to work you will
> need a Turkish word breaker; I don't believe one currently exists for SQL
> 2000, however SQL 2005 does ship with one.
> Depending on your requirements you may be able to use SQL 2005 which is
> currently in beta 2.
> Otherwise you might be able to parse at the client to trap for I and
> replace
> it with I and I. So a search on Izmir would be expanded to Izmir OR Izmir
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Esref DURNA" <EsrefDURNA@.discussions.microsoft.com> wrote in message
> news:E3E505F6-756D-4209-8644-7803497690E4@.microsoft.com...
> either
> message,
> the
>

Character limit for variables inside a stocked procedure

I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)

I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.

I am looking for a "How to" to put this limit to 8000.

Thank you!

Try Varchar(MAX) . It should help you.|||

Hi , see this link

http://www.sqlmag.com/Articles/ArticleID/26654/pg/2/2.html

|||

How you insert data to your field? Are you use stored procedure or any type of parameter? check the size of your parameter if it is not limited to 1000 chars, I never had problems with varchar(8000) like you so check the way how you insert value to your cell.

Thanks

|||

The problem is with the SQL Server itself, it has no relation to the type of variable or any data passed to the sotred procedure. The number of character that a stored procedure variable CANNOT exceed 1000.

Thus, even if I do :

DECLARE @.SQL varchar(8000)

The @.SQL will not hold more than 1000 characters. And I need to fix that and cannot seem to find were to do so. 1000 character is fine for quite simple task, but we had some stocked procedure that would have required over 10K characters in order to do what we wanted to do.

If you have any idea on how to change the limitation on the number of character a variable within a stored procedure can hold, I am looking for it since it is quite limitating.

|||

Veritek:

The problem is with the SQL Server itself, it has no relation to the type of variable or any data passed to the sotred procedure. The number of character that a stored procedure variable CANNOT exceed 1000.

I believe you are mistaken.

Try this from Query Analyzer:

DECLARE @.test varchar(2000)
SELECT @.test = REPLICATE('1',1000) + REPLICATE('2',700)
PRINT LEN(@.test)
PRINT @.test

You will see that the length returned is 1700. And that the string printed contains both 1's and 2's.

Your problem lies elsewhere. Something else is truncating your data at 1000 characters.

|||Affirmative, length is indeed 1700... But then I do not know where I could look ...|||

Veritek:

Affirmative, length is indeed 1700... But then I do not know where I could look ...

Well, either do we since we haven't seen any code...

jpazgier has suggested that you review your parameters to make sure you are not truncating data before it gets to your stored procedure.

|||

Well, still unresolved, and wont be anytime soon now since I would seem to have a new problem with the server.

Since the stocked procedure is receiving data from an aspx/vb set of files. Even when the .vb is of size 0.

Reinstalling the softwares seem in order now...

Thank you tho for the help!

|||

Well, we actually had to disable the SP causing the problem since during the weekend it simply stoped working and kept returning an error which we fail to see where it comes from.

I think we need to upgrade our software :p

|||

Veritek:

Thus, even if I do :

DECLARE @.SQL varchar(8000)

The @.SQL will not hold more than 1000 characters.

How are you determining that @.SQL will not hold more than 1000 characters? Again, you've really not shown us any of your code so it's difficult for us to help. I strongly doubt that reinstalling software is the answer.

Character count limit?

Does anyone know what the character count limit is for a SQL table? We have a table with a column called body_text. It contains characters from a story we publish every day. Example:

News from Canada and Turkey

We get our weekly news update from

our Canadian correspondent, Vaughn Palmer. Plus, the Pope is in Turkey.

The visit is provoking opposition from secular nationalists and

Islamists. We talk to a reporter in Istanbul. Also, our weekly

listener's forum.

I did a word count in MS Word. This paragraph is 275 characters long. I found this in the only SQL book I currently have called Beginning SQL Server 2005. I found a snippet I thought might be related to my question.

Char

The char data type is fixed in length. If you define a

column to be 20 characters long, then 20 characters will be stored. If you

enter less then the number of characters defined, the remaining length will be

space filled to the right. Therefore, if a column were defined as char (10),

"aaa" would be stored as "aaa

". Use the data type when the column data is to be of fixed length,

which tends to be the case for customer IDs and bank account IDs.

So what is the official answer?

Assuming that your actual question is:

"Does anyone know what the character count limit is for a SQL table?"

In Sql Server 2k the limit for in-row-data is 8060. if you want to store more than that you will have to use a data type which is stored off-row like text.
In SQL 2k the limit for in row-data is also 8060, but you can take advantage of tnew data types which extend the VARCHAR /NVARCHAR types to a max of 4GB/2GB.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Cool thanks!

char vs. varchar

The company I'm contracting at has a guideline that table columns should be
of type char if less than 20 characters, otherwise varchar. This guideline
was just changed to a requirement. In my opinion, the choice between char an
d
varchar should consider variability of data size as well as need of
modification performance vs. read performance, and therefore shouldn't be
based on a fixed size. Any comments I could use to help my cause, or any
disagreement?
Thanks
Vern RabeVern Rabe wrote:

> In my opinion, the
> choice between char and varchar should consider variability of data
> size as well as need of modification performance vs. read
> performance, and therefore shouldn't be based on a fixed size. Any
> comments I could use to help my cause, or any disagreement?
I agree with you. When for example you got a FirstName field, there
are names from 3 chars till 18 (in an example DB). Why would you waste
the space by using char? I only use char when the column length is the
same for every row. Good luck convincing the company ;)
Kind regards,
Stijn Verrept.|||Vern Rabe wrote:
Another advantage of using varchars for non fixed length columns: when
the text entered in a char column is smaller than the size of that
column it will be padded to the correct length so you'll need to handle
this in your application or use trim queries.
Kind regards.|||I'd like to hear the company's rationale for this requirement but a length
of 20 characters seems a bit excessive to me. Data are typically read much
more often than written. Although inexpensive storage mitigates the need
for byte counting, I don't see how one can justify using a particular data
type before the schema or application is designed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:194CC9B9-0702-4E74-B3D2-602BA234DEA9@.microsoft.com...
> The company I'm contracting at has a guideline that table columns should
> be
> of type char if less than 20 characters, otherwise varchar. This guideline
> was just changed to a requirement. In my opinion, the choice between char
> and
> varchar should consider variability of data size as well as need of
> modification performance vs. read performance, and therefore shouldn't be
> based on a fixed size. Any comments I could use to help my cause, or any
> disagreement?
> Thanks
> Vern Rabe|||Char is for fixed width text while VarChar is for variable width text. If
the column is updated frequently, they may be concerned that changing the
length of data in a VarChar would result in page splits. However, this is a
very specific situation and would not justify using Char instead of VarChar
as a general rule. Find out who is responsible for defining database design
requirements, and ask them about it.
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:194CC9B9-0702-4E74-B3D2-602BA234DEA9@.microsoft.com...
> The company I'm contracting at has a guideline that table columns should
> be
> of type char if less than 20 characters, otherwise varchar. This guideline
> was just changed to a requirement. In my opinion, the choice between char
> and
> varchar should consider variability of data size as well as need of
> modification performance vs. read performance, and therefore shouldn't be
> based on a fixed size. Any comments I could use to help my cause, or any
> disagreement?
> Thanks
> Vern Rabe