Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extracting specific text from a string?

Author  Topic 

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-04 : 07:04:56
Hi all,

I am trying to write an SQL that extracts a piece of text inside full text. See example below:

officer_name
Joe Bloggs (MGT)

In the officer_name field above I am trying to write an SQL that searches for the text inside the curly brackets (MGT) and ignore the rest of the text.

Does anyone know how to do this in SQL and able to share?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 07:23:38
What if there are multiple curly brackets?

PatIndex function ..

Cheers
MIK
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-04 : 07:31:33
Hi MIK, In the data I have there won't be multiple curly brackets. The text will always have one open and one closed brackets :-)
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 07:37:38

select SubString('Joe Bloggs (MGT)'
,Charindex('(','Joe Bloggs (MGT)')
,Charindex(')','Joe Bloggs (MGT)')
)

Cheers
MIK
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-04 : 07:43:49
btw.
{ or } are "curly"


Too old to Rock'n'Roll too young to die.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-04 : 07:58:22
quote:
Originally posted by MIK_2008


select SubString('Joe Bloggs (MGT)'
,Charindex('(','Joe Bloggs (MGT)')
,Charindex(')','Joe Bloggs (MGT)')
)

Cheers
MIK


For substring() START and LENGTH is needed.
Your second charindex() isn't giving the LENGTH.

-- make some sample data
declare @sample table (id int identity(1,1), officer_name varchar(255))
insert @sample (officer_name)
select 'Joe Bloggs (MGT)' union all
select 'Pepper (Sgt)' union all
select 'no parentheses' union all
select 'parentheses (are) in the middle'

-- the solution
select
id,
substring(officer_name,charindex('(',officer_name)+1,(charindex(')',officer_name)-charindex('(',officer_name))-1)
from @sample
where officer_name like '%(%)%'



Too old to Rock'n'Roll too young to die.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-04 : 10:34:43
Webfred, You are correct, I just mixed it up..

Here's the updated one

select SubString('Joe Bloggs (MGT)'
,Charindex('(','Joe Bloggs (MGT)')
,Charindex(')','Joe Bloggs (MGT)') -Charindex('(','Joe Bloggs (MGT)')+1)

thanks to Webfred for catching it :)

Cheers
MIK
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 2013-03-04 : 10:36:53
Thanks guys!
Go to Top of Page
   

- Advertisement -