Solved

Pattern Searching in SQL

Posted on 2016-07-14
5
39 Views
Last Modified: 2016-07-18
I am trying to figure out how to use the replace function by using a pattern.  There may be another function that can do what I am looking for.  In SQL I have the following string as an example:

20151130\Mailbox[1965661]\Mailbox[1965661]\Top of Information Store[1965662]\Suggested Contacts[1984960]

Open in new window


What I would like to do is remove the characters [ ] and anything in between.  So the output would be:

20151130\Mailbox\Mailbox\Top of Information Store\Suggested Contacts

Open in new window


Any direction would be greatly appreciated.
0
Comment
Question by:rye004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 41711447
you an use STUFF statement for this, run this few time, each time it updates one []
Update your table set YourColumn = STUFF(yourcolumn, CHARINDEX('[', YourColumn),9,'')
where CHARINDEX('[', yourColumn ) > 0

I am assuming that the string length between [ and ] is 8 characters
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 41711452
You will need to use a function since you need the replace to occur multiple times per string.  Below is a sample function that you can adjust to fit your needs by simply changing the delimiter characters in the @Start and @End assignments.

CREATE FUNCTION dbo.StripHTMLTags
(
	@Text NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS

BEGIN
	DECLARE @Start INT,
		@End INT
		
	SET @Start = CHARINDEX('<', @Text)
	SET @End = CHARINDEX('>', @Text, @Start)

	WHILE @Start > 0 AND @End > 0
	BEGIN
		SET @Text = STUFF(@Text, @Start, @End - @Start + 1, '')
		SET @Start = CHARINDEX('<', @Text)
		SET @End = CHARINDEX('>', @Text, @Start)
	END
	RETURN LTRIM(RTRIM(@Text))
END

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41711465
Or make @start and @end parameters :)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41713171
While it won't be the fastest way, doing it recursively is fairly simple.
Before running the code, correct the "UNION A_L_L" -- my filter at work doesn't allow U A.

IF OBJECT_ID('tempdb.dbo.#data') IS NOT NULL
    DROP TABLE #data
CREATE TABLE #data (
    id int IDENTITY(1, 1) PRIMARY KEY,
    string varchar(8000)
    )
INSERT INTO #data VALUES('20151130\Mailbox[1965661]\Mailbox[1965661]\Top of Information Store[1965662]\Suggested Contacts[1984960]')

;WITH cte_replace AS (
    SELECT id, STUFF(string, CHARINDEX('[', string), CHARINDEX(']', string) - CHARINDEX('[', string) + 1, '') AS string, 1 AS level
    FROM #data
    WHERE string LIKE '%\[%\]%' ESCAPE '\'
    UNION A_L_L
    SELECT id, STUFF(string, CHARINDEX('[', string), CHARINDEX(']', string) - CHARINDEX('[', string) + 1, '') AS string, level + 1 AS level
    FROM cte_replace
    WHERE string LIKE '%\[%\]%' ESCAPE '\'
)
SELECT id, string
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY level DESC) AS row_num
    FROM cte_replace
) AS derived
WHERE row_num = 1
0
 

Author Closing Comment

by:rye004
ID: 41717595
This worked great.  Thank you.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question