Replace in String

Question

Replace in String

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
May 2, 2011 at 2:41 pm

#250143

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
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

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..

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

AndrewSQLDBA

SSC-Insane

Points: 21827

May 2, 2011 at 3:51 pm

#1318786

Thanks Everyone

That works well

Andrew SQLDBA

piotrka SSCrazy Points: 2031 More actions · Answer 6

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

piotrka (5/2/2011)
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

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

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);