SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extracting specific text from a string?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 03/04/2013 :  07:04:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/04/2013 :  07:23:38  Show Profile  Reply with Quote
What if there are multiple curly brackets?

PatIndex function ..

Cheers
MIK
Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 03/04/2013 :  07:31:33  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/04/2013 :  07:37:38  Show Profile  Reply with Quote

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

Cheers
MIK
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 03/04/2013 :  07:43:49  Show Profile  Visit webfred's Homepage  Reply with Quote
btw.
{ or } are "curly"


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

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 03/04/2013 :  07:58:22  Show Profile  Visit webfred's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/04/2013 :  10:34:43  Show Profile  Reply with Quote
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 - 03/04/2013 :  10:36:53  Show Profile  Reply with Quote
Thanks guys!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000