Solved

Extract part of a string and put in another field.

Posted on 2012-04-09
7
286 Views
Last Modified: 2012-04-10
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 =   ??

thanks
0
Comment
Question by:MikeMCSD
[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
7 Comments
 
LVL 20

Assisted Solution

by:Daniel Van Der Werken
Daniel Van Der Werken earned 250 total points
ID: 37824668
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 @SEName2 VARCHAR(50)
DECLARE @PositionOfColon INT
SET @PositionOfColon = CHARINDEX(':', @FullDescription )
DECLARE @NewString VARCHAR(50)
SET @NewString = SUBSTRING( @FullDescription, @PositionOfColon + 2, LEN( @FullDescription ) - @PositionOfColon )
DECLARE @PositionOfSecondBR INT
SET @PositionOfSecondBR = CHARINDEX( '<', @NewString )
PRINT @PositionOfSecondBR
SET @SEName2 = SUBSTRING( @NewString, 0, @PositionOfSecondBR )
PRINT @SEName2

Then, you'd set the value to @SEName2.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 37825182
Perhaps:
Update Nop_Product
   set SEName2 = replace(replace(FullDescription, '<br />', ''), 'Brand : ', '')

Open in new window

0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 37825738
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; . . . .".
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 75

Expert Comment

by:Anthony Perkins
ID: 37825957
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.
0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 37827353
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?
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 250 total points
ID: 37827800
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

0
 
LVL 16

Author Comment

by:MikeMCSD
ID: 37828107
wdosanjos, that worked, thanks.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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