?
Solved

Stripping characters in Select Statement

Posted on 2010-01-08
12
Medium Priority
?
183 Views
Last Modified: 2012-05-08
I have an issue that I'm at my wit's end with.

I have an online Real Estate Application that customers use to show their properties.
The same table that the web text is in is used to create "feed" files to vendors to "show" thier properties.

The ONLY formatting allowed by the vendors are line breaks ( <br> )

ANYTHING else will cause a failed feed.

However...I'm dealing with 30 end users that don't always do the entry correctly.

Is there ANY way to STRIP all HTML tags and replace any </p> tags (and only </p>) with <br><br> ?

In the meantime I'm putting in an hour or more a day making sure the feeds go correctly.
0
Comment
Question by:lrbrister
12 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26210615
one way is to make yourself a table with available html tags.  from there you can see if any of them are in the strings and replace them

set fieldname = replace('<p>', '<b>', fieldname)
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26210650
update tableName
set ColumnName = replace('', '

', ColumnName )
0
 
LVL 11

Expert Comment

by:govindarajan78
ID: 26210810
which programming language you use when users enters the data?
its better to do it in the programming side end than the database end if you want to strip html tags.

if you want to just replace </p> with <br>  use:

update <tablename> set <columnname> = replace(<columnname>,'</p>','<br>')

before doing that updation at the db end. take a backup of the database.

By the way; why are you allowing the users to use <p> and other html tags. you can just put a text area right
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26210867
given function will remove all html tags from input so you can modify it according
remove all except
 and

then use replace to change all
 with



but for better approach i will suggest you to use CLR functions
check this link
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/ccbde8aa-68da-44c0-b9b2-71bd66707eee

also

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx#S1

if you need further help in using CLR function do ask
CREATE FUNCTION dbo.tagstrip ( @in VARCHAR(8000)  )
RETURNS VARCHAR(8000) AS BEGIN
DECLARE @i INT
WHILE 1 = 1 BEGIN
SET @i = LEN( @in )
SET @in = REPLACE( @in, SUBSTRING( @in,
CHARINDEX( '<', @in ),











SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/*Strip HTML tags out of text. Anything enclosed in '<' and '>' will be removed.*/

CREATE Function [dbo].[fnStripTags]
    (@Dirty varchar(4000))
    Returns varchar(4000)
As

Begin
    Declare @Start int,
        @End int,
        @Length int

    While CharIndex('<', @Dirty) > 0 And CharIndex('>', @Dirty, CharIndex('<', @Dirty)) > 0
        Begin
        Select @Start = CharIndex('<', @Dirty), 
          @End = CharIndex('>', @Dirty, CharIndex('<', @Dirty))
        Select @Length = (@End - @Start) + 1
        If @Length > 0
            Begin
            Select @Dirty = Stuff(@Dirty, @Start, @Length, '')
            End
        End

    return @Dirty
End


CHARINDEX( '>', @in ) -
CHARINDEX( '<', @in ) + 1 ), SPACE( 0 ) )
IF @i = LEN( @in ) BREAK END
RETURN (  @in )
END

Open in new window

0
 
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26210881
i messed up to function above
 1 is
CREATE FUNCTION dbo.tagstrip ( @in VARCHAR(8000)  )
RETURNS VARCHAR(8000) AS BEGIN
DECLARE @i INT
WHILE 1 = 1 BEGIN
SET @i = LEN( @in )
SET @in = REPLACE( @in, SUBSTRING( @in,
CHARINDEX( '<', @in ),

CHARINDEX( '>', @in ) -
CHARINDEX( '<', @in ) + 1 ), SPACE( 0 ) )
IF @i = LEN( @in ) BREAK END
RETURN (  @in )
END

Open in new window

0
 
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26210887
2nd
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

/*Strip HTML tags out of text. Anything enclosed in '<' and '>' will be removed.*/

CREATE Function [dbo].[fnStripTags]
    (@Dirty varchar(4000))
    Returns varchar(4000)
As

Begin
    Declare @Start int,
        @End int,
        @Length int

    While CharIndex('<', @Dirty) > 0 And CharIndex('>', @Dirty, CharIndex('<', @Dirty)) > 0
        Begin
        Select @Start = CharIndex('<', @Dirty), 
          @End = CharIndex('>', @Dirty, CharIndex('<', @Dirty))
        Select @Length = (@End - @Start) + 1
        If @Length > 0
            Begin
            Select @Dirty = Stuff(@Dirty, @Start, @Length, '')
            End
        End

    return @Dirty
End

Open in new window

0
 

Author Comment

by:lrbrister
ID: 26210918
Yeah guys,
  But this is a Telerik RadEditor that's being used to in[ut the text.

It allows copy and paste so they can get anything from web formatting to MS Word formatting.

Now I have the editor set so that most of that is stripped away in the paste..but THEM they do formatting inside the editor.

So you think a table with all primary tags?

How do I do it so that is picks up the <P (first character and strips everything from the < to the closing > ?
0
 

Author Comment

by:lrbrister
ID: 26210970
rakeshjaimini:

I like the last one but how can I replace </p> with </br> AND leave <br> or </br> alone?
0
 
LVL 10

Accepted Solution

by:
Rakesh Jaimini earned 2000 total points
ID: 26211710
i think u can get plain text from RADEditor unless user have explicitly typed some html tag
do check its properties
0
 

Author Comment

by:lrbrister
ID: 26212026
rakeshjaimini:
yes...rad does that.  I teach the "regular" assistants to right click and "Paste as Plain Text" which removes everything but the <br> tags...which is ok.

But AFTERWARDS...others may go in (sales folks) and add the formatting...which I can't control.

Maybe the only thing left is to start charging the hooey out of them for this, and show in the invoice comments the "last modified by" person.  I hate to do that to customers in these economic times though.
0
 
LVL 10

Expert Comment

by:Rakesh Jaimini
ID: 26212666
i'm not talking about  
right click and "Paste as Plain Text"
in code behind when you get editors content i guess there is 1 property which gives you plain text
i'm not in office else i could have checked it and told you exactly
also why don't you remove tags in C# only why u want it at db level
0
 

Author Comment

by:lrbrister
ID: 26213407
I'll take a look thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

864 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