May 2, 2011 at 2:41 pm
Hello Everyone
I am working on some things in SQL that involve strings. I know, I do not like it either. 😀
I want to make absolutely certain that there is not a backslash at the end of this string
P:\SQL Database Backups\Transaction Logs\
That is just a simple sample that I made up, but it shows what I need.
This string path name is actually selected from a table. I need to make sure that is somehow the ending backslash some how gets past my front-end app, that it is removed just before the string concatenation.
This is what I want:
P:\SQL Database Backups\Transaction Logs\AdventureWorks_TransLog_201105021530.trn
This is what I cannot have:
P:\SQL Database Backups\Transaction Logs\\AdventureWorks_TransLog_201105021530.trn
Cannot have two backslashes in a row like that.
I need to concatenate two strings, that will make up a true path statement. I am manually adding in the backslash in my code, and I need to make sure that the first half of the path does not have a backslash at the end, even by accident. So I need to remove it if one exists.
I have tried a couple things, but I am not so good with working with strings, and I know that someone has a better way that I have.
Thanks in advance
Andrew SQLDBA
May 2, 2011 at 2:52 pm
i htink by using a case statement, you can make sure you apend them together correctly; I'm assuming the fields are varchars, so i don't worry about trailing spaces;
see if this does what you want:
With MyCTE As
(
SELECT 'P:\SQL Database Backups\Transaction Logs\' AS ThePath, 'AdventureWorks_TransLog_201105021530.trn' AS TheFile UNION ALL
SELECT 'P:\SQL Database Backups\Transaction Logs' AS ThePath, 'AdventureWorks_TransLog_201105021530.trn' AS TheFile UNION ALL
SELECT 'P:\SQL Database Backups\Transaction Logs' AS ThePath, '\AdventureWorks_TransLog_201105021530.trn' AS TheFile UNION ALL
SELECT 'P:\SQL Database Backups\Transaction Logs\' AS ThePath, '\AdventureWorks_TransLog_201105021530.trn' AS TheFile
)
SELECT
CASE
WHEN RIGHT(ThePath,1) <> '\'
THEN ThePath
ELSE REVERSE(SUBSTRING(REVERSE(ThePath),2,100))
END
+ CASE
WHEN LEFT(TheFile,1) = '\'
THEN TheFile
ELSE '\' + TheFile
END
FROM MyCTE
Lowell
May 2, 2011 at 3:32 pm
Thank You Lowell
But I am not at all sure why you are including all the UNION All statements. And using a CTE method.
I am just after a simple replace in a string type of statement, at the far right, or using LEN function, to get the length, and some more string manipulation methods.
Thanks
Andrew SQLDBA
May 2, 2011 at 3:42 pm
AndrewSQLDBA (5/2/2011)
But I am not at all sure why you are including all the UNION All statements. And using a CTE method.
Those UNION ALL are not part of the replace query.. those were used to create sample data as you have not provided possible types of folder patths to work the query on.. the second part of Lowell's query ( the one that uses CASE statements ) is what u are after..
May 2, 2011 at 3:43 pm
Lowell query:
SELECT CASE WHEN RIGHT(ThePath,1) <> '\' THEN ThePath ELSE REVERSE(SUBSTRING(REVERSE(ThePath),2,100)) END + CASE WHEN LEFT(TheFile,1) = '\' THEN TheFile ELSE '\' + TheFile END
FROM YourTable
May 2, 2011 at 3:51 pm
Thanks Everyone
That works well
Andrew SQLDBA
May 2, 2011 at 4:00 pm
Try this
CREATE TABLE [dbo].[Test_1](
[String1] [nchar](100) NULL,
[String2] [nchar](100) NULL
) ON [PRIMARY]
insert into dbo.Test_1
select 'P:\SQL Database Backups\Transaction Logs\',
'\AdventureWorks_TransLog_201105021530.trn'
select LEFT(string1, LEN(string1) - 1) + LTRIM(string2)
from Test_1
May 2, 2011 at 4:32 pm
piotrka (5/2/2011)
Try thisCREATE TABLE [dbo].[Test_1](
[String1] [nchar](100) NULL,
[String2] [nchar](100) NULL
) ON [PRIMARY]
insert into dbo.Test_1
select 'P:\SQL Database Backups\Transaction Logs\',
'\AdventureWorks_TransLog_201105021530.trn'
select LEFT(string1, LEN(string1) - 1) + LTRIM(string2)
from Test_1
Hi piotrka, try your query with input:
insert into dbo.Test_1
select 'P:\SQL Database Backups\Transaction Logs',
'\AdventureWorks_TransLog_201105021530.trn'
Sounds correct?? Hmm
The usage of CASE in Lowell's code is exactly due to this type of inputs. Even if there isnt a backslash at the end, Lowell code will produce a full folder path..
HTH
May 2, 2011 at 4:52 pm
Hi, I would recommend using STUFF(myvarchar,LEN(myvarchar),1,'') instead of REVERSE/SUBSTRING - it's a bit quicker.
Sample timing for 1 million rows:
Duration_Avg Cpu_Avg
REVERSE/SUBSTRING METHOD 7599 7597
STUFF METHOD 6756 6747
Also, unless you are guaranteed to only have 1 backslash at the end of your path, you had better allow for multiples....
e.g. P:\mypath\\\
I would be interested to know if this is a "real life" requirement and what is the thinking behind it - as Windows will be quite happy with a double backslash - is there some third party software involved that doesn't like it? Or is this just a simple example of a more complex problem?
Thanks
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply