replace\substring\charindex statements how to ....

• SD1999

  Hall of Fame

  Points: 3140

  More actions

  January 12, 2011 at 1:23 pm

  #144283

  T SQL GURUS, I NEED YOUR HELP !!!

  DECLARE @fileCode CHAR(4)

  DECLARE @T table (iT int identity, fileCode char(4))

  DECLARE @iT int

  SET NOCOUNT ON

  DECLARE @DelDir varchar(200),

  @Command varchar(200)

  -- SPECIFY LOCATION

  SET @DelDir = '\\sysName\folderName\'

  SET @Command = 'dir ' + @DelDir + '*.doc'

  DECLARE @bk table (f_output varchar(100))

  insert into @bk

  EXEC master.dbo.xp_cmdshell @Command

  insert into @t (filecode)

  SELECT replace(substring(f_output, charindex('ABCD', f_output) + 4, 50), '.doc', '')

  FROM @bk where isnumeric(replace(substring(f_output, charindex('ABCD', f_output) + 4, 50), '.doc', '') ) = 1

  order by 1

  SELECT * FROM @T

  It supposed to go to the specified directory read all .doc file names which are in the following format:

  ABCD and any combination of four digits, for example – ABCD1234

  And then insert all the last four digits in the temp table @T

  However file naming format has changed and new one is ABCD1234ZX…..

  I cannot figure out with all these replace\substring\charindex statements how to make sure that those last two characters

  Are being read and inserted in the @T table

  So if the file name not in the original format ABCD1234……

  @T table is empty

  It does not read new file format ABCD1234ZX

  Thanks,

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 12, 2011 at 1:29 pm

  #1272615

  Duplicate post. Already answered in another thread on this site.

  - 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

• SD1999

  Hall of Fame

  Points: 3140

  More actions

  January 12, 2011 at 1:49 pm

  #1272639

  unfortunately, your solution didn't work