Find string and stop there

Question

Post reply

Find string and stop there

Saran

Right there with Babe

Points: 760

April 1, 2022 at 4:04 pm

#4009414

Hi All,

Here is the data, I want to get result from the beginning up to including end of the \.

ex:

\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\db_1\FULL\

\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\db_2\FULL\

\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\db_3\FULL\

\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_4\FULL\

Can you help.

create table #tbl_backup_path (backup_path varchar (8000))

insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\db_1\FULL\ServerName$SQLEXPRESS_db_1_FULL_20220327_031546.bak')
insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\db_2\FULL\ServerName$SQLEXPRESS_db_2_FULL_20220327_031551.bak')
insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\db_3\FULL\ServerName$SQLEXPRESS_db_3_FULL_20220327_031552.bak')
insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_4\FULL\ServerName$SQLEXPRESS_DB_4_FULL_20220327_031554.bak')

select * from #tbl_backup_path

drop table #tbl_backup_path

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Which \?

Can you give an example of what you're trying to return? My mindreading skills are taking April Fool's Day off.

Saran Right there with Babe Points: 760 More actions · Answer 2

I want to return the up to last \ (OR) FULL\.

ex:

\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\db_1\FULL\

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Oh, okay, Gotcha. Something like this?

select backup_path
, LEFT(backup_path,CHARINDEX('\FULL\',backup_path,1) + LEN('FULL\'))
from #tbl_backup_path;

Saran Right there with Babe Points: 760 More actions · Answer 4

pietlinden wrote:

Oh, okay, Gotcha. Something like this?

select backup_path
, LEFT(backup_path,CHARINDEX('\FULL\',backup_path,1) + LEN('FULL\'))
from #tbl_backup_path;

Thank you working perfect.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Saran wrote:

pietlinden wrote:
Oh, okay, Gotcha. Something like this?
select backup_path
, LEFT(backup_path,CHARINDEX('\FULL\',backup_path,1) + LEN('FULL\'))
from #tbl_backup_path;
Thank you working perfect.

Do you understand how and why it works?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Add another row to the sample table:

insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_4\DIFF\ServerName$SQLEXPRESS_DB_4_DIFF_20220327_032554.bak')

Does the query produce the expected result now?

_____________
Code for TallyGenerator

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

I'd urge you not to write a query based only on current data, but to make it more flexible:

--
insert into #tbl_backup_path values ('\\x.x.x.x\Database_Backup_Daily\ServerName$SQLEXPRESS\DB_5\DIFF\ServerName$SQLEXPRESS_DB_4_FULL_20220327_091554.diff')


SELECT backup_path, LEFT(backup_path, LEN(backup_path) - CHARINDEX('\', REVERSE(backup_path)) + 1)
FROM #tbl_backup_path

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".