?
Solved

Strip out only leading character string - all others remain.

Posted on 2013-11-21
13
Medium Priority
?
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +3
13 Comments
 
LVL 66

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

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 66

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

762 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