Function to search string enclosed within brackets

Question

Post reply

Function to search string enclosed within brackets

bdba

SSCommitted

Points: 1504
More actions
February 27, 2009 at 1:04 pm

#384043

Hi,
Looking for help with function.
InputString= John[Sam]Will[Joseph]Smith[Joe]
Output table values:
Sam
Joseph
Joe
Trying the following however unsure of output as table values.
select SUBSTRING(InputString,CHARINDEX('[',InputString)+1,charindex(']',InputString,CHARINDEX('[',InputString))-2)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

I would recommend that your read this article by Jeff Moden

http://www.sqlservercentral.com/articles/T-SQL/63003/

contains enough sample code to do what you want to do

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

I'd definitely suggest you read the article that Bit Bucket pointed you to, but not until you understand how a Tally table actually replaces a loop in situations like this. Read the following article, first...

http://www.sqlservercentral.com/articles/TSQL/62867/

Then, make a permanent Tally table...

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

After that, your problem suddenly becomes child's play, just like the article that Bit Bucket pointed you to...

--===== Simulate a passed parameter

DECLARE @Parameter VARCHAR(8000)

SET @Parameter = 'John[Sam]Will[Joseph]Smith[Joe]'

--===== Suppress the auto-display of rowcounts to keep them from being

-- mistaken as part of the result set.

SET NOCOUNT ON

--===== Get the items in the brackets and number them

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ElementNumber,

SUBSTRING(@Parameter,N+1,CHARINDEX(']',@Parameter,N+1)-N-1) AS ElementValue

FROM dbo.Tally

WHERE N < LEN(@Parameter)

AND SUBSTRING(@Parameter,N,1) = '['

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)