|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Adding a space in a fieldA workaround to fix an application issue requires to insert a space in an
Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer seems do not fix the issue. This Address field is varchar and allows nulls. I'm not sure if I hit the space bar in the Default Value line under the Address field in Enterprise Manager a space will be inserted or not. Any advice will be appreciated. Eli Eli Feng wrote:
> A workaround to fix an application issue requires to insert a space in an Perhaps this will explain why SET Address = ' ' doesn't work?> Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer > seems do not fix the issue. This Address field is varchar and allows nulls. > I'm not sure if I hit the space bar in the Default Value line under the > Address field in Enterprise Manager a space will be inserted or not. Any > advice will be appreciated. Eli > > DECLARE @test TABLE ( field VARCHAR(10) NULL ) INSERT INTO @test (field) VALUES ('') INSERT INTO @test (field) VALUES (' ') INSERT INTO @test (field) VALUES (' ') INSERT INTO @test (field) VALUES (' x') INSERT INTO @test (field) VALUES ('x ') SELECT field, LEN(field) FROM @test You did not say where you want to insert the space.
A varchar field will not allow a trailing space. Here is a workarround for ur issue..
http://groups.google.com/group/borland.public.delphi.database.sqlservers/browse_thread/thread/c1480d2ac541a27c/aa1bc94560029abe%23aa1bc94560029abe Takecare Eli Feng wrote: Show quote > A workaround to fix an application issue requires to insert a space in an > Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer > seems do not fix the issue. This Address field is varchar and allows nulls. > I'm not sure if I hit the space bar in the Default Value line under the > Address field in Enterprise Manager a space will be inserted or not. Any > advice will be appreciated. Eli That's going to be a difficult one. As you can see from this example, varchar is designed to drop trailing spaces/blanks.
SET NOCOUNT ON CREATE TABLE #MyTable ( RowID int IDENTITY , TestField varchar(20) ) INSERT INTO #MyTable VALUES ( '' ) -- Empty String INSERT INTO #MyTable VALUES ( ' ' ) -- One space INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces INSERT INTO #MyTable VALUES ( 'a ' ) -- One space INSERT INTO #MyTable VALUES ( 'a ' ) -- Three INSERT INTO #MyTable VALUES ( 'a ' ) -- Four INSERT INTO #MyTable VALUES ( space(1) ) -- Two SELECT RowID , TestField FROM #MyTable WHERE TestField = space(1) -- Two Spaces qualify (Rows 2, 7) -- Would be nice for you if only Row 1 SELECT RowID , len( TestField ) -- len() = 0 = NO spaces FROM #MyTable DROP TABLE #MyTable What quality of data are you attempting to capture? NULL for 'Unknown', Empty String for 'Nothing'. Give us more information and perhaps we can help you come up with a solution that works for you. -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Eli Feng" <ef***@kerisys.com> wrote in message news:%23mPU1QCrGHA.1976@TK2MSFTNGP04.phx.gbl... >A workaround to fix an application issue requires to insert a space in an > Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer > seems do not fix the issue. This Address field is varchar and allows nulls. > I'm not sure if I hit the space bar in the Default Value line under the > Address field in Enterprise Manager a space will be inserted or not. Any > advice will be appreciated. Eli > > Thank you all for your quick responses and I'm sorry for not making it clearer. This Address field is null by default. I'd like to insert a space in this field if this field is null. This is what I was told to do. The query looks like this:
Update TableA set Address1 = ' ' where Type = 'P' and where Address1 is null Best regards, Eli "Arnie Rowland" <ar***@1568.com> wrote in message news:OH7wHdCrGHA.516@TK2MSFTNGP05.phx.gbl... That's going to be a difficult one. As you can see from this example, varchar is designed to drop trailing spaces/blanks.SET NOCOUNT ON CREATE TABLE #MyTable ( RowID int IDENTITY , TestField varchar(20) ) INSERT INTO #MyTable VALUES ( '' ) -- Empty String INSERT INTO #MyTable VALUES ( ' ' ) -- One space INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces INSERT INTO #MyTable VALUES ( 'a ' ) -- One space INSERT INTO #MyTable VALUES ( 'a ' ) -- Three INSERT INTO #MyTable VALUES ( 'a ' ) -- Four INSERT INTO #MyTable VALUES ( space(1) ) -- Two SELECT RowID , TestField FROM #MyTable WHERE TestField = space(1) -- Two Spaces qualify (Rows 2, 7) -- Would be nice for you if only Row 1 SELECT RowID , len( TestField ) -- len() = 0 = NO spaces FROM #MyTable DROP TABLE #MyTable What quality of data are you attempting to capture? NULL for 'Unknown', Empty String for 'Nothing'. Give us more information and perhaps we can help you come up with a solution that works for you. -- Arnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "Eli Feng" <ef***@kerisys.com> wrote in message news:%23mPU1QCrGHA.1976@TK2MSFTNGP04.phx.gbl... >A workaround to fix an application issue requires to insert a space in an > Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer > seems do not fix the issue. This Address field is varchar and allows nulls. > I'm not sure if I hit the space bar in the Default Value line under the > Address field in Enterprise Manager a space will be inserted or not. Any > advice will be appreciated. Eli > > Eli Feng wrote:
> Thank you all for your quick responses and I'm sorry for not making it The point that we're trying to make is that a VARCHAR field cannot > clearer. This Address field is null by default. I'd like to insert a > space in this field if this field is null. This is what I was told to > do. The query looks like this: > > Update TableA set Address1 = ' ' where Type = 'P' and where Address1 is null > > Best regards, > Eli > contain a blank space. I'm assuming you're trying to do this as a band-aid fix for an application that can't properly deal with an empty or NULL field. This isn't going to fix that problem. You're going to have to fix the app, or stick some other character into that field, such as a period '.' Thanks Tracy. Appreciate it. I'll give it a try with a period '.'
Eli Show quote "Tracy McKibben" <tr***@realsqlguy.com>. wrote in message news:ebXn5wCrGHA.2108@TK2MSFTNGP03.phx.gbl... > Eli Feng wrote: > > Thank you all for your quick responses and I'm sorry for not making it > > clearer. This Address field is null by default. I'd like to insert a > > space in this field if this field is null. This is what I was told to > > do. The query looks like this: > > > > Update TableA set Address1 = ' ' where Type = 'P' and where Address1 is null > > > > Best regards, > > Eli > > > > The point that we're trying to make is that a VARCHAR field cannot > contain a blank space. > > I'm assuming you're trying to do this as a band-aid fix for an > application that can't properly deal with an empty or NULL field. This > isn't going to fix that problem. You're going to have to fix the app, > or stick some other character into that field, such as a period '.' > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Are you by chance doing this so that an HTML table doesn't loose its borders
when the field contents are null? I've ran accross this hundreds of times, and there are ways to go about it without having to populate with an empty space. Just curiuos - if you elaborated a bit more on what you were trying to accomplish, you might get more focused answers that may help you out. Show quote "Eli Feng" <ef***@kerisys.com> wrote in message news:O1cF81CrGHA.928@TK2MSFTNGP04.phx.gbl... > Thanks Tracy. Appreciate it. I'll give it a try with a period '.' > Eli > "Tracy McKibben" <tr***@realsqlguy.com>. wrote in message > news:ebXn5wCrGHA.2108@TK2MSFTNGP03.phx.gbl... >> Eli Feng wrote: >> > Thank you all for your quick responses and I'm sorry for not making it >> > clearer. This Address field is null by default. I'd like to insert a >> > space in this field if this field is null. This is what I was told to >> > do. The query looks like this: >> > >> > Update TableA set Address1 = ' ' where Type = 'P' and where Address1 is > null >> > >> > Best regards, >> > Eli >> > >> >> The point that we're trying to make is that a VARCHAR field cannot >> contain a blank space. >> >> I'm assuming you're trying to do this as a band-aid fix for an >> application that can't properly deal with an empty or NULL field. This >> isn't going to fix that problem. You're going to have to fix the app, >> or stick some other character into that field, such as a period '.' >> >> >> -- >> Tracy McKibben >> MCDBA >> http://www.realsqlguy.com > > Oops! One excessive where
Update TableA set Address1 = ' ' where Type = 'P' and Address1 is null But this doesn't fix the application issue and I'm not sure if the space is added. "Eli Feng" <ef***@kerisys.com> wrote in message news:u$l1TtCrGHA.3380@TK2MSFTNGP04.phx.gbl... Thank you all for your quick responses and I'm sorry for not making it clearer. This Address field is null by default. I'd like to insert a space in this field if this field is null. This is what I was told to do. The query looks like this:Update TableA set Address1 = ' ' where Type = 'P' and where Address1 is null Best regards, Eli "Arnie Rowland" <ar***@1568.com> wrote in message news:OH7wHdCrGHA.516@TK2MSFTNGP05.phx.gbl... That's going to be a difficult one. As you can see from this example, varchar is designed to drop trailing spaces/blanks.SET NOCOUNT ON CREATE TABLE #MyTable ( RowID int IDENTITY , TestField varchar(20) ) INSERT INTO #MyTable VALUES ( '' ) -- Empty String INSERT INTO #MyTable VALUES ( ' ' ) -- One space INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces INSERT INTO #MyTable VALUES ( 'a ' ) -- One space INSERT INTO #MyTable VALUES ( 'a ' ) -- Three INSERT INTO #MyTable VALUES ( 'a ' ) -- Four INSERT INTO #MyTable VALUES ( space(1) ) -- Two SELECT RowID , TestField FROM #MyTable WHERE TestField = space(1) -- Two Spaces qualify (Rows 2, 7) -- Would be nice for you if only Row 1 SELECT RowID , len( TestField ) -- len() = 0 = NO spaces FROM #MyTable DROP TABLE #MyTable What quality of data are you attempting to capture? NULL for 'Unknown', Empty String for 'Nothing'. Give us more information and perhaps we can help you come up with a solution that works for you. -- Arnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "Eli Feng" <ef***@kerisys.com> wrote in message news:%23mPU1QCrGHA.1976@TK2MSFTNGP04.phx.gbl... >A workaround to fix an application issue requires to insert a space in an > Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer > seems do not fix the issue. This Address field is varchar and allows nulls. > I'm not sure if I hit the space bar in the Default Value line under the > Address field in Enterprise Manager a space will be inserted or not. Any > advice will be appreciated. Eli > > 'Space in NOT added. You CANNOT set a varchar() field to a space.
OK, sometimes, life is easier for developers when they don't have to deal with NULLs in the resultset. They would always have to do some conversion in order to create the screen display -NULL cannot be displayed. So many times developers don't want NULLs in the database. Use an Empty String. Two single quotes with nothing in between. Set the DEFAULT for the column to Empty String (''). Let them search for a space -they will get back the empty strings. -- Arnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Eli Feng" <ef***@kerisys.com> wrote in message news:%23QdMDyCrGHA.3592@TK2MSFTNGP02.phx.gbl... Oops! One excessive whereUpdate TableA set Address1 = ' ' where Type = 'P' and Address1 is null But this doesn't fix the application issue and I'm not sure if the space is added. "Eli Feng" <ef***@kerisys.com> wrote in message news:u$l1TtCrGHA.3380@TK2MSFTNGP04.phx.gbl... Thank you all for your quick responses and I'm sorry for not making it clearer. This Address field is null by default. I'd like to insert a space in this field if this field is null. This is what I was told to do. The query looks like this:Update TableA set Address1 = ' ' where Type = 'P' and where Address1 is null Best regards, Eli "Arnie Rowland" <ar***@1568.com> wrote in message news:OH7wHdCrGHA.516@TK2MSFTNGP05.phx.gbl... That's going to be a difficult one. As you can see from this example, varchar is designed to drop trailing spaces/blanks.SET NOCOUNT ON CREATE TABLE #MyTable ( RowID int IDENTITY , TestField varchar(20) ) INSERT INTO #MyTable VALUES ( '' ) -- Empty String INSERT INTO #MyTable VALUES ( ' ' ) -- One space INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces INSERT INTO #MyTable VALUES ( 'a ' ) -- One space INSERT INTO #MyTable VALUES ( 'a ' ) -- Three INSERT INTO #MyTable VALUES ( 'a ' ) -- Four INSERT INTO #MyTable VALUES ( space(1) ) -- Two SELECT RowID , TestField FROM #MyTable WHERE TestField = space(1) -- Two Spaces qualify (Rows 2, 7) -- Would be nice for you if only Row 1 SELECT RowID , len( TestField ) -- len() = 0 = NO spaces FROM #MyTable DROP TABLE #MyTable What quality of data are you attempting to capture? NULL for 'Unknown', Empty String for 'Nothing'. Give us more information and perhaps we can help you come up with a solution that works for you. -- Arnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "Eli Feng" <ef***@kerisys.com> wrote in message news:%23mPU1QCrGHA.1976@TK2MSFTNGP04.phx.gbl... >A workaround to fix an application issue requires to insert a space in an > Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer > seems do not fix the issue. This Address field is varchar and allows nulls. > I'm not sure if I hit the space bar in the Default Value line under the > Address field in Enterprise Manager a space will be inserted or not. Any > advice will be appreciated. Eli > > Arnie:
I've never heard that before (that you cannot set a varchar() column to a space). The following seems to indicate you can: DECLARE @t TABLE (v varchar(10), v2 varchar(10)); INSERT @t VALUES (' ', ' '); SELECT '[' + v + ']', LEN('[' + v + ']'), '[' + v2 + ']', LEN('[' + v2 + ']') FROM @t; I've always thought that it was because trailing spaces are dropped in the select statement, not within the table. If you revise your sample query as follows, you'll see that all of the spaces are still there, just dropped during the select. SELECT RowID , len('[' + TestField + ']') FROM #MyTable; Vern Rabe Show quote "Arnie Rowland" wrote: > 'Space in NOT added. You CANNOT set a varchar() field to a space. > > OK, sometimes, life is easier for developers when they don't have to deal with NULLs in the resultset. They would always have to do some conversion in order to create the screen display -NULL cannot be displayed. So many times developers don't want NULLs in the database. > > Use an Empty String. Two single quotes with nothing in between. Set the DEFAULT for the column to Empty String (''). > > Let them search for a space -they will get back the empty strings. > > -- > Arnie Rowland > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Eli Feng" <ef***@kerisys.com> wrote in message news:%23QdMDyCrGHA.3592@TK2MSFTNGP02.phx.gbl... > Oops! One excessive where > > Update TableA set Address1 = ' ' where Type = 'P' and Address1 is null > > But this doesn't fix the application issue and I'm not sure if the space is added. > "Eli Feng" <ef***@kerisys.com> wrote in message news:u$l1TtCrGHA.3380@TK2MSFTNGP04.phx.gbl... > Thank you all for your quick responses and I'm sorry for not making it clearer. This Address field is null by default. I'd like to insert a space in this field if this field is null. This is what I was told to do. The query looks like this: > > Update TableA set Address1 = ' ' where Type = 'P' and where Address1 is null > > Best regards, > Eli > "Arnie Rowland" <ar***@1568.com> wrote in message news:OH7wHdCrGHA.516@TK2MSFTNGP05.phx.gbl... > That's going to be a difficult one. As you can see from this example, varchar is designed to drop trailing spaces/blanks. > > > SET NOCOUNT ON > > CREATE TABLE #MyTable > ( RowID int IDENTITY > , TestField varchar(20) > ) > > INSERT INTO #MyTable VALUES ( '' ) -- Empty String > INSERT INTO #MyTable VALUES ( ' ' ) -- One space > INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces > INSERT INTO #MyTable VALUES ( 'a ' ) -- One space > INSERT INTO #MyTable VALUES ( 'a ' ) -- Three > INSERT INTO #MyTable VALUES ( 'a ' ) -- Four > INSERT INTO #MyTable VALUES ( space(1) ) -- Two > > SELECT > RowID > , TestField > FROM #MyTable > WHERE TestField = space(1) -- Two Spaces qualify (Rows 2, 7) > -- Would be nice for you if only Row 1 > SELECT > RowID > , len( TestField ) -- len() = 0 = NO spaces > FROM #MyTable > > DROP TABLE #MyTable > > > What quality of data are you attempting to capture? NULL for 'Unknown', Empty String for 'Nothing'. > > Give us more information and perhaps we can help you come up with a solution that works for you. > > -- > Arnie Rowland > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > > "Eli Feng" <ef***@kerisys.com> wrote in message news:%23mPU1QCrGHA.1976@TK2MSFTNGP04.phx.gbl... > >A workaround to fix an application issue requires to insert a space in an > > Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer > > seems do not fix the issue. This Address field is varchar and allows nulls. > > I'm not sure if I hit the space bar in the Default Value line under the > > Address field in Enterprise Manager a space will be inserted or not. Any > > advice will be appreciated. Eli > > > > I'm a 'dunce'. See my response to Hugo below.
-- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Vern Rabe" <VernR***@discussions.microsoft.com> wrote in message news:F3B46C72-0222-46CB-9BE1-00F2493CEEFA@microsoft.com... > Arnie: > > I've never heard that before (that you cannot set a varchar() column to a > space). The following seems to indicate you can: > > DECLARE @t TABLE (v varchar(10), v2 varchar(10)); > INSERT @t VALUES (' ', ' '); > SELECT '[' + v + ']', LEN('[' + v + ']'), '[' + v2 + ']', LEN('[' + v2 + > ']') FROM @t; > > I've always thought that it was because trailing spaces are dropped in the > select statement, not within the table. If you revise your sample query as > follows, you'll see that all of the spaces are still there, just dropped > during the select. > > SELECT > RowID > , len('[' + TestField + ']') > FROM #MyTable; > > Vern Rabe > > > "Arnie Rowland" wrote: > >> 'Space in NOT added. You CANNOT set a varchar() field to a space. >> >> OK, sometimes, life is easier for developers when they don't have to deal >> with NULLs in the resultset. They would always have to do some conversion >> in order to create the screen display -NULL cannot be displayed. So many >> times developers don't want NULLs in the database. >> >> Use an Empty String. Two single quotes with nothing in between. Set the >> DEFAULT for the column to Empty String (''). >> >> Let them search for a space -they will get back the empty strings. >> >> -- >> Arnie Rowland >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Eli Feng" <ef***@kerisys.com> wrote in message >> news:%23QdMDyCrGHA.3592@TK2MSFTNGP02.phx.gbl... >> Oops! One excessive where >> >> Update TableA set Address1 = ' ' where Type = 'P' and Address1 is null >> >> But this doesn't fix the application issue and I'm not sure if the >> space is added. >> "Eli Feng" <ef***@kerisys.com> wrote in message >> news:u$l1TtCrGHA.3380@TK2MSFTNGP04.phx.gbl... >> Thank you all for your quick responses and I'm sorry for not making >> it clearer. This Address field is null by default. I'd like to insert a >> space in this field if this field is null. This is what I was told to do. >> The query looks like this: >> >> Update TableA set Address1 = ' ' where Type = 'P' and where Address1 >> is null >> >> Best regards, >> Eli >> "Arnie Rowland" <ar***@1568.com> wrote in message >> news:OH7wHdCrGHA.516@TK2MSFTNGP05.phx.gbl... >> That's going to be a difficult one. As you can see from this >> example, varchar is designed to drop trailing spaces/blanks. >> >> >> SET NOCOUNT ON >> >> CREATE TABLE #MyTable >> ( RowID int IDENTITY >> , TestField varchar(20) >> ) >> >> INSERT INTO #MyTable VALUES ( '' ) -- Empty String >> INSERT INTO #MyTable VALUES ( ' ' ) -- One space >> INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces >> INSERT INTO #MyTable VALUES ( 'a ' ) -- One space >> INSERT INTO #MyTable VALUES ( 'a ' ) -- Three >> INSERT INTO #MyTable VALUES ( 'a ' ) -- Four >> INSERT INTO #MyTable VALUES ( space(1) ) -- Two >> >> SELECT >> RowID >> , TestField >> FROM #MyTable >> WHERE TestField = space(1) -- Two Spaces qualify >> (Rows 2, 7) >> -- Would be nice for you >> if only Row 1 >> SELECT >> RowID >> , len( TestField ) -- len() = 0 = NO spaces >> FROM #MyTable >> >> DROP TABLE #MyTable >> >> >> What quality of data are you attempting to capture? NULL for >> 'Unknown', Empty String for 'Nothing'. >> >> Give us more information and perhaps we can help you come up with a >> solution that works for you. >> >> -- >> Arnie Rowland >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> >> "Eli Feng" <ef***@kerisys.com> wrote in message >> news:%23mPU1QCrGHA.1976@TK2MSFTNGP04.phx.gbl... >> >A workaround to fix an application issue requires to insert a >> space in an >> > Address field. Using UPDATE tablename SET Address = ' ' in Query >> Aanalyzer >> > seems do not fix the issue. This Address field is varchar and >> allows nulls. >> > I'm not sure if I hit the space bar in the Default Value line >> under the >> > Address field in Enterprise Manager a space will be inserted or >> not. Any >> > advice will be appreciated. Eli >> > >> > On Thu, 20 Jul 2006 11:06:51 -0700, Arnie Rowland wrote:
>That's going to be a difficult one. As you can see from this example, varchar is designed to drop trailing spaces/blanks. Hi Arnie,I'm sorry, but that's not correct. You just picked the wrong tests. First, I'll show you that varchar DOES retain trailing blanks, using your own test data: SET NOCOUNT ON CREATE TABLE #MyTable ( RowID int IDENTITY , TestField varchar(20) ) INSERT INTO #MyTable VALUES ( '' ) -- Empty String INSERT INTO #MyTable VALUES ( ' ' ) -- One space INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces INSERT INTO #MyTable VALUES ( 'a ' ) -- One space INSERT INTO #MyTable VALUES ( 'a ' ) -- Three INSERT INTO #MyTable VALUES ( 'a ' ) -- Four INSERT INTO #MyTable VALUES ( space(1) ) -- Two SELECT RowID, DATALENGTH(TestField), TestField + '.' FROM #MyTable SELECT RowID, TestField FROM #MyTable WHERE TestField + '.' = SPACE(1) + '.' SELECT RowID, TestField FROM #MyTable WHERE TestField = SPACE(1) AND DATALENGTH(TestField) = DATALENGTH(SPACE(1)) DROP TABLE #MyTable Next, I'll explain why your tests appear to show that the trailing spaces are dropped. >SELECT The rules for string comparison in the ANSI SQL standard state that the> RowID > , TestField >FROM #MyTable >WHERE TestField = space(1) -- Two Spaces qualify (Rows 2, 7) > -- Would be nice for you if only Row 1 shorter string has to be padded with spaces before comparison. So for the row with the empty string, the empty string was padded with a space before comparing it with space(1). And for the row with two spaces in TestField, space(1) was padded with an extra space before starting the byte by byte comparison of the strings. I've shown two workarounds in my code above - either append both operands with a non-space character, or include an extra test to check that both strings have the same length >SELECT Check out what Books Online says about LEN(): "Returns the number of> RowID > , len( TestField ) -- len() = 0 = NO spaces >FROM #MyTable characters of the specified string expression, excluding trailing blanks." If you don't want trailing blanks to be excluded, use the DATALENGTH function instead. -- Hugo Kornelis, SQL Server MVP Hugo, Erland,
I appreciate your gentle manner in setting me straight. One of the reasons I participate here is to learn by being challenged. I see, in your explanation, why I held this misinformation. I have been working for quite some with large projects are ports from legacy systems and many of the ANSI standards have been 'ignored' in the project design. I just checked and ANSI_PADDING is indeed off, and I have become so comfortable working that way, that I had totally 'forgotten' the concepts as you have described. And I appreciate the example code -it makes the learning so much easier than just having to wear the 'dunce cap'. -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Hugo Kornelis" <h***@perFact.REMOVETHIS.info.INVALID> wrote in message news:8400c2ln01r2t9ebf9i1i4fr3ovrhacnrj@4ax.com... > On Thu, 20 Jul 2006 11:06:51 -0700, Arnie Rowland wrote: > >>That's going to be a difficult one. As you can see from this example, >>varchar is designed to drop trailing spaces/blanks. > > Hi Arnie, > > I'm sorry, but that's not correct. You just picked the wrong tests. > > First, I'll show you that varchar DOES retain trailing blanks, using > your own test data: > > SET NOCOUNT ON > > CREATE TABLE #MyTable > ( RowID int IDENTITY > , TestField varchar(20) > ) > > INSERT INTO #MyTable VALUES ( '' ) -- Empty String > INSERT INTO #MyTable VALUES ( ' ' ) -- One space > INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces > INSERT INTO #MyTable VALUES ( 'a ' ) -- One space > INSERT INTO #MyTable VALUES ( 'a ' ) -- Three > INSERT INTO #MyTable VALUES ( 'a ' ) -- Four > INSERT INTO #MyTable VALUES ( space(1) ) -- Two > > SELECT RowID, DATALENGTH(TestField), TestField + '.' > FROM #MyTable > > SELECT RowID, TestField > FROM #MyTable > WHERE TestField + '.' = SPACE(1) + '.' > > SELECT RowID, TestField > FROM #MyTable > WHERE TestField = SPACE(1) > AND DATALENGTH(TestField) = DATALENGTH(SPACE(1)) > > DROP TABLE #MyTable > > > Next, I'll explain why your tests appear to show that the trailing > spaces are dropped. > >>SELECT >> RowID >> , TestField >>FROM #MyTable >>WHERE TestField = space(1) -- Two Spaces qualify (Rows 2, 7) >> -- Would be nice for you if only >> Row 1 > > The rules for string comparison in the ANSI SQL standard state that the > shorter string has to be padded with spaces before comparison. So for > the row with the empty string, the empty string was padded with a space > before comparing it with space(1). And for the row with two spaces in > TestField, space(1) was padded with an extra space before starting the > byte by byte comparison of the strings. > > I've shown two workarounds in my code above - either append both > operands with a non-space character, or include an extra test to check > that both strings have the same length > >>SELECT >> RowID >> , len( TestField ) -- len() = 0 = NO spaces >>FROM #MyTable > > Check out what Books Online says about LEN(): "Returns the number of > characters of the specified string expression, excluding trailing > blanks." > > If you don't want trailing blanks to be excluded, use the DATALENGTH > function instead. > > -- > Hugo Kornelis, SQL Server MVP Arnie Rowland wrote:
Show quote > Hugo, Erland, Don't feel bad Arnie, I fell into the same trap... :-(> > I appreciate your gentle manner in setting me straight. One of the reasons I > participate here is to learn by being challenged. > > I see, in your explanation, why I held this misinformation. I have been > working for quite some with large projects are ports from legacy systems and > many of the ANSI standards have been 'ignored' in the project design. I just > checked and ANSI_PADDING is indeed off, and I have become so comfortable > working that way, that I had totally 'forgotten' the concepts as you have > described. > > And I appreciate the example code -it makes the learning so much easier than > just having to wear the 'dunce cap'. > It serves to remind me to do a better job of' diligence and confirm what I
'think' I know against other sources. And that every day is an opportunity to learn even more. -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:O7vA2OGrGHA.148@TK2MSFTNGP04.phx.gbl... > Arnie Rowland wrote: >> Hugo, Erland, >> >> I appreciate your gentle manner in setting me straight. One of the >> reasons I participate here is to learn by being challenged. >> >> I see, in your explanation, why I held this misinformation. I have been >> working for quite some with large projects are ports from legacy systems >> and many of the ANSI standards have been 'ignored' in the project design. >> I just checked and ANSI_PADDING is indeed off, and I have become so >> comfortable working that way, that I had totally 'forgotten' the concepts >> as you have described. >> >> And I appreciate the example code -it makes the learning so much easier >> than just having to wear the 'dunce cap'. >> > > Don't feel bad Arnie, I fell into the same trap... :-( > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Arnie Rowland (ar***@1568.com) writes:
> That's going to be a difficult one. As you can see from this example, > varchar is designed to drop trailing spaces/blanks.Amazing! There have been several people saying the same thing. And you are all incorrect! varchar does preserve trailing spaces. At least with the settings that are the default by most means of connection. Watch this closely: SET ANSI_PADDING ON -- Default, unless you have a legacy client. go CREATE TABLE #MyTable ( RowID int IDENTITY, field varchar(20) ) INSERT INTO #MyTable VALUES ( '' ) -- Empty String INSERT INTO #MyTable VALUES ( ' ' ) -- One space INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces INSERT INTO #MyTable VALUES ( 'a ' ) -- One space INSERT INTO #MyTable VALUES ( 'a ' ) -- Three INSERT INTO #MyTable VALUES ( 'a ' ) -- Four INSERT INTO #MyTable VALUES ( space(1) ) -- Two SELECT RowID, field, LEN(field), datalength(field), '<' + field + '>' FROM #MyTable DROP TABLE #MyTable go SET ANSI_PADDING OFF -- Default, unless you have a legacy client. go CREATE TABLE #MyTable ( RowID int IDENTITY, field varchar(20) ) INSERT INTO #MyTable VALUES ( '' ) -- Empty String INSERT INTO #MyTable VALUES ( ' ' ) -- One space INSERT INTO #MyTable VALUES ( ' ' ) -- Two spaces INSERT INTO #MyTable VALUES ( 'a ' ) -- One space INSERT INTO #MyTable VALUES ( 'a ' ) -- Three INSERT INTO #MyTable VALUES ( 'a ' ) -- Four INSERT INTO #MyTable VALUES ( space(1) ) -- Two SELECT RowID, field, LEN(field), datalength(field), '<' + field + '>' FROM #MyTable DROP TABLE #MyTable go SET ANSI_PADDING ON Output: RowID field ----------- -------------------- ----------- ----------- ---------------- 1 0 0 <> 2 0 1 < > 3 0 2 < > 4 a 1 2 <a > 5 a 1 4 <a > 6 a 1 5 <a > 7 0 1 < > RowID field ----------- -------------------- ----------- ----------- ---------------- 1 0 0 <> 2 0 1 < > 3 0 1 < > 4 a 1 1 <a> 5 a 1 1 <a> 6 a 1 1 <a> 7 0 1 < > With ANSI_PADDING OFF, trailing spaces are indeed trimmed, but this is old legacy behaviour. The normal behaviour is to save trailing spaces. Note that len() is defined to exclude trailing spaces. To get a count of these, you need to use datalength(). Note also a second differnce between these two: len() counts characters, datalength() counts bytes. This matters when you work with nvarchar. One final note is that it is not the run-time setting of ANSI_PADDING that matters, it's setting of ANSI_PADDING when the column was created. -- Erland Sommarskog, SQL Server MVP, esq***@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 On Thu, 20 Jul 2006 10:44:52 -0700, Eli Feng wrote:
>A workaround to fix an application issue requires to insert a space in an Hi Eli,>Address field. Using UPDATE tablename SET Address = ' ' in Query Aanalyzer >seems do not fix the issue. In spite of what Tracy and Arnie wrote, it IS possible to store a space in a varchar column. (Unless you have set the ANSI_PADDING option to off, but that's not recommended - this option is even put on the deprecated list). Maybe you should post some more datails about the application issue, how the space is supposed to fix it, and what happened instead when you found that the space didn't fix the issue. -- Hugo Kornelis, SQL Server MVP Eli Feng (ef***@kerisys.com) writes:
> A workaround to fix an application issue requires to insert a space in Then there is a problem with your application. In difference to what> an Address field. Using UPDATE tablename SET Address = ' ' in Query > Aanalyzer seems do not fix the issue. This Address field is varchar and > allows nulls. I'm not sure if I hit the space bar in the Default Value > line under the Address field in Enterprise Manager a space will be > inserted or not. Any advice will be appreciated. Eli all people have said there, it is perfectly possible to set a column to a single space. Or lots of spaces for that matter. If there is HTML involved, I guess what the problem is. Try char(160) in such case. Or . Best of course to fix application, but I reckon that this can be difficult if it's a vendor app. -- Erland Sommarskog, SQL Server MVP, esq***@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  |
|||||||||||||||||||||||
Other interesting topics