REPLACE ISSUE..

Hi All,

I have the following STRING - 'D or Shorty'
Struggling with the syntax required to replace the 'or' with ; without
also replacing the 'or' in 'Shorty' with ;

e.g.

ORIG STRIMG:
D or Shorty

REQUIRED STRING:
D ; Shorty

Thanks in advancehharry (paulquigley@.nyc.com) writes:
> I have the following STRING - 'D or Shorty'
> Struggling with the syntax required to replace the 'or' with ; without
> also replacing the 'or' in 'Shorty' with ;
> e.g.
> ORIG STRIMG:
> D or Shorty
> REQUIRED STRING:
> D ; Shorty

SELECT replace('D or Shorty', ' or ', ' ; ')

I would guess that your real problem is somewhat more complex, but knowing
it, it's difficult to suggest a solution.

One should be aware of that SQL Server capabilities for string
manipulation is not extremely powerful, so depending on your task,
it could be an idea to bring it client-side and use something like
Perl to do whatever you want to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 15 Mar 2005 13:30:37 -0800, hharry wrote:

> Hi All,
> I have the following STRING - 'D or Shorty'
> Struggling with the syntax required to replace the 'or' with ; without
> also replacing the 'or' in 'Shorty' with ;
> e.g.
> ORIG STRIMG:
> D or Shorty
> REQUIRED STRING:
> D ; Shorty
> Thanks in advance

DECLARE @.ss varchar(30)
SET @.ss = 'D or Shorty'
SELECT @.ss, REPLACE(@.ss, ' or ', ' ; ')

---------- --------------
D or Shorty D ; Shorty

(1 row(s) affected)|||Thanks all,

Yes, the issue is slightly more complex and I have went with this
option:

SELECT 'D or Shorty', SUBSTRING('D or Shorty', 1, CHARINDEX(' or ', 'D
or Shorty') - 1) + ' ; ' +
SUBSTRING('D or Shorty', (CHARINDEX(' or ', 'D or Shorty') + 4), LEN('D
or Shorty'))

Ross Presser wrote:
> On 15 Mar 2005 13:30:37 -0800, hharry wrote:
> > Hi All,
> > I have the following STRING - 'D or Shorty'
> > Struggling with the syntax required to replace the 'or' with ;
without
> > also replacing the 'or' in 'Shorty' with ;
> > e.g.
> > ORIG STRIMG:
> > D or Shorty
> > REQUIRED STRING:
> > D ; Shorty
> > Thanks in advance
> DECLARE @.ss varchar(30)
> SET @.ss = 'D or Shorty'
> SELECT @.ss, REPLACE(@.ss, ' or ', ' ; ')

> ----------
--------------
> D or Shorty D ; Shorty
> (1 row(s) affected)|||On 15 Mar 2005 14:47:56 -0800, hharry wrote:

> Thanks all,
> Yes, the issue is slightly more complex and I have went with this
> option:
> SELECT 'D or Shorty', SUBSTRING('D or Shorty', 1, CHARINDEX(' or ', 'D
> or Shorty') - 1) + ' ; ' +
> SUBSTRING('D or Shorty', (CHARINDEX(' or ', 'D or Shorty') + 4), LEN('D
> or Shorty'))

If your issue will come up in a similar way in the future, it might be
worth it to encapsulate that "replace first occurence only" into a UDF.

CREATE FUNCTION ReplaceFirstOccurence
( @.src varchar(8000), @.sub varchar(8000), @.repl varchar(8000) )
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.n int
SET @.n = CHARINDEX(@.sub, @.src)
RETURN SUBSTRING(@.src, 1, @.n-1)
+ @.repl
+ SUBSTRING(@.src, @.n + LEN(@.sub) + 1,8000)
END

A much crazier idea that I just discovered, while googling on this topic,
is to pull in the VBScript regular expression library!

http://www.sqlteam.com/item.asp?ItemID=13947

They only demonstrate using it for testing a string against a regexp, but
it could easily be extended to use replace.