I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.
Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.
I wanted to select distinct all of the individual words from each keyword field in all of the records.
Yes, you can get the distinct keywords from your table. You need to use LOOP to fetch each value of the colomn and assign it to the variable. And then you need to split string based on ",". Insert the seperated keyword into the temporary table. At last, you just do
far as I know you need some code....this example would be 1 row from a cursor for example...
SET NOCOUNT ON
DECLARE @x varchar(8000), @y int, @z int
DECLARE @tbl table (col1 varchar(8000))
SELECT @x = 'Brett|No|Rhyme|to|Well', @y = 1, @z = CHARINDEX('|',@x,1)-1
WHILE @z <> -1
INSERT INTO @tbl (col1) SELECT SUBSTRING(@x, @y, @z-@y+1)
SELECT @y = @z + 2
SELECT @z = CHARINDEX('|',@x,@y)-1
INSERT INTO @tbl (col1) SELECT SUBSTRING(@x, @y, LEN(@x)-@y+2)
SELECT LEN(col1), col1 FROM @tbl
SET NOCOUNT OFF
The key here it create a 'stringlen' table with an counter field with incrementing numbers. So, if your longest keyword column contains 200 characters, then you would have values 1-200 in your counter field to cover the substring manipulation.