Data Type Issue
-
I'm using VB6 and found out after many hours that SQL Server introduced a bug into my program. Previously i had used Access, but now that i've switched my application does not read the words out of my DB the same.
In my code i have a for loop that searches for a AVI file type. The file type is stored in the DB. Well, the letters AVI are found in the variable but when i asked if word = "AVI", it says "NO!"
Looking at my DML, i used char(4) as the column datatype. How do i fix this problem?
Why do i have to convert the Datatypes of strings once it is outside SQL Server? It makes me wonder about other datatypes such as numbers.
I'm going to see if i can use a string conversion function in the meantime.
Thanks!
-
change to varchar(4) or like 'avi%'
-
is there a way to change the datatype without recreating the whole table or database?
I tried changing it in Enterprise manager but could not find a way.
-
ALTER TABLE TableName ADD NewColumnName varchar(4) NULL -- I am assuming the OriginalColumnName can contain NULLs.
GO
UPDATE TableName SET
NewColumnName = RTRIM( LTRIM( OriginalColumnName))
ALTER TABLE TableName DROP COLUMN OriginalColumnName
GO
Then you can change the NewColumnName to the OriginalColumnName in Enterprise Manager.
I wasn't born stupid - I had to study.
-
Since SQL Server will have no problem converting a char(4) to a varchar(4) including existing data, use:
ALTER TABLE TableName ALTER COLUMN
Name varchar(4) NOT NULL
UPDATE TableName SET Name = RTRIM(Name)
This last part is to insure the removal of the trailing space(s) created while a char(4) column.
This is the reason your Name = 'AVI' could not find 'AVI ', when in doubt, search BOL (Books on Line), in this case lookup the CHAR datatype and you will read that it right pads the value with the space character to the length of the column. So Name = 'Z' would need to be Name = 'Z '.
Andy
-
David's explanation of the need for RTRIM( LTRIM( )) is absolutely correct.
The reason I suggested a new column is, if anything goes wrong, you have the potential to loss or corrupt your original data. If you make a new column, (yes, it is a little more time consuming) but you have less chance of having to go to a backup to try and repair problems...
I wasn't born stupid - I had to study.
-
Thanks for your replies and explanations. They are very helpful.
Guys, a lot of times when i post to this site, i lose my posts. It happens often. I try to remember to copy before pressing post but i don't always remember.
I was just wondering which was the best practice. I dont want to be changing my DB structure too much to accommodate a programming language.
I guess the difference might be insignificant it is just 4 characters. But i was told that char was best because it telling the computer exactly how much space to allocate. Since in my opinion file types are typically no more than 4 characters long, it was best to use char instead of varchar. If i say it is insignicant i feel the same can be said of SSN or phone numbers.
What are you thoughts? Is char better or insignificant?
Thanks again guys!
-
If you require a specified number of characters, use char. Otherwise, varchar allows for more flexibility. I have often had jobs where "this will NEVER change!" And then, six months later it changes. Varchar offers the flexibility to allow for changes - char 'can' make for a bit more complicated matches as you saw with your match on AVI.
Just my $ 0.02.
I wasn't born stupid - I had to study.
-
As a developer and designer I almost never use the CHAR(X) data type except as Char(1), Varchar(X) is much better. Char(X) will always give you X characters even if you use less than X; Varchar(X) will only give you the N <= X characters you actually put into the column.
If for some reason you need to keep the column as a CHAR(X) then your VB6 would look something like this:
If UCASE(TRIM(Word)) = "AVI" then ...
as a Varchar(X) it would look like
if UCASE(Word) = "AVI" then ...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply