Apply Your Thought On Char To Int

HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJYes - and the result may not be what you expect since char comparison are different from numeric comparisons - if in your example you are storing only numbers in a char field.|||Originally posted by rnealejr
Yes - and the result may not be what you expect since char comparison are different from numeric comparisons - if in your example you are storing only numbers in a char field.

THANKS FOR YOUR KIND REPLY.
I WANT TO STORE ONLY THE NUMBER IN DATABASE.
IF I USE CHAR DATATYPE INSTEAD OF INT AND ALSO USE INDEX ON THAT FIELD.
THEN WHAT HAPPEN.. IS THERE PERFORMANCE IMPACT ON QUERY OR NOT.

PLS I WANT TO CLEAR THAT ACTUALY I ALWAYS USE INT WHEN I WANT TO STORE NUMBERIC(INT) DATA.
ONE FELLOW TOLD ME THAT NEVER USE INT. ALWAYS USE CHAR. IT IS GOOD PRACTICE. OTHERWISE U WILL FACE SO MANY PROBLEM IN FEATURE. IS IT CORRECT?????

I REPLY HIM THAT ONLY BECAUSE OF PERFORMANCE I USE INT INSTEAD OF CHAR.

THANKS
DHIRAJ|||A lot of accounting systems treat numbers as text,, sortof. The numeric data is stored in char fields. One of the big differences as mentioned in a previous post is the sorting. As Int fields 1, 2, 3, 10, 100, 101, 1000 get sorted ascending in that order, if stored as char the same numbers would be sorted ascending as 1, 10, 100, 1000, 101, 2, 3 (I may have 101 and 1000 swapped but you can still see the difference). If you have to connect to an accounting system to update/retrieve data that might influence decisions on which datatype to use. Personally I prefer the int value.

append single character at specific position in string

Hi everyone, i am in s of help.
I have a variable (called status) of type char(10). This needs to be
extended to type char(11) and i need to append the character 'N' at this
position. For example, if 'status' is any of the following strings of type
char(10)
NYNYYN NY
NNYYY
Y
NNNY YYYNN
These must be converted to the following strings of type char(11) ..<note
the 'N' at the end of the string>:
NYNYYN NYN
NNYYY N
Y N
NNNY YYYNNN
Would anyone know how this can be done?
Any help most appreciated.
Kindest regards,
Sarah.Based on you narrative, you seems you can simply concatenate. For example:
DECLARE @.status char(10)
SELECT @.status = 'NYNYYN NY'
SELECT @.status + 'N'
Hope this helps.
Dan Guzman
SQL Server MVP
"sarah.clarke" <s.clarke@.nospam.com> wrote in message
news:eSgTNXqHGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hi everyone, i am in s of help.
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
> Would anyone know how this can be done?
> Any help most appreciated.
> Kindest regards,
> Sarah.
>|||First off, alter the table and change the column to char(11). That's the
easy part.
update MyTable
set status = left(isnull(status,'') + ' ', 10) + 'N'
Note: There are 10 spaces between the quotes.
What this statement does is to convert any null values to an empty string.
Then, it concatenates 10 spaces.
Then, it takes the left most 10 characters.
Finally, it concatenates the 'N' as the eleventh character.
If you want null values to remain null, simply remove the isnull function.
All the concatenation will return null and the row will still have a null
value in the status column.
Hope that helps,
Joe
"sarah.clarke" wrote:

> Hi everyone, i am in s of help.
> I have a variable (called status) of type char(10). This needs to be
> extended to type char(11) and i need to append the character 'N' at this
> position. For example, if 'status' is any of the following strings of type
> char(10)
> NYNYYN NY
> NNYYY
> Y
> NNNY YYYNN
> These must be converted to the following strings of type char(11) ..<note
> the 'N' at the end of the string>:
> NYNYYN NYN
> NNYYY N
> Y N
> NNNY YYYNNN
> Would anyone know how this can be done?
> Any help most appreciated.
> Kindest regards,
> Sarah.
>
>|||oops...my previous post was regarding a column
using a variable:
declare @.status char(11)
set @.status = 'A'
set @.status = left(@.status, 10) + 'N'
Hope that helps,
Joe

Apostrophe in char and varchar columns

HI all,
When trying to insert text into a char or varchar column that contains an
apostrophe ' I get an error message
"the value you entered is not consistant with the datatype or length of the
colum"
I know the length is not a problem, and I'm sure that ' is a char value.
How do I deal with this.
I need to import text type date, not long less thatn 100 char, from anothe
database into an sql table
Thanks
RobertOk figured it out. Makes perfect sense
Thanks
"Robert Bravery" <me@.u.com> wrote in message
news:eBAeJKrLGHA.536@.TK2MSFTNGP09.phx.gbl...
> HI all,
> When trying to insert text into a char or varchar column that contains an
> apostrophe ' I get an error message
> "the value you entered is not consistant with the datatype or length of
the
> colum"
> I know the length is not a problem, and I'm sure that ' is a char value.
> How do I deal with this.
> I need to import text type date, not long less thatn 100 char, from anothe
> database into an sql table
> Thanks
> Robert
>