July 5, 2011 at 7:13 pm
use master;
declare @x int
select @x=len(physical_name) from sys.database_files
select 'move' ,'"'+substring(rtrim(physical_name),1,@x)+'"','k:\db-store' from sys.database_files
thanks again
July 5, 2011 at 8:47 pm
Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types.
The above is from Books Online - which tells us that the return value for your expression is going to be the same as the physical_name column from sys.database_files. That should be nvarchar(260) - which has an actual length of 520.
Add the quotes - and you get a final length of 524, which is nvarchar(262).
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 5, 2011 at 8:50 pm
And, reviewing your code - I am not sure what you are trying to accomplish. There doesn't seem to be any reason to use rtrim, nor any reason to use substring - since spaces will be truncated automatically unless you have set ansi padding on.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 6, 2011 at 7:45 am
use master;
declare @x int
select @x=len(physical_name) from sys.database_files
/****
The above select statement returns (in my case) 90. But when we
put @x in the substring function, it returns the full size of the column?
If I manually type 90 into the substring function it works like it should???
Am I missing something here?
****/
July 6, 2011 at 7:47 am
Yes, you are correct the rtrim is not needed since I'm using substring function.
July 6, 2011 at 7:50 am
What's the problem? I tried running your query and got no errors.
July 6, 2011 at 7:52 am
I unchecked the ANSI PADDING in the tools menu but the query still returns the padded spaces unless I manually type in the length of the text in that field...
July 6, 2011 at 7:53 am
No errors, just trying to get rid of the trail of spaces... SUBSTRING should work with the LEN() function but it isn't???
July 6, 2011 at 7:58 am
I'm not sure what trailing spaces you're referring to. I don't get any.
Though you should realise that you probably need to change the ANSI_PADDING setting in the *database*, not in your SSMS instance, for that setting to apply. I'm not going to change mine to test this, but you could try changing yours and seeing if it works.
July 6, 2011 at 8:08 am
I set ANSI PADDING to FALSE on the database. I also tried with it set to TRUE, same results... Why isn't SUBSTRING working???
Here are the queries and the results.... The length in my case is 97+1. If I manually type 98 in the substring, it works as it should. If I use len() function or variable @X, it will not work?????????????????????? @x = 98 too????????
use AdventureWorksLT;
select len(physical_name)+1 from sys.database_files
select 'move' ,'"'+substring(physical_name,1,len(physical_name)+1)+'"','k:\db-store' from sys.database_files
select 'move' ,'"'+substring(physical_name,1,98)+'"','k:\db-store' from sys.database_files
-----------
98
97
---- --------------------see 255 column length-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
move "M:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Data.mdf" k:\db-store
move "M:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Log.ldf" k:\db-store
---- ---------------------------------------------------------------------------------------------------- -----------
move "M:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Data.mdf" k:\db-store
move "M:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\AdventureWorksLT_Log.ldf" k:\db-store
July 6, 2011 at 8:10 am
funny, I guess your website took out all of the spaces or tabs from the results I just posted....
July 6, 2011 at 8:11 am
funny, the website took out all of the spaces or tabs from the results I just posted...
July 6, 2011 at 8:28 am
Well, sorry, can't really help you any more then. I tried reproducing the problem and couldn't get it to appear. I'm not seeing any trailing spaces in the result of my query :/ Good luck, hopefully someone else can help you out.
July 6, 2011 at 9:39 am
Thank for taking the time OLD HAND. I'm not sure why we are getting different results. I'm using SQL 2008R2 with no service pack applied. I guess it is either a bug or some configuration option somewhere... Maybe one may say you just have to use C# assembly for string manipulations because you just can not count on SQL...
If len(colA) = 90 then
substring(G,1,90) != substring(G,1,len(colA))
Oh well...
July 6, 2011 at 10:37 am
what you are seeing is just a display issue. By default, output is truncated at 255 characters or the size of the column, whichever is smaller.
The data type is nvarchar(260), so you get 255.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply