Inserting a word

Question

Post reply

Inserting a word

sudhanva

Ten Centuries

Points: 1343
More actions
September 2, 2009 at 3:25 am

#156163

Hi,
declare @SearchText varchar(255)
set @SearchText = 'east west AND north'
User will enter search text, something like above.
I need a query which will, insert the word 'OR' between two words, eg: between east west : east or west, for above example.
Scenario:
Whereever we find a space between two words in a sentence, we need to insert 'OR', except the space before/after AND/OR operator.
For above example: After the query, the @SearchText should be "east OR west AND north"
My query is using contains[/url], which doesnt accepts a searchword like this: 'east west AND north'
Hope you got? Help me to achieve this?
Thanks in advance.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 1

You can use CASE Statment here. See CASE (T-SQL) in BOL, it may help u..

I hope i could get proper idea regarding ur question as i m in hurry right now... 🙂

"Don't limit your challenges, challenge your limits"

sudhanva Ten Centuries Points: 1343 More actions · Answer 2

kruti (9/2/2009)
You can use CASE Statment here. See CASE (T-SQL) in BOL, it may help u..

case will not help here. Please send a sample query.

kruti (9/2/2009)
I hope i could get proper idea regarding ur question as i m in hurry right now... 🙂

Please understand the requirement before you reply. Which will help others to make effective use of this forum, and it will not have unneccesary posts/replies.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Have a look to BOL (Books Online) for REPLACE and STUFF functions.

sudhanva Ten Centuries Points: 1343 More actions · Answer 4

Hey experts, anybody has solution for this???

How to search for a pattern like mentioned in my question above??

changing : [anyword anyword] to [anyword OR anyword]

or : [anyword AND anyword anyword] to [anyword AND anyword OR anyword]

Ken McKelvey SSCoach Points: 18842 More actions · Answer 5

Just use the REPLACE command as suggested above. Something like:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(@SearchText, ' and ', '_AND_'), ' or ', '_OR_'), ' ', ' OR '), '_', ' ')

sudhanva Ten Centuries Points: 1343 More actions · Answer 6

Hats off to you Ken McKelvey. 😎

One more question, will there be any performance issue using REPLACE function?

Kruti Kansara Hall of Fame Points: 3118 More actions · Answer 7

Please understand the requirement before you reply. Which will help others to make effective use of this forum, and it will not have unneccesary posts/replies.

It also depends on how well one can explain his/her question. It will be much more helpful if you can provide some sample queries for ur problem which help others to get idea what exactly you want and what exactly u did to do so. Hope the below post will help you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Also keep reading/searching BOL regularly, this will help u a lot, especially for 'REPLACE', 'CASE' etc. You will get a very good idea what you can do with those functionality.

"Don't limit your challenges, challenge your limits"

pkan Newbie Points: 8 More actions · Answer 8

Hi sudhanva,

I hope you also must have learnt from Ken McKelvey about how to make it more understandable for other's to reply to your query so as to make "effective use of this forum" and not to have "unnecessary posts/replies".

It really is very easy to point out mistakes in someone, but first you should check whether you are not committing the same..;-)

Regards,

pkan

OTF SSCertifiable Points: 6104 More actions · Answer 9

From a performance perspective, you will probably find that a CLR Procedure/Function might out perform a TSql Solution when it comes to heavy duty String manipulation. You also have the benefit of doing more sophisticated Search and/or Replaces with the likes of the inbuilt .net String Functions and Regular Expressions.

sudhanva Ten Centuries Points: 1343 More actions · Answer 10

pkan (9/4/2009)
Hi sudhanva,
I hope you also must have learnt from Ken McKelvey about how to make it more understandable for other's to reply to your query so as to make "effective use of this forum" and not to have "unnecessary posts/replies".
It really is very easy to point out mistakes in someone, but first you should check whether you are not committing the same..;-)

Hello Mr / Mrs whatever pkan newbie, Thanking others for there effort made to solve the problem is niether a bad thing, nor it is a unnecessary post.

I cant argue in this anymore. I would appreciate if you put sincere effort to help others here. That will be a win-win situation for all of us.

Hope you understand.

pkan Newbie Points: 8 More actions · Answer 11

I think so you are very arrogant and don't understand that people replying to you on any of the forums are spending a lot of their quality time to solve your problem, and thanking someone or showing your gratitude is the polite way of reverting back.

When you are quoting "Please understand the requirement before you reply. Which will help others to make effective use of this forum, and it will not have unneccesary posts/replies.", you are showing your thankless attitude towards someone who's trying to help you out.

If you think that these forums are just for sake of taking advantage and gaining knowledge then my friend you have a wrong misconception, I would not write anymore on this, but I think I have no-where seen such thankless people having such rough attitude towards others.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Chim Kalunta (9/4/2009)
From a performance perspective, you will probably find that a CLR Procedure/Function might out perform a TSql Solution when it comes to heavy duty String manipulation. You also have the benefit of doing more sophisticated Search and/or Replaces with the likes of the inbuilt .net String Functions and Regular Expressions.

Heh... so show the CLR code you would write to solve this problem and the performace testing that shows it's faster. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

sudhanva (9/3/2009)
Hats off to you Ken McKelvey. 😎
One more question, will there be any performance issue using REPLACE function?

Actually, there is. You're doing "input parameter processing" at the data layer instead of in the presentation layer where it belongs. It may not seem like the code will take that long for one person making one request, but what if you have 10,000 people making such requests? Suddenly, the server is busy instead of each client taking something less than a millisecond to do.

The "parameter" should be passed to SQL Server from the GUI with all the ORs and ANDs already in the proper position.

Every clock cycle counts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

sudhanva (9/2/2009)
Please understand the requirement before you reply. Which will help others to make effective use of this forum, and it will not have unneccesary posts/replies.

BWAA-HAAA!!! Considering that you're the one asking the question, you're not the one to make such a judgment. Welcome ALL answers because even the wrong ones have some bit of learning in them. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)