471,075 Members | 850 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

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 advance

Jul 23 '05 #1
4 1274
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
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
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
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.

Similar topics

7 posts views Thread by Iain King | last post: by
11 posts views Thread by Joe HM | last post: by
2 posts views Thread by Todd Price | last post: by
2 posts views Thread by Neeta | last post: by
14 posts views Thread by Adrienne Boswell | last post: by
5 posts views Thread by shapper | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.