Results 1 to 7 of 7

Thread: Keyword Query

  1. #1
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34

    Unanswered: Keyword Query

    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.

    Can this be done? What would the query look like?

    I am looking for a list like:

    bull
    barrel
    rodeo
    western
    cowboy

    Any suggestions?

    Thanks,
    Rob

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    CREATE TABLE myTable99 (Photo Id int, Keyword vatchar(256))
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    454
    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

    SELECT DISTINCT Keyword FROM temporary TABLE

    to get the distinct keyword.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Got cut short...

    far as I know you need some code....this example would be 1 row from a cursor for example...

    Code:
    USE Northwind
    GO
    
    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
    	BEGIN
    		INSERT INTO @tbl (col1) SELECT SUBSTRING(@x, @y, @z-@y+1)
    		SELECT @y = @z + 2
    		SELECT @z = CHARINDEX('|',@x,@y)-1
    	END
    
    
    INSERT INTO @tbl (col1) SELECT SUBSTRING(@x, @y, LEN(@x)-@y+2)
    
    SELECT LEN(col1), col1  FROM @tbl
    GO
    
    SET NOCOUNT OFF
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    Brett, I see what you are doing and I understand what is going on but I don't know how to get my data into where you have 'Brett|No|Rhyme|to|Well'.

    My field name is keyword and the table name is TblPhotos and the Database name is CTM_samples. How would I select the keyword values and insert them into the temp
    table?

    Thanks alot for your explination.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You'll need a cursor...

    do a fetch and assign the columns to variables...

    do the loop

    then do the insert



    See?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    The following query:

    SELECT photo,
    NullIf(
    SubString(',' + keyword + ',' , counter, CharIndex(',' , ',' + keyword + ',' , counter) - counter) , '') AS keywords
    FROM photos, stringlen
    WHERE counter <= Len(',' + keyword + ',') AND SubString(',' + keyword + ',' , counter - 1, 1) = ','
    AND CharIndex(',' , ',' + keyword + ',' , counter) - counter > 0


    will return:

    photo1 bull
    photo1 barrel
    photo1 rodeo
    photo1 western
    photo2 eiffel
    photo2 tower
    photo2 paris

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •