REPLACE SUBSTRING BETWEEN TWO CHARACTERS

24 Nov 2005 12:04 PM

stelioshalkiotis@yahoo.gr

Hi.

I have to replace substrings which are between two characters.
For example: I have to replace or remove all the characters that are
between ' \':
So, i have the string abcde\fgh\ikl and i want to get: abcdeikl.

I use the following select statement:

REPLACE(ColumnName, SUBSTRING([ColumnName, CHARINDEX('\', ColumnName),
CHARINDEX('\', ColumnName, CHARINDEX('\', ColumnName) + 1) -
CHARINDEX('\', ColumnName + 1), '')

Is there any more efficient way?

Thanks.

24 Nov 2005 12:34 PM

Uri Dimant

Hi
declare @d varchar(50)
set @d='abkcde\fgh\ikl'

select STUFF(@d,start,endpos-start,'')
from
(
select charindex('\',@d,1) as start,
len(@d)-charindex('\',reverse(@d),1)+2 as endpos
) as der

<stelioshalkio***@yahoo.gr> wrote in message
Show quote

news:1132833870.358155.145790@g47g2000cwa.googlegroups.com...
> Hi.
>
> I have to replace substrings which are between two characters.
> For example: I have to replace or remove all the characters that are
> between ' \':
> So, i have the string abcde\fgh\ikl and i want to get: abcdeikl.
>
> I use the following select statement:
>
> REPLACE(ColumnName, SUBSTRING([ColumnName, CHARINDEX('\', ColumnName),
> CHARINDEX('\', ColumnName, CHARINDEX('\', ColumnName) + 1) -
> CHARINDEX('\', ColumnName + 1), '')
>
> Is there any more efficient way?
>
> Thanks.
>

24 Nov 2005 2:16 PM

Madhivanan

or

declare @d varchar(50)
set @d='abkcde\fgh\ikl'

Select
Substring(@d,1,charindex('\',@d)-1)+reverse(Substring(reverse(@d),1,charindex('\',reverse(@d))-1))

Madhivanan