December 15, 2008 at 2:23 pm
I think I may have come across a bug with the Replace() function in SQL Server 2005. I don't have another version of SQL Server avaiable to test this on. I'm thinking I am just missing something obvious.
To recreate this launch SSMS and execute the following query with the Results set to text and not to Grid.
SELECT 'SampleTextForFirstColumn', Replace('abcdefghicde','cde','xxx'), 'SampleTextForLastColumnWay...OverHere'
Unless this is restricted to just my environment, in the results window you'll see that the width of the second column is very, very long, 8000 characters to be exact. BOL states that Replace() retruns either VARCHAR or NVARCHAR vut it looks like in this case it's returning text or the max number of characters in the VARCHAR type. I say a type of TEXT only because the first time I copied the results and wrapped them within the LEN() function to see how big the thing was I got an error that the data type TEXT is invalid for the function.
I tried looking this up on Google but found nothing.
Anyone know anything about this? In particular a work-a-round? We have a SSIS that creates a text file (for data export) that uses Replace() in it's code and it's messing up the files format because Replace() is returning way to big a spot in the file.
Thanks
Kindest Regards,
Just say No to Facebook!December 15, 2008 at 4:15 pm
Maybe this will show you what is actually happening:
If object_id('tempdb..#TestReplace') Is Not Null
Begin;
Drop Table #TestReplace;
End;
Select 'SampleTextForFirstColumn' As column1
,replace('abcdefghicde' ,'cde' ,'xxx') As replacedColumn
,'SampleTextForLastColumnWay...OverHere' As OverHere
Into #TestReplace
Execute sp_help #TestReplace;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 16, 2008 at 8:18 am
Interesting, did not know this behaviour, now I know
OP, you could try shorten the column after the replace
Execute sp_help #TestReplace;
Column_nameTypeComputedLength
replacedColumnvarcharno8000
alter table #TestReplace
alter column replacedColumn varchar(500)
Execute sp_help #TestReplace;
Column_nameTypeComputedLength
replacedColumnvarcharno500
December 16, 2008 at 9:31 am
Jerry - I would convert the column in the query itself. Using the temp table was just a way of showing what database was being defined from the replace function.
The replace function is working as designed - as far as I can tell.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 16, 2008 at 10:35 am
Jeffrey Williams (12/16/2008)
Jerry - I would convert the column in the query itself. Using the temp table was just a way of showing what database was being defined from the replace function.The replace function is working as designed - as far as I can tell.
Perhaps but if it is working as designed then I;d say it's deign is flawed or this by design was unintended. After all what benefit could there be from intenionally having this function pad the value it retruns with enough spaces to equal 8000 characters? That makes no sense.
I'd say that if it was intenional then BOL would surely mention this, don't you agree?
Thanks for replying
Kindest Regards,
Just say No to Facebook!December 16, 2008 at 10:37 am
it is not a bug,
it is a limit one must know about.
If you need more than 8000 bytes, you need an implicit convert to varchar(max).
,replace(cast('abcdefghicde' as varchar(max)) ,'cde' ,'xxx') As replacedColumnvarcharmax
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2008 at 10:41 am
BTW I managed a work-a-round which is to use the Left() function to return the X most characters I need. While this works for my scenario it will not for every scenario because it requires you to know the length of the characters to return. Using the Len() function will (I believe) return the correct values but that also means adding another string function to the one already added just to trim away unwanted spaces.
In total you have to use 3 String functions, 2 instances of Len() & 1 instance of Left() to get a return value you expect.
NOTE: The RTrim() function, in my testing, does not get rid of these excess spaces either.
Work-a-round:
/*Replace this*/
SELECT 'SampleTextForFirstColumn', Replace('abcdefghicde','cde','xxx'), 'SampleTextForLastColumnWay...OverHere'
/*With This which adds 2 instances of the Len() function and 1 instance of the Left() function*/
SELECT 'SampleTextForFirstColumn', Left(Replace('abcdefghicde','cde','xxx'),Len(Replace('abcdefghicde','cde','xxx'))), 'SampleTextForLastColumnWay...OverHere'
Kindest Regards,
Just say No to Facebook!December 16, 2008 at 1:06 pm
I guess I am still not clear on what your issue is. If I use the following:
Use tempdb;
If object_id('tempdb..#TestReplace') Is Not Null
Begin;
Drop Table #TestReplace;
End;
Select 'SampleTextForFirstColumn' As column1
,replace('abcdefghicde' ,'cde' ,'xxx') As replacedColumn
,'SampleTextForLastColumnWay...OverHere' As OverHere
Into #TestReplace
Select *
,len(replacedColumn) As ColLen
,datalength(replacedColumn) As ColDataLen
From #TestReplace;
Execute sp_help #TestReplace;
I clearly see that both the length and datalength of the column is 12. No additional spaces are added to the column.
Now, if you add additional spaces into the above - then the length stays at 12 and the datalength is increased. The replace function does not remove trailing spaces for you.
One of the other things that is confusing the issue for you, is that you are looking at the data in text display mode. And you have modified QA so that it does not truncate the data (default setting is to show the first 256 characters of any column). Because of this, it 'appears' to you that there are a whole lot of spaces added - which really are not there.
Replace will return a varchar(8000) column. If you need to column to be smaller, then you have to define the datatype and size. For example:
Use tempdb;
If object_id('tempdb..#TestReplace') Is Not Null
Begin;
Drop Table #TestReplace;
End;
Select 'SampleTextForFirstColumn' As column1
,cast(replace('abcdefghicde' ,'cde' ,'xxx') As varchar(100)) As replacedColumn
,'SampleTextForLastColumnWay...OverHere' As OverHere
Into #TestReplace
Select *
,len(replacedColumn) As ColLen
,datalength(replacedColumn) As ColDataLen
From #TestReplace;
Execute sp_help #TestReplace;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 17, 2008 at 4:15 pm
Jeffrey Williams (12/16/2008)
I guess I am still not clear on what your issue is. If I use the following:
Use tempdb;
If object_id('tempdb..#TestReplace') Is Not Null
Begin;
Drop Table #TestReplace;
End;
Select 'SampleTextForFirstColumn' As column1
,replace('abcdefghicde' ,'cde' ,'xxx') As replacedColumn
,'SampleTextForLastColumnWay...OverHere' As OverHere
Into #TestReplace
Select *
,len(replacedColumn) As ColLen
,datalength(replacedColumn) As ColDataLen
From #TestReplace;
Execute sp_help #TestReplace;
I clearly see that both the length and datalength of the column is 12. No additional spaces are added to the column.
Now, if you add additional spaces into the above - then the length stays at 12 and the datalength is increased. The replace function does not remove trailing spaces for you.
One of the other things that is confusing the issue for you, is that you are looking at the data in text display mode. And you have modified QA so that it does not truncate the data (default setting is to show the first 256 characters of any column). Because of this, it 'appears' to you that there are a whole lot of spaces added - which really are not there.
Replace will return a varchar(8000) column. If you need to column to be smaller, then you have to define the datatype and size. For example:
Use tempdb;
If object_id('tempdb..#TestReplace') Is Not Null
Begin;
Drop Table #TestReplace;
End;
Select 'SampleTextForFirstColumn' As column1
,cast(replace('abcdefghicde' ,'cde' ,'xxx') As varchar(100)) As replacedColumn
,'SampleTextForLastColumnWay...OverHere' As OverHere
Into #TestReplace
Select *
,len(replacedColumn) As ColLen
,datalength(replacedColumn) As ColDataLen
From #TestReplace;
Execute sp_help #TestReplace;
Let me try this another way that will allow anyone to re-create this if it is turely a bug and I believe the reason for the confusion here is most of us work in the GRID results mode and not TEXT or even TO FILE.
1) Open a new Query Window in SSMS and execute the below code as shown (no personal edits) selecting 'TO FILE' for the Results so that SSMS prompts you to save this to a plain text file instead of displaying the results within SSMS. Save this with the file name WithoutREPLACE.txt.
SELECT 'SampleTextForFirstColumn', 'abcdefghicde','SampleTextForLastColumnWay...OverHere'
2) Open another new query window in SSMS and execute the below code as shown (no personal edits) selecting 'TO FILE' for the results so that just as with the prior qury the results are saved to a pain text file. Name this file WithREPLACE.txt.
SELECT 'SampleTextForFirstColumn', Replace('abcdefghicde','cde','cde'), 'SampleTextForLastColumnWay...OverHere'
3) Open each file using Notepad and not some other Text Editor so that you see teh same thing as I; this oddity may be autofixed by robust text editors where as NotePad shows it as is.
Now if the REAPLCE() function works as described iu BOL then these 2 text files should be identical in their contents. When I look at each file the one named WithREPLACE.txt has a far larger number of characters in it then the one labeled WithoutREPLACE.txt .
Do you get identical files or does WithReplace.txt look a bit odd to you as well?
It's possible this is something not in SQL Server but our environment. But for me it does this the same way on multiple databases and I tested it on a copy of SQL 2008 I just installed and it still retruns the same thing.
The whole reason I asked about this is because this same odd behaivor with the Replace() function is causing the contents of a txt file, auto generated by an Integration Services package, to be out of wack. This is not just something with SSMS & sending the Results to TEXT.
PLease let me know what your 2 files look like as I'm very curious to see if this happens for others.
Remember that BOL indicates that REPLACE() retruns either VARCHAR or NVARCHAR, not the MAX size of those nor does it say anything close to this thing padding the return value with spaces.
Thank You.
Kindest Regards,
Just say No to Facebook!December 17, 2008 at 4:15 pm
ALZDBA (12/16/2008)
it is not a bug,it is a limit one must know about.
If you need more than 8000 bytes, you need an implicit convert to varchar(max).
,replace(cast('abcdefghicde' as varchar(max)) ,'cde' ,'xxx') As replacedColumnvarcharmax
HUH?
What makes you think I want this to rteurn more then 8000 bytes? The problem is that Replace() is retruning TOO MUCH text by adding a large number of spaces to the value the function returns
Kindest Regards,
Just say No to Facebook!December 17, 2008 at 4:46 pm
Just ran a test. Here is what I found. If you use REPLACE in a select into statement, since it needs to create the table on the fly it doesn't know how big the column needs to be so it so it makes it the maximum size of varchar, 8000, or nvarchar, 4000, depending on the data types used.
If you want to reduce the size of the string you need to predefine the size of the columns in your table.
December 17, 2008 at 4:59 pm
I understand where you are seeing this as a problem. What I was trying to show you is that this is not a problem, but instead your interpretation of it.
Based upon your code, what varchar length would you expect the REPLACE function to return? Should it return a VARCHAR(10)? If that is what you are expecting, why do you expect that? Should it return a VARCHAR(100)? VARCHAR(1000)? VARCHAR(8000)? Something else?
The default behavior tells us that the REPLACE function is going to return a VARCHAR(8000). Without doing anything else, that is exactly what is going to happen. Now, the problem you are running into is how SSMS is treating that output when it is output to a text file - or to the screen in text mode. Because the default data type that is returned by the REPLACE function is VARCHAR(8000) - SSMS must make enough room available in the output file to contain 8000 characters for that field. The same goes for text mode - need 8000 characters available.
Now, here is the important part. If you have not specified a length using CAST/CONVERT - how does SSMS know that you will not have any data that exceeds 100 characters for that field? SSMS has no idea whether or not that field will contain 1 character, 100 characters or 8000 characters, so it creates the output (again, either a file or text mode) large enough to contain the largest amount of data that is possible for that field to contain.
And don't be misled by grid mode - because grid mode does exactly the same thing. The only difference in grid mode is that grid mode has the ability of growing/shrinking the cell size as needed. So, if the first few rows are empty - the cell is going to be small and you will have to expand the cell to see all of the data that is returned in later rows.
So, again - this is not a bug. It is working as designed...:w00t:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 17, 2008 at 5:09 pm
I did a little more testing adding a cast( as varchar(100)) around the replace, and it validated Jeffrey's assertions above.
December 18, 2008 at 12:07 am
YSLGuru (12/17/2008)
...HUH?
What makes you think I want this to rteurn more then 8000 bytes? The problem is that Replace() is retruning TOO MUCH text by adding a large number of spaces to the value the function returns
...
No problem, I figured you'd come up with the varchar length solution yourself and therefor highlighted the varchar(max) issue.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 18, 2008 at 12:00 pm
Jeffrey Williams (12/17/2008)
I understand where you are seeing this as a problem. What I was trying to show you is that this is not a problem, but instead your interpretation of it.Based upon your code, what varchar length would you expect the REPLACE function to return? Should it return a VARCHAR(10)? If that is what you are expecting, why do you expect that? Should it return a VARCHAR(100)? VARCHAR(1000)? VARCHAR(8000)? Something else?
The default behavior tells us that the REPLACE function is going to return a VARCHAR(8000). Without doing anything else, that is exactly what is going to happen.
......
So, again - this is not a bug. It is working as designed...:w00t:
After reading LYnne's post I understand why the function is returning the extra spaces but I have to disagree that is is the default or expected behaivor even if this is what the developer intended.
Why? Because of the docuymentation.
Here is the exact text from Books-On-Line 2005 for the Replace() function:
Return Types
Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.
Returns NULL if any one of the arguments is NULL.
There's nothing here indicating that it is supposed to return VARCHAR(8000) and if it is designed to always work this way then shouldn't the documentation show the return value as VARCHAR(8000)?
In the case of the String Function UPPER() the Retrun Type is listed (in BOL 2005) as varchar or nvarchar just like what is listed for Replace(). And just as with the Replace() function, Upper() does not know in advance how many characters it needs to change to upper case and yet the value it returns is not padded with a bunch of spaces like Replace() does.
This behaivor of the Replace(0 function may be by design but if it is it should be explcitedly detailed in BOL and not simple assumed that everyone knows it is going to work this way. Some of teh replies to my post clearly indicate others did not realize Replace() was doing this and so assuming all will see it as working the same way, returning what boils down to VARCHAR(8000) is a bad assumption.
I do appreciate your taking the time to reply and in detail. My point is that you can't assume anything in computer langauges, that's how we get buggy code. And so treating this behaivor by Replace() as being the norm or what is expected when BOL does not state this behaivor and because other String functions with Return values described the same way do not work like this, is an example of where what may be IMPLICIT should instead be EXPLICIT.
In closing this is either at worst, a bug in Replace() or at best, inacurate documentation of the function in BOL. :w00t:
Thanks Again to all who took the time to chime in on this one.
Kindest Regards,
Just say No to Facebook!Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply