CODEWITHSUNDEEP
sqlcharactersubstring
BeRye
BeRye

Reputation: 39

Substring from right by character SQL

I have a string(s)-

CO_CS_SV_Integrate_WP_BalancingCostRiskandComplexityinYourDRStrat_Apr-Jun

Or

CO_CS_SV_CommVaultTapSponsorship_WP_GartnerNewsletterSmartIdeaforBigData_Jan-Mar

Or

CO_CS_IA_eMedia_WP_Top5eDiscoveryChallengesSolved_Apr-Jun

I need to get the asset name associated with the campaign which is in the campaign name. So for example "Balancing Cost Risk and Complexity in Your DR Strat" would be the asset associated with the first campaign-

"CO_CS_SV_Integrate_WP_BalancingCostRiskandComplexityinYourDRStrat_Apr-Jun"

That is my goal. I want to get just that from the string ("Balancing Cost Risk and Complexity in Your DR Strat". But I don't see how to strip out the asset from the campaign name. It is not consistent on position or anything else??? I think I can go from the right and and find the second "_" But I don't know the syntax. I get as far as -

select campaign.name ,Right (campaign.name, charindex('_', REVERSE(campaign.name))) as Test from campaign

which gives me - _Apr-Jun

Any help or direction would be greatly appreciated

Thanks.

Upvotes: 0

Views: 176

Answers (1)

lombardo
lombardo

Reputation: 429

You could create a scalar function that accept the string, like the following:

CREATE FUNCTION myFunction
(
@str varchar(300)
)
RETURNS varchar(300)
AS
BEGIN

 declare @reverse varchar(200),@idx1 int,@idx2  int

 set @reverse = reverse(@str)

 set @idx1 =  CHARINDEX('_',@reverse)

 set @idx2 =  CHARINDEX('_',@reverse,@idx1+1)

 return  reverse(substring(@reverse,@idx1+1,@idx2-@idx1-1))

END

You can try with the following example:

select dbo.myFunction('CO_CS_SV_Integrate_WP_BalancingCostRiskandComplexityinYourDRStrat_Apr-Jun');

Upvotes: 1

Related Questions