I have a requirement where by I need to append something into the filename which is being stored as a string in SQL server.
The append always needs to occur before the file extension which are always four characters in length (.txt, .csv and .xml).
I’ve being looking into ways to do this and the one I came across was STUFF and I’ve written the following query:
select fname, code, stuff(fname, 4, 0, concat('_', code)) as d
from (
select right(a.[Path], charindex('',reverse(a.[Path]))-1) as FName, b.Code
from table_A as a
left join table_B as b on a.ID_A = b.ID_B
) as a
Which produces the output:
The issue with this is that the code is not in the right location as I want it on the righthand side before the extension as mentioned. I tried using -4 as a way to maybe make it work from the right, but all this returned is a NULL value. Is there a way to make STUFF work from the right and not the left or is there some other function I should be using instead?
Many thanks
8
Using basic string functions Substring(), Charindex(), Reverse(), … – the PATH column could be divided into file_path, file_name, file_extension, file_full_name – giving you options to manipulate parts of the PATH column including the change of the file name as asked in the question.
It could be done using STUFF() function too.
-- S a m p l e D a t a :
Create Table table_A (ID_A Int, PATH VARCHAR(128));
Insert Into table_A
VALUES (1, 'C:Apps2024PROJ_001XMLORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc.xml'),
(2, 'C:Apps2024PROJ_001CSVDATA_EXP_20240913_2346_153a.csv'),
(3, 'C:Apps2024PROJ_001MISCREP_Revenue_20240613_explained.txt');
Create Table table_B (ID_B Int, CODE VARCHAR(12));
Insert Into table_B
VALUES (1, '31531'),
(2, '20421'),
(3, '02213');
1. STUFF() function
Select a.ID_A, a.PATH, b.CODE,
Substring(
STUFF( a.PATH,
Len(a.PATH) - 3,
0,
CONCAT( '_', b.CODE)
),
(Len(a.PATH) - CHARINDEX('', REVERSE(a.PATH), 1) + 2),
120
) as NEW_FILE_NAME
From table_A a
Inner Join table_B b ON(b.ID_B = a.ID_A)
Order By a.ID_A
/* R e s u l t :
ID_A PATH CODE NEW_FILE_NAME
---- ---------------------------------------------------------------------------------------- ------ --------------------------------------------------------------------
1 C:Apps2024PROJ_001XMLORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc.xml 31531 ORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc_31531.xml
2 C:Apps2024PROJ_001CSVDATA_EXP_20240913_2346_153a.csv 20421 DATA_EXP_20240913_2346_153a_20421.csv
3 C:Apps2024PROJ_001MISCREP_Revenue_20240613_explained.txt 02213 REP_Revenue_20240613_explained_02213.txt */
2. Extracting parts of PATH column
… first create a cte ( named it path_parts )
-- extracting parts of the PATH column
WITH
path_parts AS
( Select a.ID_A, a.PATH,
SubString( a.PATH,
1,
(Len(a.PATH) - CHARINDEX('', REVERSE(a.PATH), 1) + 1)
) as file_path,
SubString( a.PATH,
(Len(a.PATH) - CHARINDEX('', REVERSE(a.PATH), 1) + 2),
100
) as file_full_name,
SubString( SubString( a.PATH,
(Len(a.PATH) - CHARINDEX('', REVERSE(a.PATH), 1) + 2),
100
),
1,
Len( SubString( a.PATH,
(Len(a.PATH) - CHARINDEX('', REVERSE(a.PATH), 1) + 2),
100
)
) - 4
) as file_name,
SubString(a.PATH, Len(a.PATH)-3, 4) as file_extension
From table_A a
)
/* R e s u l t : ( for cte path_parts )
ID_A PATH file_path file_full_name file_name file_extension
---- ---------------------------------------------------------------------------------------- ---------------------------- -------------------------------------------------------------- --------------------------------------------------------- --------------
1 C:Apps2024PROJ_001XMLORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc.xml C:Apps2024PROJ_001XML ORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc.xml ORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc .xml
2 C:Apps2024PROJ_001CSVDATA_EXP_20240913_2346_153a.csv C:Apps2024PROJ_001CSV DATA_EXP_20240913_2346_153a.csv DATA_EXP_20240913_2346_153a .csv
3 C:Apps2024PROJ_001MISCREP_Revenue_20240613_explained.txt C:Apps2024PROJ_001MISC REP_Revenue_20240613_explained.txt REP_Revenue_20240613_explained .txt */
Join this cte to your table_B to add the CODE column value into file_name using CONCAT() function:
-- M a i n S Q L :
Select a.ID_A, a.PATH, a.file_full_name, b.CODE,
CONCAT(a.file_name, '_', b.CODE, a.file_extension) as NEW_FILE_NAME
From path_parts a
Inner Join table_B b ON(b.ID_B = a.ID_A)
Order By a.ID_A
/* R e s u l t :
ID_A PATH file_full_name CODE NEW_FILE_NAME
------ --------------------------------------------------------------------------------------- ------------------------------------------------------------- ------ -------------------------------------------------------------------
1 C:Apps2024PROJ_001XMLORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc.xml ORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc.xml 31531 ORG_98_20240321_1136_62be22f4_358c_436b_89e7_c247d_f03abc_31531.xml
2 C:Apps2024PROJ_001CSVDATA_EXP_20240913_2346_153a.csv DATA_EXP_20240913_2346_153a.csv 20421 DATA_EXP_20240913_2346_153a_20421.csv
3 C:Apps2024PROJ_001MISCREP_Revenue_20240613_explained.txt REP_Revenue_20240613_explained.txt 02213 REP_Revenue_20240613_explained_02213.txt */
See the fiddle here.
1