SQL Server Performance

replacesubstringcharindex statements how to .....

Discussion in 'SQL Server 2005 General DBA Questions' started by sdavidchuk, Jan 12, 2011.

  1. sdavidchuk New Member

    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 = '\sysNamefolderName'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', '') ) = 1order 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 – ABCD1234And 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 replacesubstringcharindex statements how to make sure that those last two charactersAre being read and inserted in the @T table So if the file name not in the original format ABCD1234……@T table is emptyIt does not read new file format ABCD1234ZX Thanks,
  2. sdavidchuk New Member

    my fault...forgot to add...
    above script is a beginning of the loop...
    here is what following the script above:
    set @iT=1
    WHILE @iT<=(SELECT MAX(iT) FROM @T)
    BEGIN
    SELECT @fileCode=fileCode FROM @T where iT=@iT
    SELECT @fileCode
  3. FrankKalis Moderator

    So, you are only interested in the numerical part of the file name?
    If so, here is a UDF that I used for stripping out anything but numbers from a string:
    CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
    RETURNS VARCHAR(1000)
    BEGIN

    DECLARE @pos int;

    SELECT @Pos = PATINDEX('%[^0-9]%',@Input);

    WHILE @Pos > 0
    BEGIN
    SELECT @Input = STUFF(@Input,@pos,1,'');
    SELECT @Pos = PATINDEX('%[^0-9]%',@Input);
    END

    RETURN @Input;
    END
    GO


    SELECT dbo.RemoveChars('ABCD1234And ')
    DROP FUNCTION dbo.RemoveChars;
  4. sdavidchuk New Member

    Thank you very much for a great function.
    However, i need every characterdigit that are after the 'ABCD'
  5. PurpleLady New Member

    [:^)] Try this - IF PATINDEX('ABCD%',@FileName) = 1. The sample below seems to work. You may be able to work it to your loop. You probably won't need the Select @vLength - that just my way of checking things...
    DECLARE @vLength int
    DECLARE
    @FileName varchar(50)Set
    @FileName = 'ABCD1234A.doc'IF
    PATINDEX('ABCD%',@FileName) = 1 BEGINSet @vLength = LEN(LTRIM(RTRIM(@FileName))) - 4
    Select @vLength
    SELECT RIGHT(@Filename, @vLength) -- 1234A.docEND
    Set
    @FileName = 'ABCD1234ZX.doc'IF
    PATINDEX('ABCD%',@FileName) = 1 BEGINSet @vLength = LEN(LTRIM(RTRIM(@FileName))) - 4
    Select @vLength
    SELECT RIGHT(@Filename, @vLength) -- 1234ZX.docEND
    Set
    @FileName = 'XYZABCD1234ZX'IF
    PATINDEX('ABCD%',@FileName) = 1 BEGINSet @vLength = LEN(LTRIM(RTRIM(@FileName))) - 4
    Select @vLength
    SELECT RIGHT(@Filename, @vLength) -- No MatchEND

Share This Page