SQL TRIM duplicated Training characters

I have text strings that can look like any of these lines

String<br />String<br />String<br />String<br />
String<br /><br /><br /><br />
String<br />String<br /><br /><br />
String<br />String<br />String<br /><br />

What command would I uses to remove the trailing <br />'s

Thanks
LVL 13
darren-w-Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:

create function dbo.stripTrailing(@str varchar(8000),@trailing varchar(20))
returns varchar(8000)
as
begin
while @str like '%' + @trailing
	set @str = left(@str,len(@str)-len(@trailing))
return @str
end
GO

select dbo.stripTrailing(strCol, '<br />') from tbl

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
presuming that the HTML code does not contain any € symbol on it's own (it should be &euro; ...), this could do:
select replace(replace(rtrim( replace(replace(yourfield, ' ' , '€'), '<br />', ' '), ' ', '<br />'), '€', ' ')

Open in new window

0
 
cyberkiwiCommented:
case when right(strCol,6)='<br />' then left(strCol,len(strCol)-6) else strCol end
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Om PrakashCommented:
If you are using sql server then,

Update table_name
set
field_name = left(field_name,len(field_name) - 6)

0
 
cyberkiwiCommented:
My first solution takes the first trailing br away, 2nd takes all trailing br's away.
To fix angelIII's multi-replace method:

select
replace(
      replace(
            replace(
                  rtrim(
                        replace(
                              replace(
                                    replace(
                                          strcol, '<br />', CHAR(255))
                                    , ' ' , CHAR(254))
                              , Char(255), ' ')
                        )
                  ,' ', char(255))
            ,char(254), ' ')
      ,char(255), '<br />')
from tbl
0
 
darren-w-Author Commented:
I found it  easy to understand and implement, may have been difficult to understand from a beginners perspective though.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.