REPLACE ISSUE..

[hharry]

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

[Erland Sommarskog]

hharry (pa*********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ross Presser]

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)

[hharry]

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)

[Ross Presser]

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.