Deletion some parts from field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deletion some parts from field

Author

Topic

saieban
Starting Member

2 Posts

Posted - 2009-09-10 : 13:29:46

I have this problem,
I need to delete some parts from fields like below.
my original list has these parts:

create table Adword_keywords1
(product_id int ,keywords varchar (50));
insert Adword_keywords1 (product_id,keywords)
select '3300','cotempo fisrt 1/2oz' union all
select '3301','cotempo fisrt 1/20oz' union all
select '3302','cotempo fisrt 1/32oz' union all
select '3303','cotempo fisrt 1/64oz' union all
select '3304','cotempo fisrt 1/5oz' union all
select '3305','cotempo fisrt 1/10oz' union all
select '3306','cotempo fisrt 1/100oz' union all
select '3307','cotempo fisrt 1/64oz' union all
select '3308','cotempo fisrt 1/90oz' union all
select '3309','cotempo fisrt 1/35oz' union all
select '3310','contempo fisrt 20oz' union all
select '3311','contempo fisrt 40oz' union all
select '3312','contempo fisrt 60oz' union all
select '3313','contempo fisrt 30oz' union all
select '3314','contempo fisrt 30oz' union all
select '3315','contempo fisrt 145oz' union all
select '3316','contempo fisrt green' union all
select '3317','contempo fisrt blue' union all
select '3318','contempo fisrt yellow' union all
select '3319','contempo fisrt red'

...

and I want the result be like this:

Contempo Fisrt
Contempo Fisrt
Contempo First

...

(as you can see in all similar fields, all dissimilar parts at the end need to be deleted)
Can anyone help me with this? I really appreciate that.

Ahmad Amin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-10 : 13:51:22

There is no easy and sure way to "generalize" data like that. If your actual data will only have a manageable number distinct values then first creating a table of those generalized, distinct values will make the task easier.

Be One with the Optimizer
TG

saieban
Starting Member

2 Posts

Posted - 2009-09-10 : 14:16:32

I'm sorry but since I am a new babe in SQL I have no idea how to solve this one.
can anyone explain more on the solution?

Ahmad Amin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-10 : 16:26:46

I see you removed several of your examples from your original post. We can try to come up with something automated but it would be best if you could provide as much sample data as possible because the more representitive of your actual data the better chance to succeed. So post some sample data in executable form like this:


create table #t (col varchar(200))
insert #t (col)
select 'Contempo first 1/2oz' union all
select 'Contempo first 1/4oz' union all
select 'Contempo first Light'

Better yet, script out the design of your actual table including the Primary Key. Then make sure your Insert statements actually work. The more different examples of your data the better.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-10 : 18:07:09

Stop just editing your original post because other people can't follow the thread. I thought your original post was a tough problem that would be hard - if not impossible - to solve perfectly. But now that you've simplified your sample data down to just one distinct product it's an easy solution.

select 'cotempo fisrt' from Adword_keywords1

If you want serious help then please post enough variation in your sample data to illustrate the real problem.

If I recall you had products with similarities at the beginning of the string, at the end of the string, as well as at the beginning and end with variation in the middle of the string. You said you needed to just use the existing values to identify a "generalized" product name based on the commonality between the values.

If that is still true please post that type of sample data.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources