How to get result for each row in the loop? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get result for each row in the loop?

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-08 : 12:05:19

Hi people,

Here is the situation:
table1 has text column of "quote" and I need check if there is a string of "good" in this column and return the result as condition used in where clause. I use charIndex('good', quote, 1) to find it and if it returns 0 that means it's not found ...
I tried to use while or other way to loop through but all failed. Any idea how to fix this? Thanks in advance!

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 12:08:07

Post your query, not sure if I understand completely what you're trying to accomplish

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 12:12:09

If all you want to do is return the records with 'good' in the quote, simply put the charindex in the where clause as follows:

WHERE CHARINDEX('good',quote,1) > 0

no loop necessary. SQL is a set based language and it's always better to use a set based solution.

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-08 : 13:14:05

Thanks Jeffreys. You're right. That works!
But I have more conditions to go through. See all of them:

If not found 'good' in quote then
----If not found 'better' in quote then
------if not found 'best' in quote then
--------return false
------else
--------return true
----Else
------Return true
Else
----Return true

Sounds weired? Yes. I think I need to create a function for this and put the function in where clause.
By the way how can I get the negative of a bit data? Like @bitVal = 1, how to get opposite of bitVal as 0? Thanks lot!

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 13:23:59

Okay, coming up.

To get the compliment (or opposite) value of a boolean datatype you prefix with a tilde (~).

ex. SELECT @val this would give the value
SELECT ~@vale this gives the logical NOT(@val)

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 13:24:49

ooops typo select ~@val simple prefix with tilde

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 13:27:15

If you only want to return quotes with a 'good', 'better' or 'best' then you would do:

WHERE CHARINDEX('good',quote,1) > 0
OR CHARINDEX('better',quote,1) > 0
OR CHARINDEX('best',quote,1) > 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 13:29:51

you just need this


SELECT CASE WHEN LEN(quote)-LEN(REPLACE(REPLACE(REPLACE(Quote,' good ',''),' better ',''),' best ',''))=0 THEN 'False' ELSE 'True' END,...

...
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 13:34:11

if you need to return all records but have a derived boolean field that indicates a 'TRUE' or 'FALSE' as to whether the quote contains one of your 3 conditions then you would not include them in the were clause. In this case you would put them in a case statement for a derived column. Something like...

SELECT ...
,CASE
WHEN CHARINDEX('good',quote,1) > 0
OR CHARINDEX('better',quote,1) > 0
OR CHARINDEX('best',quote,1) > 0 THEN 'TRUE' -- or 1
ELSE 'FALSE -- or 0
END as SomeDescriptiveName
FROM ...

Jeffreys
Starting Member

45 Posts

Posted - 2011-12-08 : 13:40:09

Didn't see visakh16's post - that would do it as well, a little slicker way of doing it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 01:21:01

quote:
Originally posted by Jeffreys

Didn't see visakh16's post - that would do it as well, a little slicker way of doing it.

tnx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

allan8964
Posting Yak Master

249 Posts

Posted - 2011-12-09 : 23:29:29

Gentlemen, how can you guy be so good at this? Thank you soooooooo much!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 07:40:45

quote:
Originally posted by allan8964

Gentlemen, how can you guy be so good at this? Thank you soooooooo much!!!

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources