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,
RobCREATE TABLE myTable99 (Photo Id int, Keyword vatchar(256))
GO|||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.|||Got cut short...

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

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, 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.|||You'll need a cursor...

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

do the loop

then do the insert

See?|||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.

Keeping Domain & SQL Access In Sync

Does anyone know of a sample script I could get access to that would
automatically compare disabled/deleted domain accounts to the SQL Security
Logins and keep them "in sync" without any interaction from the SQL
administrator? We're wanting to eliminate the need of sending manual
notifications to the SQL administrator of terminated employees and running
the sp_denylogin. Instead, we want to just create a script that would go ou
t
to all of our SQL servers and automatically remove accounts no longer active
in the domain itself. Does Active Directory provide any tools to do this?
Any help would be greatly appreciated.
Thanks.
ColetteHi
Why don't you rather use Domain Groups?
Give a domain group the correct access, and add the user to the group. A
user can be in multiple groups and when the NT accounts gets added or
removed, there is no maintenance from the DBA side.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Colette" wrote:

> Does anyone know of a sample script I could get access to that would
> automatically compare disabled/deleted domain accounts to the SQL Security
> Logins and keep them "in sync" without any interaction from the SQL
> administrator? We're wanting to eliminate the need of sending manual
> notifications to the SQL administrator of terminated employees and running
> the sp_denylogin. Instead, we want to just create a script that would go
out
> to all of our SQL servers and automatically remove accounts no longer acti
ve
> in the domain itself. Does Active Directory provide any tools to do this?
> Any help would be greatly appreciated.
> Thanks.
> Colette|||That's what I've suggested but they do not want to add additional groups to
Active Directory. Strange...but true. I have instructed them we need to g
o
this route or they need to manually notify the SQL Admins based off the
"security form" of a termination involving SQL access.
P.S. The servers were already set up this way prior to my hire. I'm trying
to fix it. Just wanted to throw that out there...
Thanks again.
Colette
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Why don't you rather use Domain Groups?
> Give a domain group the correct access, and add the user to the group. A
> user can be in multiple groups and when the NT accounts gets added or
> removed, there is no maintenance from the DBA side.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Colette" wrote:
>|||Hi
Then you will need to write some code for this.
Call sp_validatelogins, this will give you a list of all NT Logins that are
no longer valid.
Then based on that result, call sp_revokelogin to remove the user from the
server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Colette" <Colette@.discussions.microsoft.com> wrote in message
news:4AE2F5A1-DA00-4591-BC54-76DEC19D08CA@.microsoft.com...[vbcol=seagreen]
> That's what I've suggested but they do not want to add additional groups
> to
> Active Directory. Strange...but true. I have instructed them we need to
> go
> this route or they need to manually notify the SQL Admins based off the
> "security form" of a termination involving SQL access.
> P.S. The servers were already set up this way prior to my hire. I'm
> trying
> to fix it. Just wanted to throw that out there...
> Thanks again.
> Colette
> "Mike Epprecht (SQL MVP)" wrote:
>