Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Strip out only leading character string - all others remain.

Posted on 2013-11-21
13
Medium Priority
?
211 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 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 70

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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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