April 3, 2012 at 1:15 pm
I need to trim values returned from rows in the following way
(1) I need any characters leading up to and including the | symbol, removed.
(2) I need to trim anything after a one of two possible strings: AIR_FIR and LCAIR. The row values will look something like this:
FogCategory|AIR_FIR_ Global Reaction Strategy|77899929-5ac8-4fb4-8ede-ca50246411da
TestFogCategory|LCAIR_UseTerms|d145f576-3yb0-4865-9d39-130864zcd3ea
but I need only this:
AIR_FIR
LCAIR
What approach and functions will allow me to retain only the acronyms I'm after?
***I am sorry, at this time I do not have DDL or DML to supply and am hoping you will point me in the direction of function and general statements that may work. If it isn't possible to help me without DDL or DML, I will supply them later in day. Thank you!
-
April 3, 2012 at 1:23 pm
Q1:
What if those characters are not present in a row? what would like it to be returned?
Q2:
Do you want to update your current columns that contains 'AiR_Fir' to just 'air_fir' by removing extra characters? If yes, then it is fairly simple
select case when PATINDEX('%air_fir%',column) > 0 then 'air_fir'
else ''
end
from yourtable
April 3, 2012 at 1:38 pm
I would think you would want more like this:
select
case when PATINDEX('%air_fir%',column) > 0 then 'air_fir'
when PATINDEX('%LCAIR%',column) > 0 then 'LCAIR'
else '' -- or column if you want the data left as is
end
from yourtable
But without more info, who knows.
April 3, 2012 at 6:22 pm
Lynn, thanks. That's helpful. Because those values are taken from a controlled vocabulary (drop down list), I am sure that the case will always be all upper-case etc.
Will you please now point me to a function that will do the following?
(1) Remove the leading characters *before and including* the second underscore
The row values will look something like this before
AIT_FIT_projecto and moonbeat
OEMR_FIT_projectofile where handled
LO_FIT_pritibabe every way
and like this afterward:
projecto and moonbeat
projectofile where handled
pritibabe every way
What function/approach will allow me to shave away the string before the second underscore?
-
April 3, 2012 at 11:57 pm
REPLACE function
April 4, 2012 at 2:35 am
aitchkcandoo (4/3/2012)
Lynn, thanks. That's helpful. Because those values are taken from a controlled vocabulary (drop down list), I am sure that the case will always be all upper-case etc.Will you please now point me to a function that will do the following?
(1) Remove the leading characters *before and including* the second underscore
The row values will look something like this before
AIT_FIT_projecto and moonbeat
OEMR_FIT_projectofile where handled
LO_FIT_pritibabe every way
and like this afterward:
projecto and moonbeat
projectofile where handled
pritibabe every way
What function/approach will allow me to shave away the string before the second underscore?
SELECT SUBSTRING(SUBSTRING(yourOddData, CHARINDEX('_', yourOddData) + 1, LEN(yourOddData)), CHARINDEX('_',
SUBSTRING(yourOddData, CHARINDEX('_', yourOddData) + 1, LEN(yourOddData))) + 1, LEN(yourOddData))
FROM yourMissingSampleData;
April 4, 2012 at 9:46 am
Or this?
; WITH CTE(Strings) AS
(
SELECT 'AIT_FIT_projecto and moonbeat'
UNION ALL SELECT 'OEMR_FIT_projectofile where handled'
UNION ALL SELECT 'LO_FIT_pritibabe every way'
)
SELECT Strings,
Stuffed = STUFF(Strings,1, CHARINDEX ( '_',Strings, CHARINDEX('_',Strings)+1),'')
FROM CTE
April 4, 2012 at 11:34 am
Hmm. I'm not replacing a string (can't use REPLACE), and the string I need to eliminate is of variable length (can't use SUBSTRING, alone at least).
-
April 4, 2012 at 11:45 am
WOW, Cadavre, I spoke too soon. Your solution is very excellent. I have not tried the one by SSCommitted using CTE....but thank you! I want to keep it simple.
Oh thank you so much everyone!
-
April 4, 2012 at 12:02 pm
My solution did not use CTEs, the CTE is built to hold your sample data! 😀
April 4, 2012 at 12:33 pm
OK, well look at that. Thank you Coffee!!!!:laugh:
-
April 4, 2012 at 5:24 pm
Oh for the Love of Mankind, ColdCoffee, your solution is even simpler and works really well. :Wow:
Both cadavre's and coldcoffee's are plug and play solutions, but 3 functions is simpler than 7.
Thanks again everyone.
-
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply