Pattern Matching

• SSM-242970

  Mr or Mrs. 500

  Points: 547

  More actions

  March 18, 2011 at 11:30 am

  #233361

  Hi,

  I have a column in a table which contains the pattern of a file.It could be AB*.xls, *.*,*2010.*.

  I have a Filename variable which contains the actual file that is pulled.I need to match the filename to the filepattern column and get the pk from the table row.

  How can I do it?

  Thanks

  SSM

• GSquared

  SSC Guru

  Points: 260824

  More actions

  March 18, 2011 at 11:37 am

  #1300697

  Take a look at the "LIKE" operator in the T-SQL string functions section of MSDN or Books Online. It'll do that.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• SSM-242970

  Mr or Mrs. 500

  Points: 547

  More actions

  March 18, 2011 at 11:48 am

  #1300710

  I tried that but my query is like

  declare @filename varchar(30)

  set @filename = 'abcd.txt'

  There is row in table where filepattern = 'ab%.%'

  select fileid from dbo.table1 where @filename like filepattern

  if my variable was a pattern and column was the actual name the like works fine.But my situation is the reverse.I tried using dynamic sql and that didn't work either.

  Thanks

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  March 18, 2011 at 12:13 pm

  #1300724

  HMMM.....

  Works fine for me.

  DECLARE @f TABLE (ndex INT IDENTITY(1,1), pattern VARCHAR(90), fileType VARCHAR(256))

  DECLARE

  @fileVAR VARCHAR(99)

  ,@fileVar2 VARCHAR(99)

  INSERT @f

  SELECT 'a%.%', 'type1' UNION ALL

  SELECT 'a%.exe', 'type2' UNION ALL

  SELECT 'a%.bat', 'type3' UNION ALL

  SELECT 'a%.bat', 'type4' UNION ALL

  SELECT 'z%.%', 'type5' UNION ALL

  SELECT 'z%.exe', 'type6'

  SET @fileVAR = 'z.bat'

  SET @fileVAR2 = 'aaa.exe'

  -- using variable as the filename

  SELECT *

  FROM @f AS F

  WHERE @fileVAR LIKE pattern

  -- OR

  SELECT *

  FROM @f AS F

  WHERE @fileVAR2 LIKE pattern

  **BUT if your pattern is using DOS wildcards ...

  -- using variable as the filename

  SELECT *

  FROM @f AS F

  WHERE @fileVAR LIKE REPLACE(pattern,'*','%')

  -- OR

  SELECT *

  FROM @f AS F

  WHERE @fileVAR2 LIKE REPLACE(pattern,'*','%')

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg