How to select a string between 2 identical characters in SQL

Question

My initial string is

Content-Disposition: attachment; filename="0001.zam"

I want to select everything between the two " characters ("0001.zam" in this case). I know that I need to use the SUBSTRING and CHARINDEX functions similar to:

SELECT SUBSTRING(@Attachment, CHARINDEX('"', @Attachment),...)

I can't figure out what to pass as the second SUBSTRING argument. Note that the string between the two " characters and the string after the second " character are variable. The entire string can look eg. like this:

Content-Disposition: attachment; filename="0001556.txt"; size=187;

The bottom line is to get everything between the two " characters.

Answer 1

Another way to get the data you want it to use left() and right() functions.

select left(right(t, len(t)- CHARINDEX('"', t)), charindex('"',right(t, len(t)- CHARINDEX('"', t)))-1)
from
(
select 'Content-Disposition: attachment; filename="0001.zam"' t
) u

This outputs

0001.zam

I am hoping, rather than assuming, that there are only two " in this header.