Right single quotation mark errors

Hi,
I've created a table in SQL Server 2000 and I'm now trying to search
through the data and return specific rows. I'm using this command:

select * from Export where libelle_court='Recherche d'investisseurs'

The problem is this: The search fails whenever there is a curly
single quotation mark within the table field ( ' as opposed to ' ).

For example, if the field entry in my table is this:
Recherche d'investisseurs

then both of the following commands retun no fields:
select * from Export where libelle_court='Recherche d'investisseurs'
select * from Export where libelle_court='Recherche d''investisseurs'

However, if the field entry in my table is this:
Recherche d'investisseurs

then both of the commands quoted above succeed.

How can I get SQL Server to treat the curly quotation mark correctly
and return the right results? I've tried changing the collation but
with no success.

Thanks,

RobHi

In general if there is a quotation mark in the field it can be escaped with
another quotation mark, therefore I am not sure why the second example does
not work.

> select * from Export where libelle_court='Recherche d''investisseurs'

What does

select * from Export where libelle_court like 'Recherche d%'

return?

Also check out:

http://msdn.microsoft.com/library/d...con_03_7mch.asp

http://msdn.microsoft.com/library/d...earchvalues.asp

Collation will not effect this.

John

"Robert Garrett" <rgagarrett@.hotmail.com> wrote in message
news:b9c50dd6.0311030229.1ea4c288@.posting.google.c om...
> Hi,
> I've created a table in SQL Server 2000 and I'm now trying to search
> through the data and return specific rows. I'm using this command:
> select * from Export where libelle_court='Recherche d'investisseurs'
> The problem is this: The search fails whenever there is a curly
> single quotation mark within the table field ( ' as opposed to ' ).
> For example, if the field entry in my table is this:
> Recherche d'investisseurs
> then both of the following commands retun no fields:
> select * from Export where libelle_court='Recherche d'investisseurs'
> select * from Export where libelle_court='Recherche d''investisseurs'
> However, if the field entry in my table is this:
> Recherche d'investisseurs
> then both of the commands quoted above succeed.
> How can I get SQL Server to treat the curly quotation mark correctly
> and return the right results? I've tried changing the collation but
> with no success.
> Thanks,
> Rob|||Just another thought, depending on what the datatypes and options are, check
out trailing spaces. (Also see ANSI_PADDING in BOL)

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:bo5fp2$6q8$1@.sparta.btinternet.com...
> Hi
> In general if there is a quotation mark in the field it can be escaped
with
> another quotation mark, therefore I am not sure why the second example
does
> not work.
> > select * from Export where libelle_court='Recherche d''investisseurs'
> What does
> select * from Export where libelle_court like 'Recherche d%'
> return?
> Also check out:
>
http://msdn.microsoft.com/library/d...con_03_7mch.asp
>
http://msdn.microsoft.com/library/d...earchvalues.asp
> Collation will not effect this.
> John
> "Robert Garrett" <rgagarrett@.hotmail.com> wrote in message
> news:b9c50dd6.0311030229.1ea4c288@.posting.google.c om...
> > Hi,
> > I've created a table in SQL Server 2000 and I'm now trying to search
> > through the data and return specific rows. I'm using this command:
> > select * from Export where libelle_court='Recherche d'investisseurs'
> > The problem is this: The search fails whenever there is a curly
> > single quotation mark within the table field ( ' as opposed to ' ).
> > For example, if the field entry in my table is this:
> > Recherche d'investisseurs
> > then both of the following commands retun no fields:
> > select * from Export where libelle_court='Recherche d'investisseurs'
> > select * from Export where libelle_court='Recherche d''investisseurs'
> > However, if the field entry in my table is this:
> > Recherche d'investisseurs
> > then both of the commands quoted above succeed.
> > How can I get SQL Server to treat the curly quotation mark correctly
> > and return the right results? I've tried changing the collation but
> > with no success.
> > Thanks,
> > Rob|||Thanks for the help.

I'm not sure exactly what the problem was but it has gone now. I was
working on a number of things so I don't know quite what it was that
fixed the problem. I thought it might be because I was changing the
collation, but I cannot use this to repeat the fault. The web sites
were useful, though, so thanks again for the help.

Robsql

revoke grant access for specific user

i want to ask how to revoke grant access to specific user, so the spesific user can give grant access to other user.

thank in advance

I'm not exactly sure what you're asking for here, but i'll take a guess:

Have a look at the fixed database roles db_securityadmin and db_accessadmin which manage permissions/db access in BOL.

Also check the GRANT and REVOKE statements which may help.

If i've misundertood your intentions, could you please elaborate?

HTH!

|||

If you provided a user permissions to also GRANT permission on the object(s) using the WITH GRANT option, you can remove just the ability to grant permissions to others.

REVOKE GRANT OPTION ON OBJECT::ObjectName FROM User

Revert Question

how do we change the value of specific column?(not updating).

for example the value of ID 01 - 03 is 1 and ID 04 and 05 is 0. how do we revert it?

in one statement only

Table1

ID Value

01 1

02 1

03 1

04 0

05 0

One of many solutions that's possible

Code Snippet

declare @.t table ( cid int, cnum int)
insert @.t select 1, 1
insert @.t select 2, 1
insert @.t select 3, 1
insert @.t select 4, 0
insert @.t select 5, 0

select * from @.t

select cid,
case cnum
when 1 then 0
when 0 then 1
end
from @.t

Reverse Bill Of Materials Query?

Hello!
We have a bill of materials table with a classic parent/child relationships.
What I need to be able to do is to take a specific part number and return
the highest level parent for that part number.
For instance, If part number A is used as a component in part number B, and
part number B is then used in another component called C, then the highest
level parent for part number A is C.
so the result of the query when ran against part number A would be C
How can I set up such a query?
Thanks
JoeBelow is a solution using a user-defined function. If this
is a frequent requirement, you may want to consider alternate
ways of modeling your hierarchy. If you search groups.google.co.uk
or www.google.com for hierarchy+itzik+sqlserver you'll find some nice
ideas.
-- Original thread at http://groups.google.co.uk/groups?q=A9B05D_C5E784
CREATE TABLE Employee (
pk int not null primary key,
parent int
)
go
INSERT INTO Employee VALUES (1,NULL)
INSERT INTO Employee VALUES (2,NULL)
INSERT INTO Employee VALUES (3,1)
INSERT INTO Employee VALUES (4,2)
INSERT INTO Employee VALUES (5,4)
go
CREATE FUNCTION rootPK(
@.pk INT
) RETURNS INT
AS
BEGIN
DECLARE @.parent INT, @.this INT
SET @.this = NULL
SET @.parent = @.pk
WHILE @.parent IS NOT NULL BEGIN
SELECT
@.this
= pk
, @.parent
= parent
FROM
Employee
WHERE
pk
= @.parent
END
RETURN @.this
END
go
ALTER TABLE Employee ADD rootPK as dbo.rootPK(pk)
go
SELECT * FROM Employee
go
-- drop table Employee
-- drop function dbo.rootPK
-- Steve Kass
-- Drew University
Joe Williams wrote:

> Hello!
> We have a bill of materials table with a classic parent/child relationship
s.
> What I need to be able to do is to take a specific part number and return
> the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B, an
d
> part number B is then used in another component called C, then the highest
> level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>|||There is also a good example at:
http://msdn.microsoft.com/library/d...r />
_5yk3.asp
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Joe Williams" <Joe@.anywhere.com> schrieb im Newsbeitrag
news:%234YOY0jWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hello!
> We have a bill of materials table with a classic parent/child
> relationships. What I need to be able to do is to take a specific part
> number and return the highest level parent for that part number.
> For instance, If part number A is used as a component in part number B,
> and part number B is then used in another component called C, then the
> highest level parent for part number A is C.
> so the result of the query when ran against part number A would be C
> How can I set up such a query?
> Thanks
> Joe
>

Returning long string in varchar using Coalesce()

Hi,
I want to return a string of words separated by comma using coalesce() so
that the client application can look for a specific word in this long
string.Following is the script:-
CREATE TABLE WordList
(
WordId int IDENTITY,
WordDesc varchar(100)
)
INSERT INTO WordList VALUES ('Lion')
INSERT INTO WordList VALUES ('Goat')
INSERT INTO WordList VALUES ('Snake')
DECLARE @.WordList varchar(8000)
SELECT @.WordList = COALESCE(@.WordList + ', ', '') + WordDesc
FROM WordList
SELECT @.WordList AS WordList
Above approach works fine.However,as the maximum size for varchar is
8000,any longer string will be truncated.I can not use "text" datatype for
this as it is not allowed.
Any workaround?Any pointers?you cannot declare a variable of text datatype. Of course in 2005 I think yo
u
can do that because its been replaced as varchar(max). Hope this helps.
"Ashish" wrote:

> Hi,
> I want to return a string of words separated by comma using coalesce() so
> that the client application can look for a specific word in this long
> string.Following is the script:-
>
> CREATE TABLE WordList
> (
> WordId int IDENTITY,
> WordDesc varchar(100)
> )
> INSERT INTO WordList VALUES ('Lion')
> INSERT INTO WordList VALUES ('Goat')
> INSERT INTO WordList VALUES ('Snake')
>
> DECLARE @.WordList varchar(8000)
> SELECT @.WordList = COALESCE(@.WordList + ', ', '') + WordDesc
> FROM WordList
> SELECT @.WordList AS WordList
>
> Above approach works fine.However,as the maximum size for varchar is
> 8000,any longer string will be truncated.I can not use "text" datatype for
> this as it is not allowed.
> Any workaround?Any pointers?
>|||Correct.Thank you.I am aware of the same.But i am using Sql 2000 and looking
for a workaround for this.
"Omnibuzz" wrote:
> you cannot declare a variable of text datatype. Of course in 2005 I think
you
> can do that because its been replaced as varchar(max). Hope this helps.
> --
>
>
> "Ashish" wrote:
>|||Perform concatenation at the client / presentation layer?

> Above approach works fine.However,as the maximum size for varchar is
> 8000,any longer string will be truncated.I can not use "text" datatype for
> this as it is not allowed.
> Any workaround?Any pointers?|||I want to avoid returning resultset to the presentatio layer.If i would want
i would make use of the resultset only.No concatenation would be required.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Perform concatenation at the client / presentation layer?
>
>
>
>|||SQL Server is not a text/image editor. If your content can easily be
concatenated, why is it not stored in one piece?
You could create a temporary table, and then use READTEXT/UPDATETEXT to
concatenate the values in SQL. This can hardly perform as well as a client
application.
ML
http://milambda.blogspot.com/