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.
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_nameJoe 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 ..CheersMIK |
|
|
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 :-) |
|
|
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)') )CheersMIK |
|
|
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. |
|
|
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)') )CheersMIK
For substring() START and LENGTH is needed.Your second charindex() isn't giving the LENGTH.-- make some sample datadeclare @sample table (id int identity(1,1), officer_name varchar(255))insert @sample (officer_name)select 'Joe Bloggs (MGT)' union allselect 'Pepper (Sgt)' union allselect 'no parentheses' union allselect 'parentheses (are) in the middle'-- the solutionselect id,substring(officer_name,charindex('(',officer_name)+1,(charindex(')',officer_name)-charindex('(',officer_name))-1)from @samplewhere officer_name like '%(%)%' Too old to Rock'n'Roll too young to die. |
|
|
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 oneselect SubString('Joe Bloggs (MGT)',Charindex('(','Joe Bloggs (MGT)'),Charindex(')','Joe Bloggs (MGT)') -Charindex('(','Joe Bloggs (MGT)')+1)thanks to Webfred for catching it :)CheersMIK |
|
|
Maverick_
Posting Yak Master
107 Posts |
Posted - 2013-03-04 : 10:36:53
|
Thanks guys! |
|
|
|
|
|
|
|