April 17, 2014 at 9:33 am
Hi
I have a field with "Data"
similar to
"@@"@@T_001_Version = "v2009.01.13.1_1.7"@@T_002_ = "@@T_749_formstatus = "0"@@T_750_finalscorestatus = "Draft"@@M_752_ = "*Hx of AH, VH, and d....
Some of these values in the field are large I want to pull only whats in between @@T_750_finalscorestatus = and @@M_752
sorry forgot to mention sometime the field ends on @@T_750_finalscorestatus = "Draft" not being between two values and for that matter not always followed by @@M_752_
so I guess I'm looking for what follows @@T_750_finalscorestatus = " until the closing "
Thanks
Joe
April 17, 2014 at 10:15 am
I have a field with "Data"
similar to
"@@"@@T_001_Version = "v2009.01.13.1_1.7"@@T_002_ = "@@T_749_formstatus = "0"@@T_750_finalscorestatus = "Draft"@@M_752_ = "*Hx of AH, VH, and d....
You gave us some business rules but you also stated that each of these business rules has an exception. Also is the above supposed to represent one of the values?
declare @data varchar(1000)
set @data = ' "@@"@@T_001_Version = "v2009.01.13.1_1.7"@@T_002_ = "@@T_749_formstatus = "0"@@T_750_finalscorestatus = "Draft"@@M_752_ = "*Hx of AH, VH, and d....'
Some of these values in the field are large I want to pull only whats in between @@T_750_finalscorestatus = and @@M_752
Some of them are large and you want only a portion? What defines large?
sorry forgot to mention sometime the field ends on @@T_750_finalscorestatus = "Draft" not being between two values and for that matter not always followed by @@M_752_
HUH???
so I guess I'm looking for what follows @@T_750_finalscorestatus = " until the closing "
HUH???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 10:37 am
Something like this might handle the example problem:
declare @t table (txt varchar(max))
insert into @t(txt) values
('"@@"@@T_001_Version = "v2009.01.13.1_1.7"@@T_002_ = "@@T_749_formstatus = "0"@@T_750_finalscorestatus = "Draft"@@M_752_ = "*Hx of AH, VH, and d....')
select substring(txt, ca1.strStart, ca2.strEnd-ca1.strStart-1)
from @t t
cross apply (select 28+patindex('%@@T_750_finalscorestatus = "%', txt) from @t) ca1(strStart)
cross apply (select ca1.StrStart+patindex('%"%', substring(txt, ca1.strStart, len(txt))) from @t) ca2(strEnd)
April 17, 2014 at 11:15 am
Wow Sean, and I tried real hard on this one... LOL 😀
I guess size really doesn't matter..
what I need are the 5 characters following the string @@T_750_finalscorestatus = " that would be a constant in that field
for example
if its "@@T_750_finalscorestatus = "Draft" then I want .. Draft
if its "@@T_750_finalscorestatus = "Final" then I want .. Final
if its "@@T_750_finalscorestatus = "" then I want null
Thanks
April 17, 2014 at 11:56 am
jbalbo (4/17/2014)
Wow Sean, and I tried real hard on this one... LOL 😀I guess size really doesn't matter..
what I need are the 5 characters following the string @@T_750_finalscorestatus = " that would be a constant in that field
for example
if its "@@T_750_finalscorestatus = "Draft" then I want .. Draft
if its "@@T_750_finalscorestatus = "Final" then I want .. Final
if its "@@T_750_finalscorestatus = "" then I want null
Thanks
hehe.
Maybe something like this is what you are after?
declare @data varchar(1000)
set @data = ' "@@"@@T_001_Version = "v2009.01.13.1_1.7"@@T_002_ = "@@T_749_formstatus = "0"@@T_750_finalscorestatus = "Draft"@@M_752_ = "*Hx of AH, VH, and d....'
select
case when charindex('@@T_750_finalscorestatus = "Draft"', @data) > 0 then 'Draft'
when charindex('@@T_750_finalscorestatus = "Final"', @data) > 0 then 'Final'
else NULL
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2014 at 12:08 pm
Thanks worked great and I learned something new today 🙂
CASE WHEN charindex('@T_750_finalscorestatus = "Draft"', Assessment_data) > 0 THEN 'Draft'
WHEN charindex('@T_750_finalscorestatus = "Final"', Assessment_data) > 0 THEN 'Final'
ELSE NULL END AS SigType
April 17, 2014 at 1:01 pm
jbalbo (4/17/2014)
Thanks worked great and I learned something new today 🙂CASE WHEN charindex('@T_750_finalscorestatus = "Draft"', Assessment_data) > 0 THEN 'Draft'
WHEN charindex('@T_750_finalscorestatus = "Final"', Assessment_data) > 0 THEN 'Final'
ELSE NULL END AS SigType
Cool. Glad that worked for you. You can leave the ELSE off if you want. When a case expression does not find a path it will return NULL.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply