A QuotedStr function

Hi;
is there a function to add quotes to a string sentence?; like this
exec('Select * from Customers where Name='+@.NAMEC+' and AGECUST > 25')
if @.NAME is only William, i need to add quotes to it to get a sentence like:
Select * from Customers where Name='William' and AGECUST > 25
is there a function to do that?There is no special function for that. you can do in 2 ways.
1. use double quotes and set quote identifier off so that you can use both
double and single quotes.
2. for using single quote in your string you need to put one more single
quote.
ie taking your e.g
exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST > 25')
please note it looks like double quote it is not, it is 2 single quotes.
Try this.
Amarnath, MCTS
"Willo" wrote:
> Hi;
> is there a function to add quotes to a string sentence?; like this
> exec('Select * from Customers where Name='+@.NAMEC+' and AGECUST > 25')
> if @.NAME is only William, i need to add quotes to it to get a sentence like:
> Select * from Customers where Name='William' and AGECUST > 25
> is there a function to do that?
>
>
>|||"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:6BD0612E-D7AD-4B08-AD64-15A68EE2FD41@.microsoft.com...
> There is no special function for that. you can do in 2 ways.
> 1. use double quotes and set quote identifier off so that you can use both
> double and single quotes.
where can i set that?
> 2. for using single quote in your string you need to put one more single
> quote.
> ie taking your e.g
> exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST >
> 25')
> please note it looks like double quote it is not, it is 2 single quotes.
>
i got a syntax error here|||You need to set in the data tab itself. e.g
SET QUOTED_IDENTIFIER OFF
exec
("Select * from Customers where Name= " + " ' " +@.NAMEC+ " ' " + " and
AGECUST > 25")
Just paste this in your data tab it will work. ps: To make it clear I have
left space in between the double quotes. once you get the idea you can remove
the space. Just to check whether the sql query is correct just replace "exec"
with "select"
you will get the full query itself for you to check.
Amarnath, MCTS
"Willo" wrote:
> "Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
> news:6BD0612E-D7AD-4B08-AD64-15A68EE2FD41@.microsoft.com...
> > There is no special function for that. you can do in 2 ways.
> > 1. use double quotes and set quote identifier off so that you can use both
> > double and single quotes.
> where can i set that?
> > 2. for using single quote in your string you need to put one more single
> > quote.
> > ie taking your e.g
> >
> > exec('Select * from Customers where Name= '' '+@.NAMEC+ ' '' and AGECUST >
> > 25')
> > please note it looks like double quote it is not, it is 2 single quotes.
> >
> i got a syntax error here
>
>
>|||Thank! Amarnath, works great.
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:41C16D7C-954B-43D6-A165-1AC2D887AD3F@.microsoft.com...
> You need to set in the data tab itself. e.g
> SET QUOTED_IDENTIFIER OFF
> exec
> ("Select * from Customers where Name= " + " ' " +@.NAMEC+ " ' " + " and
> AGECUST > 25")
> Just paste this in your data tab it will work. ps: To make it clear I have
> left space in between the double quotes. once you get the idea you can
> remove
> the space. Just to check whether the sql query is correct just replace
> "exec"
> with "select"
> you will get the full query itself for you to check.
> Amarnath, MCTS

A long query problem

Hi all,
I currently build a text processing database for my application.
The database is to store all the plain text files submitted by customers. The plain text files will be processed before it store in the sql server. Thus I have 3 major tables Document, Term and TermDocument.
I encounter a problem when I did a query. For example when I performed the following query

SELECT T.TermName, S.* FROM Weight AS S INNER JOIN Term AS T ON S.TermID = T.TermID WHERE T.TermName IN ('electronic','commerce','consists', ....)

The system told me that "No enough storage is available to complete the operation". The terms in "IN(....)" could have up 3k entries. I know this is huge, but I have no choice but to retrieve all of them in a single query.

Is there anyway to solve the problem or perform similar operation in alternative way.

Maybe it would be easier to use reverse logic?

If possible you could reverse the IN to a NOT IN any maybe the list is shorter then (assumption).

You could also insert the 3000 terms in a temporary table and join with that table instead of using the IN clause.

Anyway, I'm not too fond of queries with 3000 entries in the WHERE clause to be honest.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

I agree that its best to go down the table route, either with a temp table or perhaps a table valued UDF.

There are some good examples of the split function referenced in this thread which may suit your needs as it will enable you to convert a string of terms in to a table.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1847071&SiteID=1

Hope that makes sense.