Return the Text between the () from a varchar (50)

Question

Return the Text between the () from a varchar (50)

Northern Monkey

Ten Centuries

Points: 1017
More actions
April 9, 2010 at 4:07 am

#138235

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steveb. SSC-Forever Points: 46733 More actions · Answer 2

try

DECLARE @STR VARCHAR(100)

SET @STR = '(EBS4) Access to Finance'

SELECT SUBSTRING(@str,CHARINDEX('(',@str) + 1,CHARINDEX(')',@str)-2)

though this will only work when there is one set of '(' + ')' any changes in this and the results may vary

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

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!!!