0

hi i have a stored procedure like below:

ALTER PROCEDURE "DBA"."my_enr_test"(in file_name char(255), in uploaded_by char(100))
/* RESULT( column_name column_type, ... ) */
BEGIN

    declare filepath char(100);
    declare validatefile char(255);

    // declare rc bit;

    set filepath = file_name;
    set Filename = Substr(FilePath, PatIndex('.', FilePath),3);

        if filepath <> 'xml' and filepath <> 'csv' then
            set validatefile = 'Invalid File Format'
        else
            set validatefile = 'Valid'
        end if;

    INSERT INTO DBA.pro_import_paths(filename, filevalidate, updated_by) values(filename, validatefile, uploaded_by); 

// RETURN rc;

END

Noow, its capture the last three letters and validate and stored in the database column. But instead of capturing the last three letters, is it any other way to capture after the dot and store the filename as the same of user defined. Thanks in advance!!

Sri
  • 2,233
  • 4
  • 31
  • 55

1 Answers1

1

You can use reverse ,substring ,charindex and you get the result you need ..

SET Filename = SELECT reverse(substring(reverse('FilePath.exe'),1, 
               charindex('.', reverse('FilePath.exe'))))

For multiple "." present in the file name you can use :

SET Filename = SELECT  
               SUBSTRING('FilePath.com.exe',  
      LEN('FilePath.com.exe') - (CHARINDEX('.', reverse('FilePath.com.exe'))-2), 8000)
A Developer
  • 1,001
  • 3
  • 12
  • 32
  • hi your edited answer works fine. but its still captures the last three letters in the filename column. I want the last three letters only for validations... the filename should store as same as the user defined.. Any idea sir..? – Sri Sep 21 '12 at 09:54
  • Here in Filename you have the file extention..now check in the IF (Filename <> 'csv') and do your job.. – A Developer Sep 21 '12 at 10:00
  • am glad that it helped u ..enjoy coding!! – A Developer Sep 21 '12 at 10:08