【HELP】T_SQL LIKE CLAUSE

• knockyo

  SSC Enthusiast

  Points: 165

  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?

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  October 22, 2015 at 2:55 am

  #1834835

  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: 148809

  More actions

  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: 165

  More actions

  October 22, 2015 at 3:16 am

  #1834839

  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: 113507

  More actions

  October 22, 2015 at 3:23 am

  #1834840

  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

  October 22, 2015 at 4:51 am

  #1834849

  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: 165

  More actions

  October 22, 2015 at 4:58 am

  #1834854

  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