Extract part of a string and put in another field.

I have this string in the "FullDescription" field :

<br />Brand : Laura Mercier<br />

I want to extract the name in the string, like "Laura Mercier" in above,
and put it in the SEName2 field.

UPDATE Nop_Product
        SET SEName2 =   ??

LVL 16
Who is Participating?
wdosanjosConnect With a Mentor Commented:
Please try.  It assumes that your Nop_Product table has an id column.
update Nop_Product
   set SEName2 = t2.SEName2
  from Nop_Product t1
  join (select id, substring(SEName2, 1, charindex('<br />', SEName2) - 1) SEName2
          from (select id, 
                       substring(FullDescription, charindex('Brand : ', FullDescription) + 8, len(FullDescription)) SEName2
                  from Nop_Product) t) t2 on t1.id = t2.id

Open in new window

Daniel Van Der WerkenConnect With a Mentor Independent ConsultantCommented:
You'd have to manipulate the string. Assuming each of your Full Description fields follow the exact same format, here is a script that will do this. I've made it longer than it needs to be so you can see every step I used.

DECLARE @FullDescription VARCHAR(50)
SET @FullDescription = '<br />Brand : Laura Mercier<br />'

DECLARE @PositionOfColon INT
SET @PositionOfColon = CHARINDEX(':', @FullDescription )
SET @NewString = SUBSTRING( @FullDescription, @PositionOfColon + 2, LEN( @FullDescription ) - @PositionOfColon )
DECLARE @PositionOfSecondBR INT
SET @PositionOfSecondBR = CHARINDEX( '<', @NewString )
PRINT @PositionOfSecondBR
SET @SEName2 = SUBSTRING( @NewString, 0, @PositionOfSecondBR )

Then, you'd set the value to @SEName2.
Update Nop_Product
   set SEName2 = replace(replace(FullDescription, '<br />', ''), 'Brand : ', '')

Open in new window

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

MikeMCSDAuthor Commented:
thanks guys . . .
I guess I should have mentioned the FullDescription field has more text in it, like :

<strong style='font-size:19px;color:blue;'>Laura Mercier by Laura Mercier</strong>
<p style='font-size:18px;color:black;'>Flawless Skin Repair Day Creme</p>
Gender : WOMEN
<br />
<br />Brand : Laura Mercier<br />

<br />Designer : Laura Mercier
<br /><br />Skin Care :

I'm guessing it's not going to work because of that.
But every field does start with "<strong style='font-size:19px; . . . .".
Anthony PerkinsCommented:
I'm guessing it's not going to work because of that.
That depends.  If you can make it a well-formed Xml document, than it becomes a trivial excersise.
MikeMCSDAuthor Commented:
If I change it to :

   SET @NewString = SUBSTRING( @FullDescription, @PositionOfColon + 14, LEN( @FullDescription ) - @PositionOfColon )

it works, . . but not when there are more than 2 words, like this :

SET @FullDescription = 'Gender : WOMEN<br /><br />Brand : AMOR AMOR SUMMER<br />'

any idea on how I can fix this?
MikeMCSDAuthor Commented:
wdosanjos, that worked, thanks.

Dan7el, you answered my original question but I gave you the wrong information, sorry about that.
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.

All Courses

From novice to tech pro — start learning today.