• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Strip out only leading character string - all others remain.

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
splanton
Asked:
splanton
  • 4
  • 4
  • 2
  • +3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Lee SavidgeCommented:
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
 
splantonAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
awking00Commented:
Given your example data, what would you expect to see as a result?
0
 
splantonAuthor Commented:
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
 
Lee SavidgeCommented:
Are these separate lines in a database or one block of text?
0
 
splantonAuthor Commented:
Separate rows/records :)
0
 
LowfatspreadCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Scott PletcherSenior DBACommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
splanton - So ... how's it going??
0
 
splantonAuthor Commented:
All good, Fell off the planet for a while. Sorry for delay allocating points :)
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.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now