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

  • 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,

  • 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

  • unfortunately, your solution didn't work

Viewing 3 posts - 1 through 2 (of 2 total)

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