Solved

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

Posted on 2008-10-07
9
203 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.

757 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

19 Experts available now in Live!

Get 1:1 Help Now