Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function to search string enclosed within brackets Expand / Collapse
Author
Message
Posted Friday, February 27, 2009 1:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 9:15 PM
Points: 112, Visits: 319
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)
Post #665968
Posted Friday, February 27, 2009 2:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 5,565, Visits: 24,711
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

Before posting a performance problem please read
Post #666004
Posted Friday, February 27, 2009 8:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #666109
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse