March 26, 2020 at 12:59 pm
Hi there
I have the following input string which represents the name of a file I have created
SVPP_ROYA_00001_00021_7_of_7_20200326_101116.csv
There are 7 files named in the target directory
SVPP_ROYA_00001_00021_7_of_7_20200326_101116.csv
Now this is time date stamped and could be 7_Of_7 of 3_of_3 etc
Now I want to be able to identify the part in the string '1_of_7' and replace this wih '*_Of_*'
So I would now have a search string
SVPP_ROYA_00001_00021_*_of_*_20200326_101116.csv
And then I can use this to search for all files matching the above pattern
I tried using something like
dECLARE @FilePath VARCHAR(100) = 'SVPP_ROYA_00001_00021_1_of_7_20200326_1011161.csv'
DECLARE @FindChar VARCHAR(1) = '%*_of_*%'
select patindex(@FilePath, @FindChar)
select CHARINDEX(@FilePath, @FindChar)
But cant locate this.
How can identify this part of the string to find my 7 files?
Could I used something like Replace('SVPP_ROYA_00001_00021_7_of_7_20200326_101116.csv','%_#of_#_%','_*_of_*_'?
March 26, 2020 at 2:34 pm
I used the following code to achieve the expected result.
DECLARE @FileName nvarchar (256) = N'SVPP_ROYA_00001_00021_7_of_7_20200326_101116.csv';
DECLARE @PatIndex nvarchar (32) = N'%[0-9][_]of[_][0-9]%';
SELECT CONCAT(
SUBSTRING(@FileName, 0, PATINDEX(@PatIndex, @FileName)),
N'*_of_*',
REVERSE(SUBSTRING(REVERSE(@FileName), 0, PATINDEX(REPLACE(@PatIndex, 'of', 'fo'), REVERSE(@FileName))))
);
The last part of the concatenation is the tricky part and I'm really curious if there is a better way to solve for this. I have to reverse the inner "of" to "fo" to look against the string in reverse to identify where the pattern ends. Then I reverse the result of that reversed substring to append it to it's original name. I could see this breaking if the file number exceeded 1 digit, but the @PatIndex variable could be adjusted accordingly. I hope this helps!
As I mentioned, I think there are several ways to solve for this, hoping to see some more solutions here!
April 1, 2020 at 12:21 pm
I'm sure one of our resident geniuses can simplify this, but I think this responds to your requirement.
Depends on x_of_y, where x is the 5th element and y is the 7th in the filename between underscores.
WITH tStrings AS (SELECT FileName = 'SVPP_ROYA_00001_00021_6_of_9_20200326_101116.csv'
UNION SELECT 'SVPP_ROYA_00001_00021_7_of_9_20200326_101116.csv'
UNION SELECT 'SVPP_ROYA_00001_00021_1_of_2_20200326_101115.csv'
UNION SELECT 'SVPP_ROYA_00001_00021_2_of_2_20200326_101115.csv'
UNION SELECT 'SVPP_ROYA_00001_00021_11_of_12_20200324_101115.csv'
UNION SELECT 'SVPP_ROYA_00001_00021_12_of_12_20200324_101115.csv'),
t1 AS (SELECTnRow = ROW_NUMBER() OVER (PARTITION BY FileName ORDER BY FileName),
*
FROMtStringst
CROSS APPLY dbo.ufn_DelimitedStringToTable (FileName, '_')),
t2 AS (SELECTDISTINCT tSearch = STUFF((SELECT '_' + CASE WHEN tmp1.nRow IN (5, 7) THEN '%' ELSE tmp1.cRow END
FROMt1 AS tmp1
WHEREtmp1.FileName = tmp2.FileName
ORDER BY tmp1.FileName, tmp1.nRow
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, '')
FROMt1 AS tmp2)
SELECTtSearch, FileName
FROMtStrings
JOINt2ON PATINDEX(tSearch, FileName) > 0
April 1, 2020 at 12:42 pm
Thanks all
April 1, 2020 at 12:50 pm
Sorry, just noticed that my solution depends on my function dbo.ufn_DelimitedStringToTable.
You can find excellent string splitters on this site with a quick search.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply