April 9, 2010 at 4:07 am
I have a table with rows contaning text and I need to only return the text between the ()
one of the records is (EBS4) Access to Finance
I just need to return EBS4
So any text between the () needs to be returned without the () and the remaining text outside of the ()
Many thanks in advance
April 9, 2010 at 4:29 am
something like this Simon;
the CHARINDEX function lets you find the position of specific strings within strings; then it is just a bit of tweaking to skip the parts you do not want:
declare @example table(
etext varchar(100) )
insert into @example(etext)
select '(EBS4)' union all
select 'other text and then the (ESS5)' union all
select'non matching criteria'
--use char index twice to make sure you get both start and end.
select
substring(etext,
CHARINDEX('(',etext)+ 1, --where does '(' exist in the string? add one to it to remove from string
CHARINDEX(')',etext) --where is the closing parenthesis? subtract one to it to remove from string
- CHARINDEX('(',etext) -1 --we want the diff between start and end, not jus tthe end to get the substring length
) --close the substring
from @example --don't let it crash with invalid substring...only get recs witht he required parenthsis
WHERE CHARINDEX('(',etext )> 0
and CHARINDEX(')',etext) > CHARINDEX('(',etext)
Lowell
April 9, 2010 at 5:39 am
Hey buddy, here is one piece of code.
I think Lowell and steveb. let out one tiny thing in your request , that is, to add the part of the string that dont come in braces as well along with the data inside the parens. i have handled them as well..
Please inform us if this works
IF OBJECT_ID('TEMPDB..#STRING_PARENS') IS NOT NULL
DROP TABLE #STRING_PARENS
CREATE TABLE #STRING_PARENS
(
STRING VARCHAR(64)
)
INSERT INTO #STRING_PARENS (STRING)
SELECT 'SAVE ENVIRONMENT (AND) SAVE WORLD'
UNION ALL
SELECT '(WE) HAVE TO SAVE OUR PLANET'
UNION ALL
SELECT 'SAVE TIGERS OF INDIA (WE LOVE THEM,DONT WE?)'
UNION ALL
SELECT 'LETS NOT FIGHT FOR PEACE , LETS BE AT PEACE ('
UNION ALL
SELECT ') WAR IS NOT ANSWER '
UNION ALL
SELECT 'LOVE ALL HATE NONE'
SELECT
-- THE BELOW WILL PULL OFF THE DATA TO THE LEFT OF THE '('
LEFT(STRING ,(CHARINDEX('(',STRING)-1))
-- THE BELOW WILL PULL OFF THE DATA BETWEEN '(' AND ')'
+ SUBSTRING(STRING,(CHARINDEX('(',STRING) + 1 ) ,(CHARINDEX(')',STRING) - CHARINDEX('(',STRING) - 1 ))
-- THE BELOW WILL PULL OFF THE DATA TO THE RIGHT OF THE ')'
+ RIGHT(STRING ,(DATALENGTH(STRING) - CHARINDEX(')',STRING)))
-- ALIAS THIS WITH A NICE NAME
AS PARENS_STRIPED_STRING
FROM #STRING_PARENS
WHERE
--MAKE SURE SOME JUNK ROWS ARE TAKEN INTO ACCOUNT
CHARINDEX('(',STRING )> 0
AND
CHARINDEX(')',STRING) > CHARINDEX('(',STRING)
UNION ALL
-- NOW, LETS PUT TOGETHER THE JUNK AS WELL, SHALL WE??
SELECT STRING AS PARENS_STRIPED_STRING
FROM #STRING_PARENS
WHERE
CHARINDEX('(',STRING ) = 0
OR
CHARINDEX(')',STRING) < CHARINDEX('(',STRING)
OR
CHARINDEX(')',STRING ) = 0
IF OBJECT_ID('TEMPDB..#STRING_PARENS') IS NOT NULL
DROP TABLE #STRING_PARENS
On a side note, how about you going thro the following article from Jeff Moden on how to post your question to get the best resuts!!
FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
Inform us if it works!! 🙂
Cheers!!
C'est Pras!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply