Solved

replace('{here it is' , '{', '^') doesn't work

Posted on 2008-10-07
9
215 Views
Last Modified: 2010-03-20
i'm trying to do a replacement function and it works for everything but { characters
so i thought i would fake it out and replace the { and } with ^
but i get an incorrect syntax
where @originalcode is the string
Set @OriginalCode = Replace(@OriginalCode, '{', '^^')
Set @OriginalCode = Replace(@OriginalCode, '}', '^')
0
Comment
Question by:quigleyryan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22663698
Not sure... Because this works:
declare @originalcode nvarchar(max)
set @originalcode = '{here i}t is'
set @originalcode = replace(@originalcode , '{','^^')
set @originalcode = replace(@originalcode , '}','^')
select @originalcode

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22663711
Ultimately, what is your goal?  Is { some soft of a delimiter?
0
 

Author Comment

by:quigleyryan
ID: 22663928
its a parsing routine - i have a lot of "junk" and print specific characters/instructions in my data - i need to replace the print specific stuff with html code...
so initially i had a function (which works with everything but { brackets) that goes and does the replaces based on a set of compares
so if the data had as @originalcode = '{\this way now }' my code would make it <sup>This way now </sup>
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22664016
I think the problem exists in that function then.  With only seeing a small section of the function, it's hard to say.  All I know is a search and replace on { and } are valid.
0
 

Author Comment

by:quigleyryan
ID: 22664133
Here's the whole thing...

well this is what i would rather do --
i have this scalar-valued function that is called by a stored procedure with
this command: SET @OriginalCode = dbo.ReplaceTokenPair(@OriginalCode, '{b',
'}', '<b>', '</b>')

If i have @originalcode = 'testing {b this is a test }' which should give me
testing <b> this is a test </b> but instead it goes into never never land


ALTER FUNCTION [dbo].[ReplaceTokenPair]
(
      @OriginalCode nvarchar(4000),
      @StartToken nvarchar(100),
      @EndToken nvarchar(100),
      @StartHTML nvarchar(100),
      @EndHTML nvarchar(100)
)
RETURNS nvarchar(4000)
AS
BEGIN
      DECLARE @Before nvarchar(4000), @Between nvarchar(4000), @After
nvarchar(4000)

      DECLARE @x int, @y int, @z int
      SET @x = 1
      SET @y = 1
      SET @z = 1

      WHILE @y > 0 AND @z > 0
      BEGIN
            SET @y = CHARINDEX(@StartToken, @OriginalCode, @x)
            SET @z = CHARINDEX(@EndToken, @OriginalCode, @y + LEN(@StartToken) + 1)

            IF @y > 0
            BEGIN
                  SET @Before = SUBSTRING(@OriginalCode, 1, @y - 1)
                  SET @Between = SUBSTRING(@OriginalCode, @y + LEN(@StartToken), @z - @y -
LEN(@EndToken) - 1)
                  SET @After = SUBSTRING(@OriginalCode, @z + LEN(@EndToken),
LEN(@OriginalCode))
                  SET @OriginalCode = @Before + @StartHTML + @Between + @EndHTML + @After
            END
            SET @x = LEN(@OriginalCode) - (LEN(@Before) + LEN(@StartHTML) +
LEN(@Between) + LEN(@EndHTML))
            SET @x = 0
      END
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22664386
Try this:



alter FUNCTION [dbo].[ReplaceTokenPair]
(
      @OriginalCode nvarchar(max),
      @StartToken nvarchar(100),
      @EndToken nvarchar(100),
      @StartHTML nvarchar(100),
      @EndHTML nvarchar(100)
)
RETURNS nvarchar(max)
AS
BEGIN
declare @Return nvarchar(max)
select @Return = isnull(@Return,'')+  @StartHTML + substring(theValue, 1,charindex(@EndToken,theValue)-1) + @EndHTML
+ substring(theValue,charindex(@endToken,theValue)+1,(datalength(theValue)/2)-charindex(@endToken,theValue)+1)
from [dbo].[fn_DelimitedToTable](@OriginalCode, @StartToken)
where datalength(theValue)/2>0

return @Return
end

go
select dbo.ReplaceTokenPair('{bhello}', '{b','}', '<b>', '</b>')



It depends upon this function:
if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion+ character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22664425
Actually... that one only works for one } right now.... give me a few.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22665204
I have a potential problem to propose to you.  If you use the same terminator for multiple tags, there is no reliable way to parse it.

So if you have:
{b{ihello}my name is} jon

There is nothing to say that the first } goes with {b or {i.  My process that I'm working on now will find all terminators '}' and not just the first one.  So it would also not work well with

{bhello}my name is} jon

you would get
<b>hello</b>my name is</b> jon
0
 

Author Comment

by:quigleyryan
ID: 22668369
Brandon
thank you - iright now the data has print specific characters in it - and luckily as we go through and parse the strings we don't have a scenario of {b{ihello}my name is}
but i guess i could...
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question