Replacing the substring - 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)

Replacing the substring

Author

Topic

Ramnadh
Starting Member

23 Posts

Posted - 2004-05-10 : 05:53:32

Hi

I have problem with replacing the substring starts with $ and ends with $.

For example i will get the strings like these

Provide Ranking $Form$
The thing $System$ is for Developers

The output should be :

Provide Ranking for
The thing is for Developers

I want to nullify the string between the wildcharacters.
Can anyone help me.

Ramnadh

tinks
Starting Member

34 Posts

Posted - 2004-05-10 : 06:33:14

you could write something like this :

declare @string varchar(999) /*string to manipulate*/
,@start integer
,@end integer

select @string = 'Provide Ranking $Form$'
select @start = charindex('$',@string,0)
select @end = charindex('$',@string,@start + 1 )
select @start
select @end
select @string = substring(@String,0,@start) + substring(@String,@end + 1 ,999)

select @string

Taryn-Vee
@>-'-,---

Ramnadh
Starting Member

23 Posts

Posted - 2004-05-10 : 06:44:01

Hi,
Thanx for your reply

But the string comes from the table in different rows i have to calculate for every row.

So i need that one in a single query.

quote:
Originally posted by tinks

you could write something like this :

declare @string varchar(999) /*string to manipulate*/
,@start integer
,@end integer

select @string = 'Provide Ranking $Form$'
select @start = charindex('$',@string,0)
select @end = charindex('$',@string,@start + 1 )
select @start
select @end
select @string = substring(@String,0,@start) + substring(@String,@end + 1 ,999)

select @string

Taryn-Vee
@>-'-,---

Ramnadh

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-10 : 07:25:17

select replace(MyString, '$', '')

Duane.

tinks
Starting Member

34 Posts

Posted - 2004-05-10 : 09:22:28

quote:
Originally posted by ditch

select replace(MyString, '$', '')

Duane.

Would that not only work with predefined values between the $? will there always be a space before the first $ and a space after the second $? or no space before the second - then perhaps you could do something like this ....

select @string = substring(@String,0,charindex('$',@string,0)) + substring(@String, charindex('$',@string, (charindex('$',@string,0)+ 1))+ 1,999)

a bit nasty but it seems to do the job on my side!

Taryn-Vee
@>-'-,---

Kristen
Test

22859 Posts

Posted - 2004-05-21 : 13:51:36

quote:
I have problem with replacing the substring starts with $ and ends with $.

You probably need to use STUFF() together with some PATINDEX or CHARINDEX searches to calculate the START and LENGTH parameters (expect to repeat the PAT/CHARINDEX sub-expressions several times in order to get the LENGTH :-( ). If you need to replace multiple occurences you will have to loop round until you can no longer find any rows


WHERE MyColumn LIKE '%$%$%'

The job might be easier if the Start/End delimiter were different - if you have the opportunity to influence that?

Kristen

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-21 : 14:14:49

[code]
--Create tally table used in below select
create table numbers ( n int primary key)
declare @n int; set @n = 0
while @n <=1000
begin
insert into numbers
select @n
set @n = @n+1
end

--create test data
create table t ( val varchar(50))

insert into t
select 'Provide Ranking $Form$' union
select 'The thing $System$ is for Developers'

--perform select
select left(val,lb-2) + substring(val,ub+1,len(val))
from
(
select val,min(c) lb,max(c) ub
from
(
select distinct val,charindex('$',val,n) c
from t, numbers n
where n.n <= len(val)
and n.n > 0
) d
where c > 0
group by val
) d

drop table t[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-21 : 23:22:14

keep it simple -- just write a UDF that does the replacement .

- Jeff

Loknath
Starting Member

2 Posts

Posted - 2004-05-24 : 05:01:28

you could write something like this :

declare @string varchar(999) /*string to manipulate*/

You can find the string starts with $ and ends with $ by using following condition
charindex('$',@string)=1 and
charindex('$',reverse(@string))=1

reply me if any ambiguity..
cheers..
Loknath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources