SQL - get the values between the curly brackets from a string

Question

I am using SQL Server 2008.

How can i get the values in between "{" and "}" from a string and put it into a temp table

DECLARE @myString VARCHAR(100) = 'my value {Year}{Month}{Day} sample'
create table #temp(Tag varchar(50))

I need to insert "Year", "Month", "Day" into the temp table from the string @myString

Is there any logic to do this?

Answer 1

Replace all { with <X> and all } with </X>. Cast to XML and shred the xml in a cross apply using nodes(). Extract the value using value().

declare @myString varchar(100) = 'my value {Year}{Month}{Day} sample';

select T2.X.value('.', 'varchar(50)')
from (select cast(replace(replace((select @myString for xml path('')), '{', '<X>'), '}', '</X>') as xml).query('.')) as T1(X)
  cross apply T1.X.nodes('/X/text()') as T2(X);

SQL Fiddle