TSQL pull out part of string

How can I get the procedure name only out of my text field? I have a table that list SQL text and I want to pull the procedure name out so basically I need everything after the word 'procedure' ending at the ] symbol. So in my example below I want to pull out the string [dbo].[FileDOCWORKSTranscription]

 CREATE       procedure [dbo].[FileDOCWORKSTranscription]  /* 1  */   @InterfaceSourceID       int  /* 2  */ , @EXTPatientID            varchar (20)  /* 3  */ , @RegSystem               varchar (80)  /* 4  */ , @PatientMRN              varchar (255)  /* 5  */ , @PatientOrg              uniqueid  /* 6  */ , @PatientLastName         varchar (255)  /* 7  */ , @PatientFirstName        varchar (255)  /* 8  */ , @PatientDOB              datetime  /* 9  */ , and encounter.Provider2ID = @AuthorID    and DTTM = @EncounterDTTM    an blah blah blah and so on and so on for many more rows
Who is Participating?
CIC AdminConnect With a Mentor Commented:
Not pretty, but assuming all records will have two sets of brackets, you can try something like :

  substring(textfield, charindex('[', textfield), charindex(']', textfield, charindex(']',textfield)+1  ) - charindex('[', textfield)+1)

Basically, it is just calculating the position of the first open bracket :

     charindex('[', textfield)

and length between the first open bracket and the second closing bracket :

     charindex(']', textfield, charindex(']',textfield)+1  ) - charindex('[', textfield) +1

Once you have those two numbers you can put them into the SUBSTRING function :

     SUBSTRING ( expression, start, length )

If your procedures are displayed with different amounts of brackets, the formula will have to be modified slightly.

Good luck,
CIC AdminCommented:
Did you get a chance to try out my solution?
cheryl9063Author Commented:
Sorry I forgot about this! Thanks!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.