May 1, 2015 at 10:49 am
I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.
ALTER PROCEDURE
@product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@product = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @product)
GO
May 1, 2015 at 12:15 pm
diogo_1984 (5/1/2015)
I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.ALTER PROCEDURE
@product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@product = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @product)
GO
Not certain what you mean but this might help
😎
DECLARE @Product VARCHAR(100) = '''ORANGE LEMON'' ''BANANA APPLE'' ''PEACH PEAR''';
SELECT REPLACE(@Product,CHAR(32),' OR ');
Output
'ORANGE OR LEMON' OR 'BANANA OR APPLE' OR 'PEACH OR PEAR'
May 1, 2015 at 12:15 pm
diogo_1984 (5/1/2015)
I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.ALTER PROCEDURE
@product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@product = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @product)
GO
It is not very clear what you are trying to do here. Are you trying to replace all spaces with the literal OR but only when those spaces are not inside a set of single quotes? If that is the case how do you proposed dealing with internal strings that also contain a single quote?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2015 at 12:47 pm
Hello HALL OF FAME,
I understand your explanation. But when put OR between orange and lemon contains the will search among orange or lemon and I want to search for all words containing orange and lemon like it was just one word.
For that reason it is that I put the words that are between '' is to be considered only a word .
May 2, 2015 at 12:51 pm
Hello Sean,
I'm just trying to replace the spaces that are outside the ''
May 2, 2015 at 1:01 pm
Quick question, are you using Full Text Search?
😎
May 2, 2015 at 1:10 pm
Yes i m using. 😉
What I meant was that even using the full-text functionality. I'm using CONTAINS . But before the CONTAISN was :
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS ( Description, PRODUCT )
Since PRODUCT and was researched by words such as ORANGE LEMON OR OR OR BANANA APPLE OR PEAR OR PEACH .
What is wanted now the words that come from the WEB application within '' stay as if it were the AND operator then was :
Product = '' ORANGE LEMON ' BANANA APPLE ' PEACH PEAR ''
PRODUCT = ' ( ORANGE AND LEMON ) OR BANANA OR APPLE OR ( PEACH AND PEAR) '
May 3, 2015 at 5:42 am
Just so you know, posting in multiple forums leads to very disjointed conversations. You asked the same question over here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 4, 2015 at 5:58 am
I suppose the frontend in question has some minimal checks in place so that it always passes a string with even number of quotes and no repeating blanks.
declare @s varchar(8000) ='MELON '' ORANGE LEMON '' BANANA APPLE '' PEACH PEAR '' ';
with terms as (
select replicate('(',1-ItemNumber%2)
+ replace (rtrim(ltrim(item))
, ' '
, case ItemNumber%2 when 1 then' OR ' else ' AND ' end)
+ replicate(')',1-ItemNumber%2) as elem
from dbo.DelimitedSplit8K(@s,'''')
where len(rtrim(ltrim(item))) > 0
)
select stuff((select ' OR '+ elem
from terms
for xml path('')),1,4,'');
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply