I'm trying to change the result set so that the OriginalName column string is rewritten to the output table. There are 2 scenarios id like to change this string according to:

1. Reading the OriginalName from left to right IF the first date value found is in the form YYYYMMDD then trim everything from the right and append .txt

   • For example, for OriginalName := "TPV02002_KBA_HOUSE_Daily_Claims_20221009_221010091706003.txt" Id want the output result to be changed to TPV02002_KBA_HOUSE_Daily_Claims_20221009.txt

2. Same change but instead IF is the first date format seen YYYY-MM-DD

   • For example, for OriginalName := "TPV00165_Paid Claims 2022-10-09_221010080406124.txt"
   • Desired output "TPV00165_Paid Claims 2022-10-09.txt"

SELECT
    RXInboundFileQueue.OriginalName,
    'rx' AS Filetype,
    RXInboundFileQueue.FileCatalogedOn

FROM
    EDIPlatform.dbo.RXInboundFileQueue

WHERE
    FileCatalogedOn >= DATEADD(DAY, -7, GETDATE())

ORDER BY
    OriginalName DESC,
    FileCatalogedOn

CodePudding user response：

If your data is indeed that regular, just STUFF() the extraneous numerals with an empty string:

SELECT
    RXInboundFileQueue.OriginalName,
    STUFF(
        RXInboundFileQueue.OriginalName,
        LEN(RXInboundFileQueue.OriginalName)-19,
        16,
        ''
    ) AS OriginalNameFormatted,
    'rx' AS Filetype,
    RXInboundFileQueue.FileCatalogedOn

FROM
    EDIPlatform.dbo.RXInboundFileQueue

WHERE
    FileCatalogedOn >= DATEADD(DAY, -7, GETDATE())

ORDER BY
    OriginalName DESC,
    FileCatalogedOn

If the data gets more irregular, research regular expressions and pattern matching.

CodePudding user response：

SELECT RXInboundFileQueue.OriginalName OldOriginalName
,SUBSTRING(RXInboundFileQueue.OriginalName, 0, 
LEN(RXInboundFileQueue.OriginalName) - CHARINDEX('_', 
REVERSE(RXInboundFileQueue.OriginalName), 0)   1)   '.txt' 
NewOriginalName
FROM EDIPlatform.dbo.RXInboundFileQueue
WHERE FileCatalogedOn >= DATEADD(DAY, -7, GETDATE())

CodePudding user response：

A couple of approaches:

SELECT Q.OriginalName, U.UpdatedName, U.UpdatedName2, C.*
FROM EDIPlatform.dbo.RXInboundFileQueue Q
CROSS APPLY (
    SELECT
        pos1 = CHARINDEX('.', REVERSE(Q.OriginalName)),
        pos2 = CHARINDEX('_', REVERSE(Q.OriginalName)),
        pos3 = PATINDEX('%[.][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][_]%', REVERSE(Q.OriginalName))
) C
CROSS APPLY (
    SELECT
        UpdatedName =
            CASE WHEN pos1 > 0 AND pos2 > pos1
            THEN REVERSE(STUFF(REVERSE(Q.OriginalName), pos1   1, pos2 - pos1, ''))
            ELSE Q.OriginalName
            END,
        UpdatedName2 =
            CASE WHEN pos3 > 0
            THEN REVERSE(STUFF(REVERSE(Q.OriginalName), pos3   1, 16, ''))
            ELSE Q.OriginalName
            END
) U

The first just looks at the delimiters. The second looks for the specific sequence of underscore 15 digits dot.

See this db fiddle.