delete from field - SQL Server Forums

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

delete from field

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-15 : 14:08:06

I'd like to find common values on my field and delete the rest of the filed which is not common. I mean if I have these values:

Dymatize Nutrition Alpha 100 caps
Dymatize Nutrition Alpha Acid 30.00 ea
Lipoderm Dymatize Nutrition Alpha Lipoic Acid/Green Tea
Vermont Alpha Dymatize Nutrition Alpha
Now Royal Jelly 1000 mg
Royal Jelly 1500 mg freeze dried
Bee 500 mg Royal Jelly
Now Propolis 500 mg
Alvita Teas Now Leaf
...

I want these results from it:

Dymatize Nutrition Alpha
Dymatize Nutrition Alpha
Dymatize Nutrition Alpha
Royal Jelly
Royal Jelly
Royal Jelly
Now
Now

plz help its really important.
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 14:12:39

do you have these standard values which you want to retrieve stored somewhere?

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-15 : 14:15:29

No.I want to grab that values from the field & I want to create the standard values from it. Thats why I’m trying to find common values.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 14:17:51

The title should read "extract from a column", not "delete from a field".

Please describe for us the business rules for extracting the correct data.

E 12°55'05.63"
N 56°04'39.26"

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-15 : 15:05:43

Sorry yr right I had to say extracting from a field.
I need to create categories from a description field. the only way to do that is to find similar keywords on the field

when the values are :
Lipoderm Dymatize Nutrition Alpha Lipoic Acid/Green Tea
Vermont Alpha Dymatize Nutrition Alpha

the common keywords would be:
Dymatize Nutrition Alpha

so I need to find those common words.
they maybe only one word or two or three words.
I think I have to do them separately.
I've created 3 felids:
one for finding only one common word from that field.
second column for values which has 2 common words.
third column that have three common words.but the problem is I don’t know how to grab them and put them on the fields.

thanks for helping

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 15:28:03

This will get you going

DECLARE	@Sample TABLE
	(
		ID INT IDENTITY(1, 1),
		data VARCHAR(200)
	)

INSERT	@Sample
SELECT	'Dymatize Nutrition Alpha 100 caps' UNION ALL
SELECT	'Dymatize Nutrition Alpha Acid 30.00 ea' UNION ALL
SELECT	'Lipoderm Dymatize Nutrition Alpha Lipoic Acid/Green Tea' UNION ALL
SELECT	'Vermont Alpha Dymatize Nutrition Alpha' UNION ALL
SELECT	'Now Royal Jelly 1000 mg' UNION ALL
SELECT	'Royal Jelly 1500 mg freeze dried' UNION ALL
SELECT	'Bee 500 mg Royal Jelly' UNION ALL
SELECT	'Now Propolis 500 mg' UNION ALL
SELECT	'Alvita Teas Now Leaf'

SELECT		LTRIM(RTRIM(MAX(CASE WHEN s2.RowID = 1 THEN s2.Data ELSE '' END)
		+ ' ' + MAX(CASE WHEN s2.RowID = 2 THEN s2.Data ELSE '' END)
		+ ' ' + MAX(CASE WHEN s2.RowID = 3 THEN s2.Data ELSE '' END)
		+ ' ' + MAX(CASE WHEN s2.RowID = 4 THEN s2.Data ELSE '' END)
		+ ' ' + MAX(CASE WHEN s2.RowID = 5 THEN s2.Data ELSE '' END)))
FROM		(
			SELECT		s.ID,
					f.RowID,
					f.Data
			FROM		@Sample AS s
			CROSS APPLY	dbo.fnParseList(' ', s.Data) AS f
		) AS s1
INNER JOIN	(
			SELECT		s.ID,
					f.RowID,
					f.Data
			FROM		@Sample AS s
			CROSS APPLY	dbo.fnParseList(' ', s.Data) AS f
		) AS s2 ON s2.ID > s1.ID
WHERE		s1.Data = s2.Data
GROUP BY	s1.ID,
		s2.ID

E 12°55'05.63"
N 56°04'39.26"

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-15 : 17:50:31

thanks for yr reply.but i didn't get yr solution.
what are the s,s1,s2?
it gave me a syntax error near APPLY.is there any easier way to do it?

thanks in advanced

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 03:14:24

quote:
Originally posted by a.ashabi

thanks for yr reply.but i didn't get yr solution.
what are the s,s1,s2?
it gave me a syntax error near APPLY.is there any easier way to do it?

thanks in advanced

Are you using SQL Server 2005?
The above solutions works if the version>=2005

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 10:46:16

also your compatibility level should be 90 for using APPLY in sql 2005

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-04-16 : 13:18:17

I'm using sql2000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources