How to select a string between 2 identical characters in SQL

sql-server substring charindex
DanteeChaos picture DanteeChaos · Aug 1, 2017 · Viewed 11.1k times · Source

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

Tanveer Badar picture Tanveer Badar · Aug 1, 2017

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.

Related questions

Using CHARINDEX and SUBSTRING in a CASE statement

I need to evaluate a field with a CASE statement. The field name is Commodity and is a varchar(255). The values differ and I need to extract a specific portion from it after a specific character. The character is a …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More