Delete non-existing values from string

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

SQL Server 2005 Forums

Transact-SQL (2005)

Delete non-existing values from string

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2011-09-15 : 10:52:43

Hi all,

I have a multivalue string. Elements are seperated by a semi-colon, e.g.

Apple;HP;Samsung

Now I have also a table with several rows. The key column is 'Company'. It contains rows like:

Apple
Samsung
Dell

Now I need to remove all non-existing values from the above given string. So for the current example the given string 'Apple;HP;Samsung' should then return 'Apple;Samsung' because 'HP' is not listed in the datatable. Do I have to split the string, then compare, and then recreate the string with the values?
Thanks in advance!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-15 : 18:32:43

quote:
Originally posted by Heinz23

... Do I have to split the string, then compare, and then recreate the string with the values?
Thanks in advance!

Hello,

That is one method. Here is an example using a 'tally' table technique. @val contains your original string and @newVal is the new scrubbed value.

HTH.


DECLARE @newVal VARCHAR(100); SET @newVal = ''

SELECT @newVal = @newVal + v.value + ';'
FROM
(
	--tally to parse @val
	SELECT 
		LTRIM(RTRIM(NULLIF(SUBSTRING(';' + @val + ';', Number, CHARINDEX(';', ';' + @val + ';', Number) - Number), ''))) AS value
	FROM 
		master..spt_values 
	WHERE 
		Type = 'P' 
			AND Number <= LEN(';' + @val + ';')
			AND SUBSTRING(';' + @val + ';', Number - 1, 1) = ';'
			AND CHARINDEX(';', ';' + @val + ';', Number) - Number > 0
) v
JOIN <yourTable> t ON t.company = v.value

SELECT LEFT(@newVal,LEN(@newVal)-1) AS newVal

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2011-09-16 : 05:45:23

Hi ehorn,

many thanks, this works great! Only problem: If the supplied string contains only a single value which is not valid (in the example above: 'HP') then I get 'Invalid length parameter passed to the SUBSTRING function.' I've already tried some things on my own but as I currently don't understand your script

I wonder whether you have a simple solution for that?

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2011-09-16 : 07:18:13

Ah, found it. I thought the error was in the large SELECT but in fact it appeared in

SELECT LEFT(@newVal,LEN(@newVal)-1) AS newVal

Placing this in a

IF LEN(@newVal) > 0

fixed the problem.

Many thanks!!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-16 : 09:02:56

yvw,

Tally tables are a very powerful technique for these types of operations. There are great articles on this site which cover the topic.

Have a nice day.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources