|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Storing URLs in Sql Server - Beginner - Bad to use ANSI_PADDING OFF?Hi.
My database app stores URLs in the database. I noticed that if I use nvarchar I wind up with leading spaces. I read around the forum and realize I should use varchar. The only problem is that when I serve the pages, the URLs have extra characters: e.g. webpage/page.html%20%20. This behavior I know is from the leading spaces. I looked intoo using ANSI_PADDING OFF but microsoft's web site states that this command is being discontinued and should not be used. Should I use a text datatype instead to avoid trailing spaces? Thanks! You don't want to use text. Use text only when you have to, basically only
when the data in one row can be more than about 8000 characters if you are using varchar or 4000 characters if you are using nvarchar - and I hope your URL's are not that long <grin>. You are right, you don't want to set ANSI_PADDING OFF, that is very likely to cause you problems in the future even if it works now. And the choice of varchar or nvarchar has nothing to do with leading or trailing blanks. They both treat spaces in the same manner. Chosing varchar or nvarchar should be made based on how much you need Unicode data in your application (both now and in the future). Read up on Unicode data in BOL. One thing that was confusing to me is you are talking about "leading" spaces - which are spaces at the beginning of a value, but your example is "webpage/page.html%20%20" which looks like trailing spaces - spaces at the end of a value. In any case SQL Server has functions to deal with either leading or trailing spaces or both. For the rest of this discussion, I'm going to use char and varchar, but if you decide to use nchar and nvarchar, the same discussion would apply. There are two SQL Functions which will help you LTrim (which removes leading spaces) and RTrim (which removes trailing spaces), and you can use them both together, like RTrim(LTrim(MyValue)) which will remove both the leading and trailing spaces from MyValue, BUT you must store the result into a varchar, if you store it into a char SQL Server will add back trailing spaces to fill out the number of characters defined for that column or local variable. Try running the following script: Set NoCount On Declare @Char char(6) Set @Char = ' ABC' Select 'Test1', @Char + 'X', DataLength(@Char) Select @Char = LTrim(RTrim(@Char)) Select 'Test2', @Char + 'X', DataLength(@Char) Declare @Varchar varchar(6) Select @VarChar = @Char Select 'Test3', @VarChar + 'X', DataLength(@Varchar) Select @VarChar = LTrim(RTrim(@Char)) Select 'Test4', @VarChar + 'X', DataLength(@Varchar) You will see that for Test1, we set @Char to a leading space followed by ABC. But since it is char(6), SQL Server added two blanks at the end to make it 6 characters. Then for Test2, we did a LTrim and RTrim of @Char which did remove the leading and trailing spaces, but then we put the result back in @Char, so SQL Server added 3 trailing spaces to make it 6 characters. In Test3, we set @Varchar to the value of @Char, but since @Char already had the three trailing spaces, and we made @Varchar equal to @Char, @Varchar got the three trailing spaces and it did not give us the result we wanted. Finally, in Test 4, we did it correctly, doing a LTrim and RTrim of @Char and storing that result in @Varchar and we see that @Varchar is now only 3 characters long and no longer has the trailing spaces. Tom Show quote "Ranginald" <davidw***@gmail.com> wrote in message news:1156562643.634255.268570@m79g2000cwm.googlegroups.com... > Hi. > > My database app stores URLs in the database. I noticed that if I use > nvarchar I wind up with leading spaces. I read around the forum and > realize I should use varchar. The only problem is that when I serve > the pages, the URLs have extra characters: > > e.g. > > webpage/page.html%20%20. > > This behavior I know is from the leading spaces. > I looked intoo using ANSI_PADDING OFF but microsoft's web site states > that this command is being discontinued and should not be used. > > Should I use a text datatype instead to avoid trailing spaces? > > Thanks! > Thanks so much for your help. I have a better understanding now.
So once I put the URL data in the varchar field, I should I 1) just trim the data ahead of time (e.g. so it is trimmed as soon as it is entered into the database) or 2) should my stored procedure that calls the data also run a trim function Thanks again! -Ranginald Tom Cooper wrote: Show quote > You don't want to use text. Use text only when you have to, basically only > when the data in one row can be more than about 8000 characters if you are > using varchar or 4000 characters if you are using nvarchar - and I hope your > URL's are not that long <grin>. > > You are right, you don't want to set ANSI_PADDING OFF, that is very likely > to cause you problems in the future even if it works now. > > And the choice of varchar or nvarchar has nothing to do with leading or > trailing blanks. They both treat spaces in the same manner. Chosing > varchar or nvarchar should be made based on how much you need Unicode data > in your application (both now and in the future). Read up on Unicode data > in BOL. > > One thing that was confusing to me is you are talking about "leading" > spaces - which are spaces at the beginning of a value, but your example is > "webpage/page.html%20%20" which looks like trailing spaces - spaces at the > end of a value. > > In any case SQL Server has functions to deal with either leading or trailing > spaces or both. > > For the rest of this discussion, I'm going to use char and varchar, but if > you decide to use nchar and nvarchar, the same discussion would apply. > > There are two SQL Functions which will help you LTrim (which removes leading > spaces) and RTrim (which removes trailing spaces), and you can use them both > together, like RTrim(LTrim(MyValue)) which will remove both the leading and > trailing spaces from MyValue, BUT you must store the result into a varchar, > if you store it into a char SQL Server will add back trailing spaces to fill > out the number of characters defined for that column or local variable. > > Try running the following script: > > Set NoCount On > Declare @Char char(6) > Set @Char = ' ABC' > Select 'Test1', @Char + 'X', DataLength(@Char) > Select @Char = LTrim(RTrim(@Char)) > Select 'Test2', @Char + 'X', DataLength(@Char) > Declare @Varchar varchar(6) > Select @VarChar = @Char > Select 'Test3', @VarChar + 'X', DataLength(@Varchar) > Select @VarChar = LTrim(RTrim(@Char)) > Select 'Test4', @VarChar + 'X', DataLength(@Varchar) > > You will see that for Test1, we set @Char to a leading space followed by > ABC. But since it is char(6), SQL Server added two blanks at the end to > make it 6 characters. Then for Test2, we did a LTrim and RTrim of @Char > which did remove the leading and trailing spaces, but then we put the result > back in @Char, so SQL Server added 3 trailing spaces to make it 6 > characters. In Test3, we set @Varchar to the value of @Char, but since > @Char already had the three trailing spaces, and we made @Varchar equal to > @Char, @Varchar got the three trailing spaces and it did not give us the > result we wanted. Finally, in Test 4, we did it correctly, doing a LTrim > and RTrim of @Char and storing that result in @Varchar and we see that > @Varchar is now only 3 characters long and no longer has the trailing > spaces. > > Tom > > "Ranginald" <davidw***@gmail.com> wrote in message > news:1156562643.634255.268570@m79g2000cwm.googlegroups.com... > > Hi. > > > > My database app stores URLs in the database. I noticed that if I use > > nvarchar I wind up with leading spaces. I read around the forum and > > realize I should use varchar. The only problem is that when I serve > > the pages, the URLs have extra characters: > > > > e.g. > > > > webpage/page.html%20%20. > > > > This behavior I know is from the leading spaces. > > I looked intoo using ANSI_PADDING OFF but microsoft's web site states > > that this command is being discontinued and should not be used. > > > > Should I use a text datatype instead to avoid trailing spaces? > > > > Thanks! > > In an ideal world I would definitely pick number 1). Having the trailing
blanks in my database not only means my database is larger than it should be, but I have bad data in my database which I never want. Finally, it's probably more efficient, I only have to write the data once, but may select it many times. But in the real world, I might find myself picking number 2). If I already had a working databasse, except that I was getting the extra blanks and I wanted to use number 1), I would not only need to change the way I inserted new data, but also correct the data which was already in the database. If it turned out that this change broke something else, undoing 2) would be easy, just put back the old version of the stored procedure, undoing 1) would be more work. But the best choice is definitely number 1) with adquate testing of the change before it goes into production. Tom Show quote "Ranginald" <davidw***@gmail.com> wrote in message news:1156629544.202323.314040@m73g2000cwd.googlegroups.com... > Thanks so much for your help. I have a better understanding now. > > So once I put the URL data in the varchar field, I should I > > 1) just trim the data ahead of time (e.g. so it is trimmed as soon as > it is entered into the database) > > or > > 2) should my stored procedure that calls the data also run a trim > function > > Thanks again! > -Ranginald > > Tom Cooper wrote: >> You don't want to use text. Use text only when you have to, basically >> only >> when the data in one row can be more than about 8000 characters if you >> are >> using varchar or 4000 characters if you are using nvarchar - and I hope >> your >> URL's are not that long <grin>. >> >> You are right, you don't want to set ANSI_PADDING OFF, that is very >> likely >> to cause you problems in the future even if it works now. >> >> And the choice of varchar or nvarchar has nothing to do with leading or >> trailing blanks. They both treat spaces in the same manner. Chosing >> varchar or nvarchar should be made based on how much you need Unicode >> data >> in your application (both now and in the future). Read up on Unicode >> data >> in BOL. >> >> One thing that was confusing to me is you are talking about "leading" >> spaces - which are spaces at the beginning of a value, but your example >> is >> "webpage/page.html%20%20" which looks like trailing spaces - spaces at >> the >> end of a value. >> >> In any case SQL Server has functions to deal with either leading or >> trailing >> spaces or both. >> >> For the rest of this discussion, I'm going to use char and varchar, but >> if >> you decide to use nchar and nvarchar, the same discussion would apply. >> >> There are two SQL Functions which will help you LTrim (which removes >> leading >> spaces) and RTrim (which removes trailing spaces), and you can use them >> both >> together, like RTrim(LTrim(MyValue)) which will remove both the leading >> and >> trailing spaces from MyValue, BUT you must store the result into a >> varchar, >> if you store it into a char SQL Server will add back trailing spaces to >> fill >> out the number of characters defined for that column or local variable. >> >> Try running the following script: >> >> Set NoCount On >> Declare @Char char(6) >> Set @Char = ' ABC' >> Select 'Test1', @Char + 'X', DataLength(@Char) >> Select @Char = LTrim(RTrim(@Char)) >> Select 'Test2', @Char + 'X', DataLength(@Char) >> Declare @Varchar varchar(6) >> Select @VarChar = @Char >> Select 'Test3', @VarChar + 'X', DataLength(@Varchar) >> Select @VarChar = LTrim(RTrim(@Char)) >> Select 'Test4', @VarChar + 'X', DataLength(@Varchar) >> >> You will see that for Test1, we set @Char to a leading space followed by >> ABC. But since it is char(6), SQL Server added two blanks at the end to >> make it 6 characters. Then for Test2, we did a LTrim and RTrim of @Char >> which did remove the leading and trailing spaces, but then we put the >> result >> back in @Char, so SQL Server added 3 trailing spaces to make it 6 >> characters. In Test3, we set @Varchar to the value of @Char, but since >> @Char already had the three trailing spaces, and we made @Varchar equal >> to >> @Char, @Varchar got the three trailing spaces and it did not give us the >> result we wanted. Finally, in Test 4, we did it correctly, doing a LTrim >> and RTrim of @Char and storing that result in @Varchar and we see that >> @Varchar is now only 3 characters long and no longer has the trailing >> spaces. >> >> Tom >> >> "Ranginald" <davidw***@gmail.com> wrote in message >> news:1156562643.634255.268570@m79g2000cwm.googlegroups.com... >> > Hi. >> > >> > My database app stores URLs in the database. I noticed that if I use >> > nvarchar I wind up with leading spaces. I read around the forum and >> > realize I should use varchar. The only problem is that when I serve >> > the pages, the URLs have extra characters: >> > >> > e.g. >> > >> > webpage/page.html%20%20. >> > >> > This behavior I know is from the leading spaces. >> > I looked intoo using ANSI_PADDING OFF but microsoft's web site states >> > that this command is being discontinued and should not be used. >> > >> > Should I use a text datatype instead to avoid trailing spaces? >> > >> > Thanks! >> > >  |
|||||||||||||||||||||||
Other interesting topics