Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Char Nchar Vchar Nvchar !?


Hello
in MS SQL 2000 for text it is possible ti use
char
nchar
varchar
nvarchar

what must be used and for what ?
I want to store normal text with occidental char (accents)

thank you


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Vchar Vs Char
HiI have a SQL database with approx., 90m rows. Within the tablethere several fields that are varchar 4. On a test server with approx.200k rows I converted those to char4.It showed that the database size shrunk by approx. 20 %, why I don'tunderstand.My question is would I get a performance improvement for selectqueries against those fields when they are char4 as opposed to varchar4 ?Thanks

View Replies !   View Related
Nchar, Varchar,char?
What is the difference between the above data types in SQL?  Which datatype should I use if I wanted both numbers and characters?

View Replies !   View Related
Conversion From Char To Nchar
Hello,

I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions?
Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode.
It would be great if you could help!
Thanks,
Sheetal.

View Replies !   View Related
Nchar Or Char Or Nvarchar Or Varchar???
Hi,

Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ???

Also, what really is the benefit of one over the over :confused:

Thanks

View Replies !   View Related
Datatypes Nchar/nvarchar Vs Char/varchar
can anybody please explain me why microsoft using nvarchar/nchar instead of varchar/char in northwind database and pubs database. I know if a column holds unicode data you should use nvarchar or nchar but for me all those tables in northwind/pubs are not holding unicode data. but still why microsoft settled for nchar/nvarchar.

View Replies !   View Related
Data Types (char, Varchar, Nchar, Nvarchar, ...)
Could someone please help me by explaining which one is best to use and when?  For example, storing the word "Corona Del Mar" - which Data Type would be suggested?
 Thanks.

View Replies !   View Related
Changing Char/NChar Column From NOTNULL To NULL
Hi I have a table, which contains Char and NChar NOT NULL columns
Now I need to change it to NULL, when I use the following command, it fails for the following error,

The command I used,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> CHAR NULL
ALTER TABLE <TableName>
ALTER COLUMN <ColName> NCHAR NULL


Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.

But for the same table, the below command executes fine,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> SMALLINT NULL


Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.

I need a script to accomplish this task.

Any help would be greatly appreciated.


-Senthil

View Replies !   View Related
Converting CHAR/VARCHAR/TEXT Into NCHAR/NVARCHAR/NTEXT!!!
Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz Ansari

View Replies !   View Related
This Stored Procedure Can Be Used To Search And Replace Substring In The Char, Nchar,
#1 This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all tables in the current database. You should pass the text value to search and the text value to replace. So, to replace all char, nchar, varchar and nvarchar columns which contain the substring 'John' with the substring 'Bill', you can use the following (in comparison with the SetTbColValues stored procedure, this stored procedure replace only substring, not the entire column's value):

EXEC replace_substring @search_value = 'John', @replace_value = 'Bill'

View Replies !   View Related
What's Difference About Datatype &"Char,VarChar,NChar,NVarChar In Sql 2000 ?
Hi All:
         I am new to Sql 2000 database,Now  I'm planing to create a table in my databse,my table included below fields like this :
       PoNo(the length is 15 characters) ,Supplier Name(the length is 50 characters).etc
      but I don't how to select the datatype for them. should I  select  Char or VarChar ?
      which one is the best slection ?
  thans in advanced!
 
 
         
    

View Replies !   View Related
Employing XML Formatted String Data Rather Than Normal String(char(), Nchar() Or Varchar() Values
Hello,
Is there a way of passing in an xml formatted string or text to the report through a data set and have the textbox or table in which it displays keep the formatting specified in the xml string rather than in the textbox properties?
 
Thanks.

View Replies !   View Related
Nvchar Fields Do Not Replicate
Hi

This is my first post !

I have a database running on SQL server.  It's setup as a publication and replicated to a subscriber by a pull subscription.

All my records seem to replicate accross - all new additions and deletions work fine as do most updates.

The problem is I have two fields - both declared as nvchar that do not replicate - they do not update when the published database changes.

Has anyone got any ideas what may be causing this.

thanks

View Replies !   View Related
Convert Vchar To Int
how to convert vchar value to int.
Thanks

View Replies !   View Related
VCHAR To XML After Table Is Load Via SSIS
 
I loaded a vchar column using script trans (SSIS doesn't support XML data types  :/)  to format the XML data properly.  Since the XML data in the vchar column was not encoded, when I try to alter the vchar column to xml after the table is loaded it fails.  I assume this is because the parser is having trouble with the content of the data within the xml tags. 
 
This is how I coded the XML rather than using a xml method.  "true" places the element end tag.
 

 

newXML += FormatElement("CONFERENCE") _

+ FormatElement("NAME") + ConvertToESC(RTrim(Mid(Row.activity, 1, 34))) _

+ FormatElement("NAME", True) _

+ FormatElement("START_DATE") + ConvertToESC(RTrim(Mid(Row.activity, 35, 8))) _

+ FormatElement("START_DATE", True) _

+ FormatElement("END_DATE") + ConvertToESC(RTrim(Mid(Row.activity, 43, 8))) _

+ FormatElement("END_DATE", True) _

+ FormatElement("CONFERENCE", True)
 
 
My question is, can I either load the data, using T-SQL, into an XML data type from a vchar column or is there a way to alter the type from vchar to xml without encountering the following parsing error?
 
 

Msg 9421, Level 16, State 1, Line 1

XML parsing: line 1, character 64, illegal name character
 
Thanks for any suggestions!
 
James
 

View Replies !   View Related
How To Substring From 12 Char To 8 Char Itemid
Hi,


alter PROCEDURE [dbo].[PPUpdateIWDetails]

(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate datetime
)
AS
BEGIN
By default iam passing 12 char itemid as parameter...

Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same)
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

View Replies !   View Related
Get LangId By Nchar(1)
Is it
possible in SQL 2005 to determine to which language symbol nchar(1) is
referred?



 
If
languages differ greatly, say, like Russian and English, can one write a function,
which, under the given alphabet, can determine to what language the symbol
belongs?



If the
languages have a similar alphabet, one is faced with the difficulties. For
example, it is needed to distinguish characters of American English from those
of Australian English or of English from those of any other European language
that uses almost the same alphabet. I don€™t know how to solve this problem.
What can you advise?



The final task
is to scan in Internet a great number of Web pages in various languages and to
make up a large dictionary, where each word will be entered in its language
group. It is in principle too important to select words in different languages
that use the same alphabet. Initial data for word selection will enter the
server in NTEXT format.      

Yuri,

View Replies !   View Related
Convert From Nchar To Money
Hi i've a staging table were i have datatypes of nchar.
I'd like to convert these to money datatype

Am trying to do this like this but the data return is not correct there is aslo null and blank rows so i must account for them also.

select cast(IsNumeric((Value2)) as money)
fromtbl_Sales

View Replies !   View Related
Conversion From Nchar To Numeric
Hi ,

I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).



I tried all the ways i can and still i get the same error..:


[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E07. An OLE
DB record is available. Source: "Microsoft OLE DB Provider
for SQL Server" Hresult: 0x80040E07 Description: "Error
converting data type nvarchar to numeric.".



Can we actually do it...?? any help would be appreciated.



thanks

ravi



Nothing much that i can do..!!

View Replies !   View Related
Nchar To Decimal..which Transformation
Hi,
I selected the columns which i need in the data source itself and i need 3 columns,one will direclty go to the destination without any change/transformation and about the other two i need to do few lookups.

When i try to do lookup for One column which has the datatype in the source as nchar(3) though the date in the column is a number(like 150,160,170 and so on), my lookup table(a dimension in DW) where i want to get the actual key by looking up with this number doesnt allow me to map to the dimension in the DW, as the Dimension has this column datatype as decimal (3,0)....i tried the datatype conversion to convert the nchar to decimal but it doesnt transform i dont know why...probably i am doing some mistake...Could someone suggest how to deal with this and which transformation to use..?
I'd really appreciate if someone on this forum suggest how to deal with this..?



thanks

ravi



Nothing much that i can do..!!

View Replies !   View Related
Cannot Convert From Nchar To Money
Hi i've a staging table were i have datatypes of nchar.
I'd like to convert these to money datatype

Am trying to do this like this but the data return is not correct there is aslo null and blank rows so i must account for them also.

select cast(IsNumeric((Value2)) as money)
from tbl_Sales

View Replies !   View Related
What Is The Meaning Of Nchar(0xFEFF)?
When we send a message in service broker we send nchar(0xFEFF) in the start of the xml file.

What is the meaning of nchar(0xFEFF)?

Thank you.

Bishoy

View Replies !   View Related
Severe Performance Hit With NCHAR Queries
Hey there :)Sorry if I'm asking a dumb question here, but I'm still quite new to MS SQL,so this problem might appear larger to me than it really is.I'm trying to create a performance test environment for a Ruby on Rails andMongrel setup with an MS SQL Server 2000.The adapter, mssqlclient, uses some kind of "conversion" for unicode, here'sa quote from the homepage:"Automatically translate from proper UTF-16LE nvarchar fields in thedatabase to UTF-8 Ruby Strings you can display in your application"As far as the local DB designer knows, we're not using UTF16-LE nvarcharfields, unless it's something that happens implicitly.Either way, this is how a query from the mssqlclient adapter might look:SELECT TOP 1 * FROM Item WHERE (Item.Itemnumber = N'45783745')Response time the first couple of times was upwards of 20+ seconds, afterthe sql server has "awaken from its slumber", it's roughly 4 seconds.Omitting the "N" from the WHERE clause, response time is in milliseconds (asone would expect, regardless of the fact that there's currently >2.5million items in the table).Any tips on how to resolve this? Is the SQL statement bad, or is it aquestion of configuring SQL Server correctly?Thanks in advance for any help,Daniel Buus :)--http://www.rhesusb.dk

View Replies !   View Related
Using REPLACE With NCHAR In A Stored Procedure
I'm using replace in a stored procedure to eliminate carriage returnsa user might have entered in an Access field.The problem is that even though it seems to strip out the carriagereturn I can't get rid of the "□" character.This is my code:DECLARE @myReturn nvarchar(1)SELECT @myReturn = NCHAR(10)REPLACE(UserAddress,@myReturn,' ') AS myTEXTFor example, the user has entered:123 Oak StreetSuite 101What I get after running the replace is: 123 Oak Street □ Suite101lq

View Replies !   View Related
Unicode Is Nvarchar, Ntext, Nchar
When I tried to insert armenian by doing the following
insert into tablex (field1) values (N'testdata')
it does not display in query analyzer or in the database as armenian.
When I copy this to word it does not convert it.

What else am I supposed to do to get that information to redisplay the correct way and I would appreciate any tutorials or samples you can show or direct me to.

Howard

View Replies !   View Related
Differences In Results Of Nchar() And Unicode() Fu
Hi,

I am working on an application that uses accent characters.

SELECT nchar(256) returns A. But if I do

SELECT Unicode(A) , then it returns 65 i.e. the value for simple 'A' character. I am confused, because I have data in nvarchar fields,

but when I do string comparison then A and A are treated as equal.

Please let me know, what could be the issue.



Thanks

View Replies !   View Related
Converting Nchar To Int (or Numeric/decimal)
Hi ,

  I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's  in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).

 

I tried all the ways i can and still i get the same error..:


[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
 An OLE DB error has occurred. Error code: 0x80040E07. An OLE
DB record is available.  Source: "Microsoft OLE DB Provider
for SQL Server"  Hresult: 0x80040E07  Description: "Error
converting data type nvarchar to numeric.".

 

Can we actually do it...?? any help would be appreciated.

 

thanks

ravi

View Replies !   View Related
How To Check Whether Nchar Columns Arabic Character Set?
Hi
 
How can I check whether an nchar column contains Arabic character set? The only allowed values that can go into this column are English and Arabic.
 
 

Thanks,
Salman Shehbaz.

View Replies !   View Related
NCHAR, NVARCHAR And NTEXT Through JDBC-ODBC Bridge
Hi,I was wondering if the unicode datatypes can be retrieved through thejdbc-odbc bridge using a java client, I am currently getting questionmarks from NCHAR and NVARCHAR fields, and numbers from my NTEXT field.The server is sql server 2000.Any assistance would be much appreciated.Regards,Turloch

View Replies !   View Related
Char(1) And Char(2) Take Same Space?
 I create two tables:
 
create table table1
(
   col1 char(1)
)

go
 
create table table2
(
   col2 char(2)
)

go
 
I add some records to two tables after createing operation completed.
 
Then i use dbcc page command to oversee the structures of  data page in two tables.
I found some interest things:
The rows in two tabes take up same space:9 bytes
 
You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE
or calculate from the offset array

 
 
Any  suggestions?

View Replies !   View Related
ERROR: Procedure Expects Parameter '@statement' Of Type 'ntext/nchar/nvarchar'.
/* INFO USED HERE WAS TAKEN FROM http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 */
DECLARE @X VARCHAR(10)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Num_Members SMALLINT
SELECT @X = 'x.dbo.v_NumberofMembers'
DECLARE @SQLString AS VARCHAR(500)

SET @SQLString = 'SELECT @Num_MembersOUT=Num_Members FROM @DB'
SET @ParmDefinition = '@Num_MembersOUT SMALLINT OUTPUT'


EXECUTE sp_executesql <-LINE 11
@SQLString,
@ParmDefinition,
@DB = @X,
@Num_MembersOUT = @Num_Members OUTPUT


Just Need Help On This Error
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.


I dont know why im getting a errrror b/c I followed http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 exactly

View Replies !   View Related
Bit Or Char?
Sql Server uses bit field for boolean while Access uses Y/N. What are the pros and cons of either way? Thanks.

View Replies !   View Related
Char To Bit
I am importing a table where I need to convert a char(1) with thevalues of 't' or 'f' into a bit field with valies of 1 or 0. Is therea built-in function that does that? I've been searching, but I can'tfind an answer.

View Replies !   View Related
What Does It Mean Char(13) And Char(10)
What does it mean char(13) and char(10) when I use this in my sql code

ali

View Replies !   View Related
Int Vs. Char(1)
Which does MS SQL server handle faster an int or a char(1)? In my shop we are designing a database for use on the web.
One column that will be accessed very often could be a char(1) or an int. I am not sure which is better and why.

View Replies !   View Related
Concatenate Int && Var Char - SQL
Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result  Thanks 

View Replies !   View Related
Char Vs. Varchar
Greetings,I have a question. I work on some SQL2k/ASP.NET apps at work. Mypredacessor, who created the databases/tables seemed to have liked touse 'char' for all text fields. Is there a reason why he would havedone this over using varchar? It's a minor annoyance to always have toRTRIM data and it makes directly making changes to the database moreannoying (with all the pointless trailing spaces)?I usually use char for fixed string lengths, like state abbreviationsor something, and varchar for strings of unknown length.Is it a performance issue? Our database doesn't do much traffic, forthe most part.

View Replies !   View Related
Int To Char Conversion
I have data tables that include ZIP code, as char(5). The values looklike integers, but they are padded with leading zeroes to fill out 5characters, '00234'.There are SPs to look up data, with @Zip char(5) among the parameters.Some users call these with integer values, @Zip = 234, and SQL makesthe conversion. Is it necesary to add the leading zeroes in the SP --@Zip=RIGHT('00000'+@Zip,5) -- or would SQL find this match? (234 ='00234'). It looks like the conversion is to '234' and the matchfails.Thanks,Jim GeissmanCountrywide

View Replies !   View Related
Char Type In MS SQL
Dear All,

I am new to MS SQL. One VB application that I need to maintain using it.
There is one problem reported but I couldn't explain: one field is declared
as Char (20), but the application seems able to insert a string with length
even 30. There is no character truncatd when retrieved neither.

However, I cannot simulate the case in the MSSQL database manager. When I executed a SQL script to insert, I was simply not allowed to do so.

Any explanation to the sDid anyone encounter same thing before?
Thanks for any hint.


Regards

View Replies !   View Related
Char Vs Varchar
Hi,
This question may sound silly,but please comment.
Please tell me a situation where char should be used and not varchar.
Let us assume that we are dealing with non unicode characters.
Well, I find varchar is always smarter than char, so why char?
Thanks!!
Rudra

View Replies !   View Related
Varchar And Char
i would like to know if there is an overhead in using VARCHAR when you use to store it...

a colleague of mine claims that if the field is defined to be VARCHAR the system creates and additional column DOUBLE/DECIMAL with storage size of 17/18bytes.

such that if the size of the varchar field is less than 30 it is better to be defined as CHAR instead.

please help me out here... i think there's something wrong with his statement, but i need concrete proof to it... a link to page or pdf file would be very much appreciated.

View Replies !   View Related
How Do I Use Char(xxx) In A LIKE-statement
I want to search several columns after a range of characters. How do I use the CHAR value in a LIKE-statment?

declare @i int
select @i = 0
while @i between 1 and 10
begin
select * from tab1 where col1 like char(@i) ???
(how do I use char here)
end

View Replies !   View Related
Char To Smalldatetime
SQL Server 2000

Used to know how to do this but am having no luck today. I have data coming in from a .txt file that gives me char strings for dates, e.g. 02242003 for Feb. 2, 2003.

Need to whomp this into small datetime with the whole convert/cast thing but I guess I've previously only gone the other way--smalldatetime to char.

Thanks!!!

View Replies !   View Related
Char Format
I have one table that has a column 'amount' with datatype 'numeric(19,5)'
I'm want to insert the data from this column into another column (in a different database) with this format: '00000000.00' as 'char'
example: If in the first column I have 800.75864 I want to insert it in the other column as '00000800.75'

any suggestions on how to make this conversion?

View Replies !   View Related
Varchar V Char
I have recently inherited a database where all of the tables use varchar instead of chars for fields. Very , very few of these fields are involved in keys of even indices, but performance is an issue. I thought that I had read that varchars are worse for performance than chars when page splits may occur. Is this related to updates only, or does it matter?

Any help appreciated.

View Replies !   View Related
Getting MAX From Char Numbers
I have a table which will recieve and interface file to be loaded. All the fields are there except an account_id field which is a char(12), but the data is a number 000000000001, 000000000002, etc. These numbers are to be incremented by one. I understand how to write the process using a cursor to run through all the records and to increment them before loading. My question is the best way to get the max number in the existing table which is the char(12) , and icrement the number by one, but retain the leading zeroes I will need. Anyone had the pleasure of doing this providing input greatly appreciated.

View Replies !   View Related
Char Vs Varchar
Hi,

Does any body know of any performance implications of using 'varchar' data type against 'char'?

I have some columns that are using 'char' data type, but the data in them is not fixed length. So, to gain some disk space I am planning to change the data type to 'varchar'. But, I am concerned if there will be any performance de-gradation or any other implications of doing this.

Regards
Chakri

View Replies !   View Related
Addition Of Char + Int !!!
Hi Everybody,

I hv executed the following query in the Query Qnalyzer
(Ofcourse I am Using SQL 2000 Enterprise Edition) and Surprisingly
I am getting the Output as follows,

query:-
select '5' + 10

Output:- 15

Can anybody please tell me, why it is happening like this? OR Did I miss any Configuration Parameter in the Server Settings?.

Tks in Advance,
Sam

View Replies !   View Related
Char (8) Vs. Varchar (8)
In relation to the code in this thread

http://forums.databasejournal.com/showthread.php?t=42622

My customer code field is Char (8) but accept an argument of Varchar (8) to my stored procedure. Don't ask me why!?!!

The customer code could be anything from 'A' to 'ZZZZZZZZ'.

Will this have any effect especially relating to overhead and retrieving incorrect data?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved