Solved

Strip out only leading character string - all others remain.

Posted on 2013-11-21
13
192 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
 
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
3 Use Cases for Connected Systems

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

 
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:ScottPletcher
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

929 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