Help with formula 1

[dnfrantum]

I have the following field that I am trying to parse:

SM-13496 (D1050234 - PI )
SM-13496 (D1050234 - Lab )
SM-13496 (D1050234 - Consult )
SM-13496 (D1050234 - CRO )
SM-13496 (D1050234 - CRO/CRU IG )

I want to extract everything after the ' - ' and before the ' )', e.g. PI, Lab, Consult, CRO, CRO/CRU IG

I created this formula, but it isn't working:

RIGHT(LEFT([AcctName],CharIndex(')',[AcctName] + ')',1)-1),CharIndex('(',LEFT([AcctName],CharIndex(')',[AcctName] + ')',1)-1)+ '(',1)-1)

Thanks in advance,
Donald

 

[markros]

substring(AcctName, charindex('-',Acctname) + 1, len(AcctName) - charindex(')', AcctName) + 1)

not tested and also we need to account for no - or no )

 

[gmmastros]

There's a little trick that might work for you. In T-SQL, there is a ParseName function that is meant to parse object names. Ex: LinkedServerName.DatabaseName.Schema.Table. The ParseName function can easily parse this in to it's individual parts.

The challenge you have is caused my the multiple dashes (-) in the data. However, if you replace - with . and use parsename, you just might get what you want.

The following example uses a table variable with hard-coded data to illustrate.

Declare @Temp table(Data VarChar(100))

Insert Into @Temp Values('SM-13496 (D1050234 - PI )')
Insert Into @Temp Values('SM-13496 (D1050234 - Lab )')
Insert Into @Temp Values('SM-13496 (D1050234 - Consult )')
Insert Into @Temp Values('SM-13496 (D1050234 - CRO )')
Insert Into @Temp Values('SM-13496 (D1050234 - CRO/CRU IG )')
Insert Into @Temp Values('')

Select Replace(Data, '-', '.') As Step1,
       ParseName(Replace(Data, '-', '.'), 1) As Step2,
       Replace(ParseName(Replace(Data, '-', '.'), 1), ')', '') As Step3,
       LTrim(RTrim(Replace(ParseName(Replace(Data, '-', '.'), 1), ')', ''))) As Step4
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dnfrantum]

Can I use the +'-' and +')' to account for that?

Thanks in advance,
Donald

 

[dnfrantum]

This what is returned:

SM-13496 (D1050261 - CRO ) 1
SM-13496 (D1050261 - CRO/CRU IG ) 1
SM-13496 (D1050261 - CTM ) 1
SM-13496 (D1050261 - Other ) 1
SMP-028 (D4050076 - PI ) 0
SMP-028 (D4050076 - Lab ) 0
SMP-028 (D4050076 - Consult ) 0
SMP-028 (D4050076 - CRO ) 0
SMP-028 (D4050076 - CRO/CRU IG ) 0
SMP-028 (D4050076 - CTM ) 0
SMP-028 (D4050076 - Other ) 0
SMP-028 (D4050181 - PI ) 0
SMP-028 (D4050181 - Lab ) 0
SMP-028 (D4050181 - Consult ) 0
SMP-028 (D4050181 - CRO ) 0
SMP-028 (D4050181 - CRO/CRU IG ) 0
SMP-028 (D4050181 - CTM ) 0
SMP-028 (D4050181 - Other ) 0
SMP-986 (D3601113 - PI ) 9
SMP-986 (D3601113 - Lab ) 9
SMP-986 (D3601113 - Consult ) 9
SMP-986 (D3601113 - CRO ) 9
SMP-986 (D3601113 - CRO/CRU IG ) 9
SMP-986 (D3601113 - CTM ) 9
SMP-986 (D3601113 - Other ) 9
SMP-986 (D3601212 - PI ) 9
SMP-986 (D3601212 - Lab ) 9
SMP-986 (D3601212 - Consult ) 9
SMP-986 (D3601212 - CRO ) 9
SMP-986 (D3601212 - CRO/CRU IG ) 9
SMP-986 (D3601212 - CTM ) 9
SMP-986 (D3601212 - Other ) 9
SMP-986 (Model/Simulate - PI ) 9
SMP-986 (Model/Simulate - Lab ) 9
SMP-986 (Model/Simulate - Consult ) 9
SMP-986 (Model/Simulate - CRO ) 9
SMP-986 (Model/Simulate - CRO/CRU IG ) 9
SMP-986 (Model/Simulate - CTM ) 9
SMP-986 (Model/Simulate - Other ) 9
AC-3933 (P271 - PI ) 3
AC-3933 (P271 - Lab ) 3
AC-3933 (P271 - Consult ) 3
AC-3933 (P271 - CRO ) 3
AC-3933 (P271 - CRO/CRU IG ) 3
AC-3933 (P271 - CTM ) 3
AC-3933 (P271 - Other ) 3

It looks like it might be parsing the 4th character.

Thanks in advance,
Donald

 

[gmmastros]

Did you try my "ParseName" suggestion?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

declare @t table (Acctname varchar(100))

insert into @t select 'SM-13496 (D1050234 - PI )' union select
'SM-13496 (D1050234 - Lab )' union select 'SM-13496 (D1050234 - Consult )'

select substring(AcctName, charindex(' - ',AcctName) + 2, charindex(')',AcctName) - charindex(' - ',AcctName)- 2) from @t

 

[markros]

George,

Try adding extra information after ), e.g.

SMP-028 (D4050181 - CRO/CRU IG ) More Info

 

[gmmastros]

Markros,

Why would I bother? This data is clearly not normalized because it appears to be storing 3 (or more) separate pieces of information in the same column.

My code, and your code both assume a certain formatting for the data. For example, try your code with:

'SM - 13496 (D1050234 - Consult )'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, would not work in this case as well.

 

[dnfrantum]

Thanks guys, I decided the easiest way to deal with this was to creat a new field in the DB and add the categories at the end of the string. Too much hassle trying to parse it out.

Thanks again...

Thanks in advance,
Donald

 

[gmmastros]

I gave you a star because your final solution is the best way to approach this problem. By properly normalizing the table (not storing multiple items in the same column), you are ultimately making your database better.

It's a thousand times easier to concatenate data (for a report or export) than it is to split data.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, exactly!