Solved

Strip out only leading character string - all others remain.

Posted on 2013-11-21
13
195 Views
Last Modified: 2013-12-08
I have data that looks like this:

My old man is<br />a dustman
The quick brown fox jumped over<br />the 
<br />The owl and the pussy cat went to sea<br />in a beautiful
Twinkle, twinkle little star
<br />Humpty dumpty
...

Open in new window


I need to write a function to accept a string and stripp out the leading <br /> tags without effecting any others in the string.

Oracle SQL gets 'Trim(Leading' functionality T-SQL does not :(

Any suggestion would be welcome.

Regards
0
Comment
Question by:splanton
  • 4
  • 4
  • 2
  • +3
13 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39665683
Looks like you need to replace '<br />' with a single space
Declare @str varchar(50) = 'My old man is<br />a dustman'

SELECT REPLACE(@str, '<br />', ' ')

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39665693
Use charindex and stuff

declare @x nvarchar(1000)

select @x = 'My old man is<br />a dustman
The quick brown fox jumped over<br />the
<br />The owl and the pussy cat went to sea<br />in a beautiful
Twinkle, twinkle little star
<br />Humpty dumpty'


select  @x, stuff(@x, charindex('<br />', @x), len('<br />'), '')
0
 
LVL 2

Author Comment

by:splanton
ID: 39665709
There are  5 Individual string records.

Some start with '< br/>', others contain '<br />'.

I cannot replace all occurrences of '<br />' with ''

I need to strip all LEADING occurrences of '<br />'
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39665740
If by 'leading' you are referring to only the beginning of the string, then the below works, although I'm guessing there's a more elegant way to pull this off...

Declare @str varchar(50) = '<br />The owl and the pussy cat went to sea<br />in a beautiful'
Declare @str_replace varchar(50) = '<br />'

SELECT CASE
   WHEN LEFT(@str, LEN(@str_replace)) = @str_replace
      THEN RIGHT(@str, LEN(@str) - LEN(@str_replace))
   ELSE @str END 

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39665907
Given your example data, what would you expect to see as a result?
0
 
LVL 2

Author Comment

by:splanton
ID: 39666377
My old man is<br />a dustman
The quick brown fox jumped over<br />the 
The owl and the pussy cat went to sea<br />in a beautiful
Twinkle, twinkle little star
Humpty dumpty
...

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39666402
Are these separate lines in a database or one block of text?
0
 
LVL 2

Author Comment

by:splanton
ID: 39666494
Separate rows/records :)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39666506
select stuff(yourstring, 1, case when left(yourstring,7)='<BR / >' then 7 else 0 end,'') as yourstring
       .....
 
ie if it starts with <br /> the delete 7 characters otherwise leave the string alone...
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39666509
>I need to write a function to accept a string and stripp out the leading <br /> tags without effecting any others in the string.
Does this mean that you wish to SELECT the data without the leading tags (i.e. a select), or actually change the data to strip out the tags (i.e. an update)?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39666571
SELECT SUBSTRING(string, CASE WHEN string LIKE '<br%' THEN CHARINDEX('>', string) + 1 ELSE 1 END, LEN(string))


For example:

SELECT
    string,
    SUBSTRING(string, CASE WHEN string LIKE '<br%' THEN CHARINDEX('>', string) + 1 ELSE 1 END, LEN(string))
FROM (
    SELECT 'My old man is<br />a dustman' AS string UNION ALL
    SELECT 'The quick brown fox jumped over<br />the ' UNION ALL
    SELECT '<br />The owl and the pussy cat went to sea<br />in a beautiful ' UNION ALL
    SELECT 'Twinkle, twinkle little star ' UNION ALL
    SELECT '<br />Humpty dumpty ' UNION ALL
    SELECT '<br/>  Testing1' UNION ALL
    SELECT '<br>..Testing2'
) AS test_data
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39669961
splanton - So ... how's it going??
0
 
LVL 2

Author Closing Comment

by:splanton
ID: 39704400
All good, Fell off the planet for a while. Sorry for delay allocating points :)
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

820 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