Solved

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

Posted on 2008-10-07
9
211 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CDC and AOG on MS SQL 2012 13 25
SQL query and VBA 5 46
Select single row of data for each ID in Select Statement 7 27
In sql, how to roll up multiple rows to only one row. 4 38
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

831 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