Extracting a single line value from a multi-line string T-SQL-CodePudding

I have a multi-line nvarchar that contains a string in the below format:

@String =  
'ReportHeaderColor{R:98, B:183, G:178} 
ReportHeaderFontColor{R:255, B:255, G:255} 
ReportBodyHeaderColor{R:98, B:183, G:178} 
ReportBodyFontColor{R:255, B:255, G:255}'

I want to be able to use a nvarchar value @Attribute = 'ReportHeaderFontColor' and extract just the line from @String that contains the @Attribute value

CodePudding user response：

Just another approach

Example

Declare @String varchar(max)=  
'ReportHeaderColor{R:98, B:183, G:178}
ReportHeaderFontColor{R:255, B:255, G:255}
ReportBodyHeaderColor{R:98, B:183, G:178}
ReportBodyFontColor{R:255, B:255, G:255}'

Declare @Find varchar(150) = 'ReportHeaderFontColor'

Select value
 From  string_split(replace(replace(@String,'}','}|'),char(13) char(10),''),'|')
 where charindex(@Find,value)>0

Results

value
ReportHeaderFontColor{R:255, B:255, G:255}

CodePudding user response：

How about the following using apply to find the begin/end positions to find and substring to extract:

declare @Attribute nvarchar(100)= 'ReportHeaderFontColor',

@String nvarchar(max) =
'ReportHeaderColor{R:98, B:183, G:178}
ReportHeaderFontColor{R:255, B:255, G:255}
ReportBodyHeaderColor{R:98, B:183, G:178}
ReportBodyFontColor{R:255, B:255, G:255}';

with s as (select @string as string)
select Substring(string,b.pos,e.pos-b.pos 1)
from s
cross apply(values(charindex(Concat('ReportBodyHeaderColor','{'), string)))b(pos)
cross apply(values(CharIndex('}', string, b.pos)))e(pos)
where b.pos>0;

Result:

ReportHeaderFontColor{R:255, B:255, G:255}

CodePudding user response：

you can split the string by line feed char(10) and then remove carriage return char(13) as follows:

Declare @Attribute nVarChar(50)= 'ReportHeaderFontColor'
Declare @String nVarChar(max) =  
'ReportHeaderColor{R:98, B:183, G:178} 
ReportHeaderFontColor{R:255, B:255, G:255} 
ReportBodyHeaderColor{R:98, B:183, G:178} 
ReportBodyFontColor{R:255, B:255, G:255}'

Select Replace(Value,Char(13),'') As Result
From String_Split(@String,Char(10))
Where Value Like Concat('%',@Attribute,'%')

Result
ReportHeaderFontColor{R:255, B:255, G:255}