Combination of English and Chinese characters in Microsoft SQLServer 2000

Hello,
We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
one of our databases, we have a table REQ with one of the fields
COMMENT being defined as TEXT. Now, when we try to view the COMMENT
field, it shows us only English characters whereas the Chinese
characters are displayed as ?.
Is it possible to store both English and Chinese characters in the same
column of a table? If yes, then how?
Thanks in advance.
New user.
It still does not work. Is there any other alternative?
On Nov 19, 1:48 am, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
> one of our databases, we have a table REQ with one of the fields
> COMMENT being defined as TEXT. Now, when we try to view the COMMENT
> field, it shows us only English characters whereas the Chinese
> characters are displayed as ?.
> Is it possible to store both English and Chinese characters in the same
> column of a table? If yes, then how?
Yes, it's possible, but with the information you have given, it's difficult
to give exact advice.
First of all, which character set do you use for Chinese? Unicode? Big-5?
If you use Unicode, you should use ntext, if you use Big-5 or any other
non-Unicode character set, you should use text.
Next question is find out where things go wrong. Do something like:
SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
If there are question marks in the selected substring which should have
been Chinese characters, what do you see in their place in the binary
string? If you see 3F, the Chinese characters have been stored as ?, and
there is a problem on input. If you see the codes for the Chinese
characters, it's a display problem.
If it is an input problem, it would help to know who the characters enters
the database.
Disclaimer: I have no experience of working with Chinese on my own.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I ran the query as u suggested and the output for the binary string
shows as 3F for the Chinese characters. If that means there is a
problem in input, how do I go ahead from here?
On Nov 21, 9:17 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> to give exact advice.
> First of all, which character set do you use for Chinese? Unicode? Big-5?
> If you use Unicode, you should use ntext, if you use Big-5 or any other
> non-Unicode character set, you should use text.
> Next question is find out where things go wrong. Do something like:
> SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
> If there are question marks in the selected substring which should have
> been Chinese characters, what do you see in their place in the binary
> string? If you see 3F, the Chinese characters have been stored as ?, and
> there is a problem on input. If you see the codes for the Chinese
> characters, it's a display problem.
> If it is an input problem, it would help to know who the characters enters
> the database.
> Disclaimer: I have no experience of working with Chinese on my own.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> I ran the query as u suggested and the output for the binary string
> shows as 3F for the Chinese characters. If that means there is a
> problem in input, how do I go ahead from here?
To start with, please answer a few questions:
1) How does the data enter the database? From a Windows client? From a
web client? Which API do you use?
2) Which character set does the client use for the data?
3) What is the collation of the text column? (You can view this with
sp_help.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thank you very much for all your help. The problem has been solved.
After changing the field from text to ntext in the database, the client
still had some problems. Even though East Asian languages were
supported, the Language for non-Unicode programs was English. When it
was changed to Chinese and the PC was rebooted, everything is all fine
now.
On Nov 26, 1:15 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> 1) How does the data enter the database? From a Windows client? From a
> web client? Which API do you use?
> 2) Which character set does the client use for the data?
> 3) What is the collation of the text column? (You can view this with
> sp_help.)
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column space utilization

Hello

I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?

Thanking You
NavaneethIt will only use the space for your text.|||Thank you marcel,

I am planning to implement search functionality for my website. So choosing which datatype for search keywords will be optimum for me ? I need to store large number of data in this filed. I planned for text type. Is it a good choice ? Will text type utilize more space than any other datatype ? How much big my SQL database can grow ?|||You might was to look into using Full Text Search. Search in Book On Line for this topic.

Thanks,|||

Hello!

Moving forward you should NOT USE TEXT data types. TEXT has been obsoleted. VARCHAR(MAX) is the replacement.

Charles Hyman

Senior Consultant

MCTS SQL Server 2005

MCTS Biztalk Server

MCTS Vista Config

MCITP Database Administration

MCITP Database Developer

TALLAN Inc.

www.tallan.com

|||Thank you,

But upto my knowledge I think varchar can store only 8000 characters ? In new versions is it increased ?|||varchar(max) can store as much information as text. (so upto 2GB)

Thanks,

column length reducing

The length of a column is 20(varchar),
When i m trying to execute select column name it gives all 20 characters.
My requirement is - is there any option by which i will be able to see only 10 characters ?select left(10,columnname)|||umm...think you go t it backwards...|||nope it's an anagram|||Originally posted by Karolyn
nope it's an anagram

Is anagram an alias for incorrect syntax:D

USE Northwind
GO

--SELECT LEFT(10,LastName) FROM Employees

SELECT LEFT(LastName,10) FROM Employees
GO|||My Bad !!!

Can blame it on another long day perhaps

column Length Help

I have a table this is populated by a flat file. In the desc field from the flat file some of the data is very long up to 150 characters in length. I have to export this data after some transformations to a fixed width file. I need to check the length of the data in the desc field and any thing that is to long needs to be put in a desc2 field, however any values in desc that are shorter than 36 characters in length needs to have spaces padding it up to 36 characters as well as the desc2 field. Any help would be great. I am not the best T-SQL programmer in the world and have limited time to get this done.

well, I think you can just do (I might be missing something, of course....):

select cast(substring (desc,1,36) + replicate(36,'') as char(36)) as desc,

cast(substring (desc,37,114) + replicate(114,'') as char(114)) as desc2

from sourceTable

Not sure why you would want to pad the data, that is a tremendous waste of space, generally

|||

Here it is,

Code Snippet

create table imported

(

id int,

desc1 varchar(36),

desc2 varchar(36)

);

Go

create table #T

(

id int,

longdesc varchar(1000)

)

BULK INSERT #T

FROM 'c:\flat\flat.txt'

WITH

(

FIELDTERMINATOR = ' ',

ROWTERMINATOR = '\n'

)

Insert Into imported

select

id,

cast(Substring(longdesc ,1,36) as char(36)),

cast(case when len(longdesc) >36 Then Substring(longdesc,37,36) Else '' End as char(36))

from

#T

drop table #T

select *, len(desc1+ '.')-1, len(desc2+ '.')-1 from imported

|||

The padding for the file is for a really old system that we use. We are looking to replace this soon. So we are converting from one old system to another. I worked out this solution.

SELECT TOP 500

CONVERT( char(8), rtrim( ltrim( bi.cusip))) + ',' + -- as cusip,

CASE WHEN LEN( bi.[Security Name]) <= 36

THEN CONVERT( char(36), bi.[Security Name])

ELSE CONVERT( char(36), LEFT( bi.[Security Name], 36))

END + ',' + -- AS 'Description1'

CASE WHEN LEN( bi.[Security Name]) > 36

THEN CONVERT( char(36), SUBSTRING( bi.[Security Name], 37, 36))

ELSE CONVERT( char(36), '')

END + ',' + -- AS [Description 2],

CONVERT( char(36), '') + ',' + -- [Description 3]

CONVERT( char(36), '') + ',' + -- [Description 4]

CONVERT( char(6), LEFT( RTRIM( LTRIM( REPLACE( bi.Ticker, '.', ''))), 6)) + ',' +

CONVERT( char(3), LEFT( s.[Classification Code], 3)) + ',' + -- as [ClASsification Code]

CONVERT( char(4), LEFT( RTRIM( LTRIM( s.[industry code])), 4)) + ',' + -- [industry code]

CONVERT( char(3), LEFT( RTRIM( LTRIM( s.[tax code])),3)) + ',' + --[TAX CODE]

CONVERT( char(1),LEFT( RTRIM( LTRIM('E')), 1)) + ',' +

CONVERT( char(1),LEFT( RTRIM( LTRIM( 'A')),1)) + ',' +

CONVERT( char(13),LEFT( LTRIM( RTRIM( ISNULL( bi.[Income Rate],''))),13))+ ',' +

CONVERT( char(8), ISNULL( REPLACE( CONVERT( varchar, bi.maturity, 101), '/', ''), '')) + ',' + -- Maturity Date

CONVERT( char(4), ISNULL( ifq.Sunguard, '')) + ',' + -- Income Frequency

CONVERT( char(4), '') + ',' + -- Quality Rating

CONVERT( char(1), '') -- Accrual Method

--INTO tblSentSecurties

FROM dbo.mytable bi

LEFT JOIN dbo.Sl2 s on s.cusip = bi.cusip

LEFT JOIN dbo.tblIFreq ifq

ON ifq.AxysFCDate = CONVERT( varchar(2), DATEPART( month, REPLACE( bi.maturity, '?', ''))) + '/' + CONVERT( varchar(2), DATEPART( day, REPLACE( bi.maturity, '?', '')))

AND ifq.AxysCF = REPLACE( bi.Freq, '?', '')

|||

Thank you I will give this a try and compare the performance. This needs to only be run once a day.

See above post to see my solution.

column Length Help

I have a table this is populated by a flat file. In the desc field from the flat file some of the data is very long up to 150 characters in length. I have to export this data after some transformations to a fixed width file. I need to check the length of the data in the desc field and any thing that is to long needs to be put in a desc2 field, however any values in desc that are shorter than 36 characters in length needs to have spaces padding it up to 36 characters as well as the desc2 field. Any help would be great. I am not the best T-SQL programmer in the world and have limited time to get this done.

well, I think you can just do (I might be missing something, of course....):

select cast(substring (desc,1,36) + replicate(36,'') as char(36)) as desc,

cast(substring (desc,37,114) + replicate(114,'') as char(114)) as desc2

from sourceTable

Not sure why you would want to pad the data, that is a tremendous waste of space, generally

|||

Here it is,

Code Snippet

create table imported

(

id int,

desc1 varchar(36),

desc2 varchar(36)

);

Go

create table #T

(

id int,

longdesc varchar(1000)

)

BULK INSERT #T

FROM 'c:\flat\flat.txt'

WITH

(

FIELDTERMINATOR = ' ',

ROWTERMINATOR = '\n'

)

Insert Into imported

select

id,

cast(Substring(longdesc ,1,36) as char(36)),

cast(case when len(longdesc) >36 Then Substring(longdesc,37,36) Else '' End as char(36))

from

#T

drop table #T

select *, len(desc1+ '.')-1, len(desc2+ '.')-1 from imported

|||

The padding for the file is for a really old system that we use. We are looking to replace this soon. So we are converting from one old system to another. I worked out this solution.

SELECT TOP 500

CONVERT( char(8), rtrim( ltrim( bi.cusip))) + ',' + -- as cusip,

CASE WHEN LEN( bi.[Security Name]) <= 36

THEN CONVERT( char(36), bi.[Security Name])

ELSE CONVERT( char(36), LEFT( bi.[Security Name], 36))

END + ',' + -- AS 'Description1'

CASE WHEN LEN( bi.[Security Name]) > 36

THEN CONVERT( char(36), SUBSTRING( bi.[Security Name], 37, 36))

ELSE CONVERT( char(36), '')

END + ',' + -- AS [Description 2],

CONVERT( char(36), '') + ',' + -- [Description 3]

CONVERT( char(36), '') + ',' + -- [Description 4]

CONVERT( char(6), LEFT( RTRIM( LTRIM( REPLACE( bi.Ticker, '.', ''))), 6)) + ',' +

CONVERT( char(3), LEFT( s.[Classification Code], 3)) + ',' + -- as [ClASsification Code]

CONVERT( char(4), LEFT( RTRIM( LTRIM( s.[industry code])), 4)) + ',' + -- [industry code]

CONVERT( char(3), LEFT( RTRIM( LTRIM( s.[tax code])),3)) + ',' + --[TAX CODE]

CONVERT( char(1),LEFT( RTRIM( LTRIM('E')), 1)) + ',' +

CONVERT( char(1),LEFT( RTRIM( LTRIM( 'A')),1)) + ',' +

CONVERT( char(13),LEFT( LTRIM( RTRIM( ISNULL( bi.[Income Rate],''))),13))+ ',' +

CONVERT( char(8), ISNULL( REPLACE( CONVERT( varchar, bi.maturity, 101), '/', ''), '')) + ',' + -- Maturity Date

CONVERT( char(4), ISNULL( ifq.Sunguard, '')) + ',' + -- Income Frequency

CONVERT( char(4), '') + ',' + -- Quality Rating

CONVERT( char(1), '') -- Accrual Method

--INTO tblSentSecurties

FROM dbo.mytable bi

LEFT JOIN dbo.Sl2 s on s.cusip = bi.cusip

LEFT JOIN dbo.tblIFreq ifq

ON ifq.AxysFCDate = CONVERT( varchar(2), DATEPART( month, REPLACE( bi.maturity, '?', ''))) + '/' + CONVERT( varchar(2), DATEPART( day, REPLACE( bi.maturity, '?', '')))

AND ifq.AxysCF = REPLACE( bi.Freq, '?', '')

|||

Thank you I will give this a try and compare the performance. This needs to only be run once a day.

See above post to see my solution.