String Replace - SQL Server Forums

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)

String Replace

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-04-08 : 14:01:44

I need to replace a string that occurs within a string - in particular it may be at the start, middle, end or be the total string. I've been messing around with charindex etc. but the resulting code is about 9 yards long, allowing for edge conditions, and I'm sure there's a slicker way.

DECLARE @strThingie varchar(1000)
SELECT @strThingie = 'foo=foo&XXX=12345&bar=bar'
SELECT @strThingie = 'XXX=12345&bar=bar'
SELECT @strThingie = 'XXX=12345'

I want to replace the XXX=12345. The "XXX=" bit will be constant, the 12345 can be any string of digits. There will be no intervening spaces / punctuation etc.

Thanks

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-08 : 14:29:58

will this do?


DECLARE @strThingie varchar(1000)
SELECT @strThingie = 'foo=foo&XXX=12345&bar=bar'
SELECT @strThingie = 'XXX=12345&bar=bar'
SELECT @strThingie = 'XXX=12345'
SELECT @strThingie, 
	charindex( 'XXX=', @strThingie),
	charindex('&', @strThingie, charindex('XXX=', @strThingie)),
	replace(@strThingie, substring(@strThingie, 
		charindex( 'XXX=', @strThingie), 
		case 	when charindex( '&', @strThingie, charindex(@strThingie, 'XXX=')) = 0 
				then len(@strThingie)
			else charindex( '&', @strThingie, charindex( 'XXX=', @strThingie))-charindex( 'XXX=', @strThingie)
		end+1),
		'')

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-08 : 18:50:50

I don't know if these would qualify as "slicker" but here a few set-based versions. Not 9 yards, closer to a football field long: (but it was a good way to end a Friday at work. thanks, Kristen)


set nocount on
declare @thingies Table (rowid int identity(1,1), strThingie varchar(1000))
insert @thingies
select 'foo=foo&XXX=12&bar=bar' union
select 'XXX=12345&bar=bar' union
select 'foo=foo&bar=bar&XXX=12345' union
select 'XXX=12345' union
select 'foo=foo&bar=bar' union
select 'name=Kristen&XXX=Spirit1&bar=bar' union
select 'name=TG&bar=bar&XXX=Spirit1' union
select ''

--================================================================================
--version 1
select	rowid = coalesce(a.rowid, b.rowid)

	,newVal = isNull(a.part,'') 
		+ case when a.part + b.part is NULL then '' else '&' end 
		+ isNull(b.part,'')

	,strThingie = coalesce(a.strThingie, b.strThingie)
from	(--segment left of 'XXX='
	Select	rowid
		,part = substring(strThingie,1,charindex('XXX=',strThingie)-2) 
		,strThingie
	from	@Thingies 
	where	charindex('XXX=',strThingie) > 1
	)a
Full JOIN	(--segment right of 'XXX='
	Select	rowid
		,part = substring(strThingie, charindex('&', strThingie, charindex('XXX=',strThingie)) + 1,1000) 
		,strThingie
	from	@Thingies 
	where	charindex('&', strthingie, charindex('XXX=',strThingie)) > 0
	)b
		ON a.rowid = b.rowid

union all

--empty string for when no other values than 'XXX='
Select	rowid
	,''
	,strThingie 
From	@Thingies 
where	charindex('XXX=',strThingie) = 1 
AND	charindex('&',strThingie) = 0

order by 1


--=====================================================================
--Spirit1-esque version
select	RowiD
	,startchar
	,length
	,newVal = case 
		when left(newVal,1) = '&' then right(newVal,len(newval)-1)
		when right(newVal,1) = '&' then left(newVal, len(newVal)-1)
		else newVal
		End
	,strthingie
	,replacedVal
From	(
	Select	Rowid
		,strThingie
		,startchar
		,length
		,newVal = replace(replace(strThingie, substring(strThingie,startchar,length),''),'&&','&')
		,replacedVal = substring(strThingie,startchar,length)
	From
		(
		select	Rowid
			,startchar = charindex('XXX=',strThingie)
			,length = 
				case 
				when charindex('XXX=',strThingie) > 0 then 
					case 
					when charindex('&', strThingie, charindex('XXX=',strThingie)) = 0 
						then len(strThingie) - charindex('&', strThingie, charindex('XXX=',strThingie))
					else charindex('&', strThingie, charindex('XXX=',strThingie)) - charindex('XXX=',strThingie)
					end
				else 0 
				end
			,strThingie
		from @thingies
		) a
	) a


--================================================================================
--Over the top, Nigel-esque version
Select	rowid = coalesce(a.rowid, b.rowid)

	,newVal = isNull(a.part,'') 
		+ case when a.part + b.part is NULL then '' else '&' end 
		+ isNull(b.part,'')

	,strThingie = coalesce(a.strThingie, b.strThingie)

From	(--part left of 'XXX='
	select	rowid, substring(strthingie,1,n) part, strThingie
	From	(
		select n1+n2+n3+n4+n5+n6 n from
		(Select 0 n1 union select 1) n1 cross join
		(Select 0 n2 union select 2) n2 cross join
		(Select 0 n3 union select 4) n3 cross join
		(Select 0 n4 union select 8) n4 cross join
		(Select 0 n5 union select 16) n5 cross join
		(select 0 n6 union select 32) n6
		) nums
	cross join @thingies 
	Where	n = charindex('XXX=', strThingie) -2
	) a
full JOIN	(--part right of 'XXX='
	select	rowid, substring(strthingie,n,1000) part, strThingie
	From	(
		select n1+n2+n3+n4+n5+n6 n from
		(Select 0 n1 union select 1) n1 cross join
		(Select 0 n2 union select 2) n2 cross join
		(Select 0 n3 union select 4) n3 cross join
		(Select 0 n4 union select 8) n4 cross join
		(Select 0 n5 union select 16) n5 cross join
		(select 0 n6 union select 32) n6
		) nums
	cross join @thingies 
	Where	n = charindex('&', strThingie, charindex('XXX=', strThingie)) + 1
	AND	n > 1
	AND	charindex('XXX=', strThingie) > 0
	) b
ON a.rowid = b.rowid

UNION all

--empty string for when no other values than 'XXX='
Select	rowid
	,''
	,strThingie 
From	@Thingies 
where	charindex('XXX=',strThingie) = 1 
AND	charindex('&',strThingie) = 0

order by 1

Be One with the Optimizer
TG

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-04-08 : 19:22:58

Would you accept code that is 8 yards long? This is more generic in that it doesn't look for a specific character after the '12345':

SELECT CASE WHEN PATINDEX('%[^0-9]%', RIGHT(@strThingie, LEN(@strThingie) - PATINDEX('%XXX=%', @strThingie) - LEN('XXX=') +1)) = 0
THEN LEFT(@strThingie, CHARINDEX('XXX=', @strThingie) -1)
ELSE STUFF(@strThingie,
PATINDEX('%XXX=%', @strThingie),
LEN('XXX=') + PATINDEX('%[^0-9]%', RIGHT(@strThingie, LEN(@strThingie) - PATINDEX('%XXX=%', @strThingie) - LEN('XXX=') +1)) -1,
'')
END

Sorry, I can't get this text to format readably.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-08 : 20:50:36

put you code inside [code ][/code] tags to maintain formatting. (see faq)

I like that it's only 8 yards long but you still got a couple issues, nosepicker:

XXX=12345&bar=bar
&bar=bar

foo=foo&XXX=12345&bar=bar
foo=foo&&bar=bar

bar=bar&XXX=12345
bar=bar&

Be One with the Optimizer
TG

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-04-09 : 23:36:09

When Kristen wrote: "There will be no intervening spaces / punctuation etc.", I thought she meant that there won't be any spaces or punctuation to mark the beginning or end of the 'XXX=12345'. I guess I interpreted that statement incorrectly. Therefore, if punctuation has to be removed, are there any rules as to where they may appear? Otherwise, if they can appear anywhere in the string, or if the punctuation can be any type of punctuation, that will make them hard to remove.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-10 : 07:42:53

I think it's just "&" that will appear as a seperator for name-value pairs (like a query string). You're 99% there. Probably just need a few minor adjustments.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-10 : 09:17:16

oh, and Kristen is a HE, nosepicker.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-04-10 : 14:27:03

"Would you accept code that is 8 yards long"

Well .... does it effect performance that in Spirit1's code

charindex( 'XXX=', @strThingie)

appears 3 times? Should I pre-calculate that in a separate @variable?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-10 : 15:44:30

probably faster


declare @start int, @end int
set @start = charindex( 'XXX=', @strThingie)
set @end = charindex('&', @strThingie, @start)
SELECT @strThingie, 
	@start,
	@end,
	replace(@strThingie, substring(@strThingie, 
		@start, 
		case 	when @end = 0 
				then len(@strThingie)
			else @end - @start
		end+1),
		'')

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-04-10 : 16:29:45

Actually it seems that I can do it all in one strike. Hadn't tried that before


DELCARE	@start int, @end int
SELECT	@start = charindex( 'XXX=', @strThingie),
	@end = charindex('&', @strThingie, @start),
	@strThingie = replace(@strThingie, substring(@strThingie, 
		@start, 
		case 	when @end = 0 
				then len(@strThingie)
			else @end - @start - 1
		end+1),
		'')
SELECT	@start,
	@end,
	@strThingie

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-10 : 16:30:37

Getting there.. getting there...

DECLARE @replaceby VARCHAR(10)
SET @replaceby = '----'

DECLARE @thingytable TABLE(thingie VARCHAR(1000))
INSERT @thingytable(thingie)
SELECT 'XXX=12345&foo=foo&bar=bar' UNION ALL
SELECT 'foo=foo&XXX=12345&bar=bar' UNION ALL
SELECT 'foo=foo&bar=bar&XXX=12345' UNION ALL
SELECT 'XXX=12345' UNION ALL
SELECT 'XXX=' UNION ALL
SELECT '' UNION ALL
SELECT 'fff'

SELECT
	thingie
	,CASE ix1
		WHEN 0 THEN thingie
		ELSE STUFF(	thingie
				,CASE ix1 WHEN 1 THEN 1 ELSE ix1-1 END
				,CASE ix2 WHEN 0 THEN l-ix2 ELSE 1+ix2-ix1 END
				,@replaceby)
	END AS replaced_thingie
	--,ix1,ix2,l
FROM
	(
		SELECT
			thingie
			,CHARINDEX('XXX=',thingie) AS ix1
			,CHARINDEX('&',thingie,CHARINDEX('XXX=',thingie)) AS ix2
			,LEN(thingie) AS l
		FROM
			@thingytable
	) AS t