Solved

SQL TRIM duplicated Training characters

Posted on 2010-08-20
6
380 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:darren-w-
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33484237
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33484251
case when right(strCol,6)='<br />' then left(strCol,len(strCol)-6) else strCol end
0
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33484266
If you are using sql server then,

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

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 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33484311

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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33484370
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
 
LVL 13

Author Closing Comment

by:darren-w-
ID: 33484576
I found it  easy to understand and implement, may have been difficult to understand from a beginners perspective though.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 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

14 Experts available now in Live!

Get 1:1 Help Now