【HELP】T_SQL LIKE CLAUSE

Question

Post reply

【HELP】T_SQL LIKE CLAUSE

knockyo

SSC Enthusiast

Points: 145
More actions
October 22, 2015 at 2:41 am

#322079

Hi All,

In my column (FilePath) contains the values as below,

FILEPATH

---------------

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122\207

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C124\212

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C127\252

If my SQL as below,

select * from TRFile where FilePath LIKE '%\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12%' order by FileName

It will return all the values with contains 'C12' and this is wrong.

I need the result only return ONE record which is

> \\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12

Any idea?

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

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

Ant-Green SSC Guru Points: 112965 More actions · Answer 1

Initial way would be to reverse the string, find the first \ strip out the remaining characters and compare.

Something like this



create table #TRFile  (filepath varchar(100))

insert into #TRFile  values

('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P'),

('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122\207'),

('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C124\212'),

('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C127\252')

select filepath from #TRFile

where LEFT(FILEPATH, (LEN(FILEPATH) - CHARINDEX('\',REVERSE(filepath),0))) = '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12'

drop table #TRFile

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 2

John Mitchell-245523

SSC Guru

Points: 148786

October 22, 2015 at 3:01 am

#1834837

Surely you just need to add another backslash to your search pattern? I've removed the first wildcard from the front - you don't need it if all values start with "\\".

select * from TRFile



where FilePath LIKE '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\%'

order by FileName

John

knockyo SSC Enthusiast Points: 145 More actions · Answer 3

anthony.green (10/22/2015)

Initial way would be to reverse the string, find the first \ strip out the remaining characters and compare.

Something like this

create table #TRFile (filepath varchar(100)) insert into #TRFile values ('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P'), ('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122\207'), ('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C124\212'), ('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C127\252') select filepath from #TRFile where LEFT(FILEPATH, (LEN(FILEPATH) - CHARINDEX('\',REVERSE(filepath),0))) = '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12' drop table #TRFile

Thanks your feedback. If sometimes my query need to filter by this scenario as below,

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12

OR

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P

Ant-Green SSC Guru Points: 112965 More actions · Answer 4

Us an OR to search the exact string

Just uncomment a particular string at the top you want to search



--declare @searchstring varchar(100) = '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12'

--declare @searchstring varchar(100) = '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P'

--declare @searchstring varchar(100) = '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122'

create table #TRFile  (filepath varchar(100))

insert into #TRFile  values

('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P'),

('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122\207'),

('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C124\212'),

('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C127\252')

select filepath from #TRFile

where LEFT(FILEPATH, (LEN(FILEPATH) - CHARINDEX('\',REVERSE(filepath),0))) = @searchstring or filepath = @searchstring 

drop table #TRFile

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

knockyo (10/22/2015)

Hi All,

In my column (FilePath) contains the values as below,

FILEPATH

---------------

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122\207

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C124\212

\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C127\252

If my SQL as below,

select * from TRFile where FilePath LIKE '%\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12%' order by FileName

It will return all the values with contains 'C12' and this is wrong.

I need the result only return ONE record which is

> \\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12

Any idea?

Use = instead of LIKE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

knockyo SSC Enthusiast Points: 145 More actions · Answer 6

anthony.green (10/22/2015)

Us an OR to search the exact string

Just uncomment a particular string at the top you want to search

--declare @searchstring varchar(100) = '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12' --declare @searchstring varchar(100) = '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P' --declare @searchstring varchar(100) = '\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122' create table #TRFile (filepath varchar(100)) insert into #TRFile values ('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C12\29P'), ('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C122\207'), ('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C124\212'), ('\\Dpo-doctest1\Citibank Files\Citibank\07-JULY\20150703\C127\252') select filepath from #TRFile where LEFT(FILEPATH, (LEN(FILEPATH) - CHARINDEX('\',REVERSE(filepath),0))) = @searchstring or filepath = @searchstring drop table #TRFile

Thanks ur help