Pattern Searching in SQL
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:
What I would like to do is remove the characters [ ] and anything in between. So the output would be:
Any direction would be greatly appreciated.
20151130\Mailbox[1965661]\Mailbox[1965661]\Top of Information Store[1965662]\Suggested Contacts[1984960]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 ContactsAny direction would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or make @start and @end parameters :)
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.#dat
DROP TABLE #data
CREATE TABLE #data (
id int IDENTITY(1, 1) PRIMARY KEY,
string varchar(8000)
)
INSERT INTO #data VALUES('20151130\Mailbox[1
;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
Before running the code, correct the "UNION A_L_L" -- my filter at work doesn't allow U A.
IF OBJECT_ID('tempdb.dbo.#dat
DROP TABLE #data
CREATE TABLE #data (
id int IDENTITY(1, 1) PRIMARY KEY,
string varchar(8000)
)
INSERT INTO #data VALUES('20151130\Mailbox[1
;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
ASKER
This worked great. Thank you.
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