By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,906 Members | 1,767 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,906 IT Pros & Developers. It's quick & easy.

REPLACE ISSUE..

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Jul 23 '05 #2

P: n/a
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)
Jul 23 '05 #3

P: n/a
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)


Jul 23 '05 #4

P: n/a
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.
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.