Remove text within braces in a string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Remove text within braces in a string

Author

Topic

radoslav
Starting Member

17 Posts

Posted - 2006-02-16 : 12:30:28

Hi Folks,

I’m using Microsoft SQL and have the following data in a filed:

EP123456(A1,A2)

I want my select statement to ignore everything within the braces (A1,A2). I know that is possible with ‘like’, but I was wondering if there is another way because ‘like’ doesn’t work for me all of the time.

Thank you in advance!
Rocko

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 13:03:37

There may be more efficient methods, but this UDF will do the trick (it replaces multiple occurrences)


--
PRINT 'Create function kk_fn_UTIL_ReplacePattern'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_ReplacePattern]') 
  AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.kk_fn_UTIL_ReplacePattern
GO

CREATE FUNCTION dbo.kk_fn_UTIL_ReplacePattern
(
	@strData		varchar(8000),	-- String Data
	@strReplacePattern	varchar(8000),	-- Character set to remove - e.g. '[;,.]' or '[^A-Za-z0-9]'
	@strReplaceWith		varchar(8000)	-- Replace string - Empty string to remove, or  e.g. ' '
						-- Make sure that the Replace Pattern includes the ReplaceString!
)
RETURNS varchar(8000)
/* WITH ENCRYPTION */
AS
/*
 * kk_fn_UTIL_ReplacePattern	Remove Character Set from a String
 *
 * Returns:
 *
 * varchar(8000)
 *
 * HISTORY:
 *
 * 31-Oct-2005 Started
 */
BEGIN
DECLARE	@intLoop	int

	SELECT	@strReplacePattern = '%' + @strReplacePattern + '%'
	SELECT	@intLoop = PATINDEX(@strReplacePattern, @strData)
	WHILE @intLoop > 0
	BEGIN
		-- Need to determine the length of the substring matched
		SELECT @strData = STUFF(@strData, @intLoop, 1, @strReplaceWith)
		SELECT @intLoop = PATINDEX(@strReplacePattern, @strData)
	END

	RETURN @strData

/** TEST RIG

-- Remove all NON AlphaNumeric
SELECT	dbo.kk_fn_UTIL_ReplacePattern('!ABC;DEF<>xyz?', '[^a-zA_Z0-9]', '', NULL)

-- Remove ;<>
SELECT	dbo.kk_fn_UTIL_ReplacePattern('!ABC;DEF<>xyz?', '[;<>]', '', NULL)

-- Remove all NON-digits
SELECT	dbo.kk_fn_UTIL_ReplacePattern('   123456 .', '[^0-9]', '', NULL)

-- Replace all NON alpha-numerics (inc. spaces) with space
SELECT	dbo.kk_fn_UTIL_ReplacePattern('John, Smith-Jones', '[^a-zA_Z0-9 ]', ' ', NULL)

**/
--==================== kk_fn_UTIL_ReplacePattern ====================--
END
GO
PRINT 'Create function kk_fn_UTIL_ReplacePattern DONE'
GO
--

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-16 : 13:03:39

If you only have 1 instance of the text like "EP123456(A1,A2)" then STUFF may do the trick:


select stuff(myColumn, charindex('(', myColumn), charindex(')', myColumn), '')
from	(select 'EP123456(A1,A2)abc' myColumn union all
	select 'EP123456(A1,A2)') a

output:
EP123456
EP123456

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2006-02-16 : 13:18:07

Thanks Folks. It works for me excellent!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-17 : 08:37:34

If you want to omit only those in braces and keep rest, then



select 	mycolumn as Full_data,substring(myColumn, 1,charindex('(', myColumn)-1)+
	substring(myColumn, charindex(')', myColumn)+1,len(myColumn)) as Partial_data from 
(
	select 'EP123456(A1,A2)test' myColumn union all
	select 'EP123456(A1,A2)other_test'
) a

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-17 : 11:28:06

thanks Maddy, I actually intended to do that with my STUFF version. Here is the corrected code:


select	stuff(myColumn, s, l, '')
from	(
	select	myColumn
		,charindex('(', myColumn) s
		,charindex(')', myColumn) - charindex('(', myColumn) + 1 l
	from	(select 'EP123456(A1,A2)abc' myColumn union all
		select 'EP123456(A1,A2)') a
	) a

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-02-17 : 13:36:20

Note that it will fail if there is a ")" before the "("

e.g. 'EP)123456(A1,A2)'

Mine doesn't work either because I've hard coded the replace length as one

Is there a way to work out the length of the sub-string that PATINDEX has matched?

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-17 : 14:20:26

good catch, Kristen. I guess you could modify mine by using patindex instead of charindex to make sure we're looking at a full '()' set as well as ignore values that don't have any parenthesis. (I was already calculating the length of the substring to replace)


select	stuff(myColumn, s, l, '')
from	(
	select	myColumn
		,patindex('%(%)%', myColumn) s
		,charindex(')', myColumn, patindex('%(%)%', myColumn)) - patindex('%(%)%', myColumn) + 1 l
	from	(select 'EP123456(A1,A2)abc' myColumn union all
		select 'EP123456(A1,A2)' union all
		select 'EP)123456(A1,A2)' union all
		select 'EP)12()3456' union all
		select '123456') a
	) a
where	s > 0

EDIT:
Of course mine still only handles 1 occurence

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-02-17 : 21:38:00

We must be getting close to needing to call a COM object so we can do a proper RegEx job!

Kristen

radoslav
Starting Member

17 Posts

Posted - 2006-03-09 : 16:06:48

Hi TG,

As you mentioned you query will ignore all values that don’t have parenthesis (). I would like to get values with parenthesis as well. How should looks like your query if you want to list 123456' as well?
If you change your code like s >= 0 instead of s > 0 '123456' will display as null. I would like to be the same - '123456'.

Thanks!
Rocko

quote:
Originally posted by TG

good catch, Kristen. I guess you could modify mine by using patindex instead of charindex to make sure we're looking at a full '()' set as well as ignore values that don't have any parenthesis. (I was already calculating the length of the substring to replace)

select stuff(myColumn, s, l, '')
from (
select myColumn
,patindex('%(%)%', myColumn) s
,charindex(')', myColumn, patindex('%(%)%', myColumn)) - patindex('%(%)%', myColumn) + 1 l
from (select 'EP123456(A1,A2)abc' myColumn union all
select 'EP123456(A1,A2)' union all
select 'EP)123456(A1,A2)' union all
select 'EP)12()3456' union all
select '123456') a
) a
where s > 0

EDIT:
Of course mine still only handles 1 occurence

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-09 : 16:14:34

Is this what you're looking for?


select	case
	when s > 0 then stuff(myColumn, s, l, '')
	else myColumn
	end
from	(
	select	myColumn
		,patindex('%(%)%', myColumn) s
		,charindex(')', myColumn, patindex('%(%)%', myColumn)) - patindex('%(%)%', myColumn) + 1 l
	from	(select 'EP123456(A1,A2)abc' myColumn union all
		select 'EP123456(A1,A2)' union all
		select 'EP)123456(A1,A2)' union all
		select 'EP)12()3456' union all
		select '123456') a
	) a

output:
----------------
EP123456abc
EP123456
EP)123456
EP)123456
123456

Be One with the Optimizer
TG

radoslav
Starting Member

17 Posts

Posted - 2006-03-09 : 16:26:44

TG,

I was thinking that you can define it in the function, but the above solution is working for me as well.

Good Job!
Thanks!
Rocko

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources