| Author |
 Topic  |
|
|
Maverick_
Yak Posting Veteran
78 Posts |
 Posted - 03/04/2013 : 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
Aged Yak Warrior
Pakistan
827 Posts |
Posted - 03/04/2013 : 07:23:38
|
What if there are multiple curly brackets?
PatIndex function ..
Cheers
MIK |
 |
|
|
Maverick_
Yak Posting Veteran
78 Posts |
Posted - 03/04/2013 : 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 :-) |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
827 Posts |
Posted - 03/04/2013 : 07:37:38
|
select SubString('Joe Bloggs (MGT)'
,Charindex('(','Joe Bloggs (MGT)')
,Charindex(')','Joe Bloggs (MGT)')
)
Cheers
MIK |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/04/2013 : 07:43:49
|
btw.
{ or } are "curly"
Too old to Rock'n'Roll too young to die. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/04/2013 : 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. |
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
827 Posts |
Posted - 03/04/2013 : 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 |
|
|
|
Maverick_
Yak Posting Veteran
78 Posts |
Posted - 03/04/2013 : 10:36:53
|
| Thanks guys! |
|
|
| |
Topic |
|
Extracting specific text from a string?
