Styjun

Have a table where one of the columns has all of the info I need for a report.
I want to substring certain portions of this column as a column in this report, but the problem is that this column has results from 3 varying character lengths.

Example:

Row1: 20180101_ABC_12
Row2: 20180102_DEFG_23
Row3: 20180103_HIJKL_45

In this particular example I want the middle portion (eg. ABC) to be a column called 'Initials', problem is I am using CASE logic for each LEN. Not sure else how to achieve this.

My sample query below. It pulls all of the possible options, but as separate columns. What would I need to do to have these 3 options pull into one column, let's call it 'Initials'?

Thanks

SELECT
 FileName
 , CASE WHEN LEN(FileName) = 10 THEN SUBSTRING(FileName, 10, 3) ELSE NULL END
 , CASE WHEN LEN(FileName) = 11 THEN SUBSTRING(FileName, 10, 4) ELSE NULL END
 , CASE WHEN LEN(FileName) = 12 THEN SUBSTRING(FileName, 10, 5) ELSE NULL END
FROM File

In Tableau, you would accomplish this using a calculated field.

Initials:

CASE LEN(FileName)
 WHEN 10 THEN SUBSTRING(FileName, 10, 3)
 WHEN 11 THEN SUBSTRING(FileName, 10, 4)
 WHEN 12 THEN SUBSTRING(FileName, 10, 5)
END

Or maybe

SUBSTRING(FileName
 ,10
 ,CASE LEN(FileName)
 WHEN 10 THEN 3
 WHEN 11 THEN 4
 WHEN 12 THEN 5
 END
 )

But barring the more technical aspect, this can be solved with math (assuming your data is either limited to the 10, 11, and 12, or that the pattern holds):

SUBSTRING(FileName
 ,10
 ,LEN(FileName)-7
 )

You need 1 CASE statement covering every possible case and not 3 separate ones, because each one creates a new column:

SELECT
 FileName
 , CASE LEN(FileName) 
 WHEN 10 THEN SUBSTRING(FileName, 10, 3)
 WHEN 11 THEN SUBSTRING(FileName, 10, 4)
 WHEN 12 THEN SUBSTRING(FileName, 10, 5) 
 ELSE NULL 
 END AS Initials
FROM File

Another way to get everything between the 2 _:

SELECT
 FileName
 , substring(
 left(FileName, len(FileName) - charindex('_', reverse(FileName) + '_')), 
 charindex('_', FileName) + 1,
 len(FileName)
 ) AS Initials
FROM File

but from your logic I assume that the values in column FileName have the same patern:

<9 digits>_<Initials>_<2 digits>

If this is the case then you can get what you want like this:

SELECT
 FileName
 , substring(FileName, 10, len(FileName) - 12) AS Initials
FROM File