Solved

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

Posted on 2008-10-07
9
206 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
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now