Solved

Extract part of a string and put in another field.

Posted on 2012-04-09
7
285 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

732 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