CODEWITHSUNDEEP
sql-serversubstringcharindex
DanteeChaos
DanteeChaos

Reputation: 333

How to select a string between 2 identical characters in SQL

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.

Upvotes: 4

Views: 26081

Answers (2)

Paweł Tajs
Paweł Tajs

Reputation: 482

Solution with SUBSTRING, which you tried at the beginning:

SELECT SUBSTRING(@Attachment,
                 CHARINDEX('"', @Attachment)+1,
                 CHARINDEX('"', @Attachment,CHARINDEX('"', @Attachment)+1)-CHARINDEX('"', @Attachment)-1)

Upvotes: 3

Tanveer Badar
Tanveer Badar

Reputation: 5523

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.

Upvotes: 7

Related Questions