Where clause and singularing out the Word N

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

Where clause and singularing out the Word N

Author

Topic

jodders
Starting Member

41 Posts

Posted - 2012-02-03 : 08:40:35

hi,

I have a where clause which returns at the moment a bunch of comments. The aim is to not show any information that starts with the letter N. The N stands for No, so when I run the query, i want to not show any comments that begins with this letter.


WHERE 
LEFT(TT7.TXT1,1) != 'N'

So far I have got this, but I think it's wrong.

Can you help? Cheers!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-03 : 09:08:03

WHERE <column name> NOT LIKE 'N%'

The left you have would have worked too.

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-03 : 09:13:06

jodders:
using

LIKE 'N%'

Lets you use an index on the column
Using

LEFT([COLUMN], 1) = 'N'

Does not

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jodders
Starting Member

41 Posts

Posted - 2012-02-03 : 09:18:14

Hi Gila monster, thanks for the hint, I had ago with that but my query is not quite right.

What my where clause is doing is if there is an N present in the comments column, it won't bring any results at all. So in my mini example below, I am hoping to bring back Apple and Pear. There are comments on ther but they don't start with the word N

Any way around this?

Eg.
Fruit N - Blah Blah Blah
Apple It is green
Pear Very tasty
Blackberry N - Tasty

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 09:21:51

show your current WHERE condition please

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

jodders
Starting Member

41 Posts

Posted - 2012-02-03 : 09:26:50

[code]
WHERE (TC5.SHOP='UK') and hC10.Client in ('AppleVille') and TT7.TXT1 NOT LIKE 'N%'
[\code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 09:32:19

as per your sample data, this is what you want


WHERE (TC5.SHOP='UK') and hC10.Client in ('AppleVille') and TT7.TXT1 NOT LIKE '% N %'

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

jodders
Starting Member

41 Posts

Posted - 2012-02-03 : 10:49:23

thanks visakh16, what if there was no comments in TT7.TXT1, but you still wanted to show details. for example:

Fruit N - Blah Blah Blah
Apple
Pear Very tasty
Blackberry N - Tasty

End Result:
Apple
Pear Very tasty

Using the TT7.TXT1 NOT LIKE '% N %' seems to only show results that have an entry.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 10:57:38

(TT7.TXT1 NOT LIKE '% N %' OR COALESCE(TT7.TXT1,'') = '')

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

jodders
Starting Member

41 Posts

Posted - 2012-02-03 : 11:01:33

Dude, you are a life saver. Thanks so much for your time and dedication.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-03 : 11:06:05

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources